1、连接查询
1.0连接的基本语法格式:
from TABLE1 join_type TABLE2 [on (join_condition)][where (query_condition)]
TABLE1:左表
TABLE2:右表
join_type:连接的类型。交叉、内连接、左外连接、右外连接
on:设置连接条件
where:对连接查询的结果进步一的筛选
1.1交叉连接
select * from CUSTOMER cross join ORDERS;
或者
select * from CUSTOMER,ORDERS;
select c.name,o.order_number from CUSTOMER c,ORDERS o;
1.2内连接:
隐式内连接:(不使用on关键字,使用where)
select * from CUSTOMER c,ORDERS o where c.id=o.customer_id;
显式内连接:(使用on关键字)
select * from CUSTOMER c inner join ORDERS o on c.id=o.customer_id;
1.3外连接:
左外连接:(返回符合连接条件的所有记录,同时还返回左表中其余的所有记录)
select * from CUSTOMER c left outer join ORDERS o on c.id=o.customer_id;
右外连接:(返回符合连接条件的所有记录,同时还返回右表中其余的所有记录)
select * from CUSTOMER c right outer join ORDERS o on c.id=o.customer_id;