SQL中inner join、left join、right join、outer join之间的区别
SQL中inner join、left join、right join、outer join之间的区别
举个例子你就能知道了!
A表(a1,b1,c1) B表(a2,b2)
a1 b1 c1 a2 b2
01 数学 95 01 张三
02 语文 90 02 李四
03 英语 80 04 王五
select A.*, B.* from A
inner join B on(A.a1=B.a2)
结果是: www.2cto.com
a1 b1 c1 a2 b2
01 数学 95 01 张三
02 语文 90 02 李四
select A.*, B.* from A
left outer join B on(A.a1=B.a2)
结果是:
a1 b1 c1 a2 b2
01 数学 95 01 张三
02 语文 90 02 李四
03 英语 80 NULL NULL
select A.*, B.* from A
right outer join B on(A.a1=B.a2)
结果是:
a1 b1 c1 a2 b2
01 数学 95 01 张三
02 语文 90 02 李四
NULL NULL NULL 04 王五
select A.*,B.* from A
full outer join B on(A.a1=B.a2)
结果是:
a1 b1 c1 a2 b2
01 数学 95 01 张三
02 语文 90 02 李四
03 英语 80 NULL NULL
NULL NULL NULL 04 王五
再举一个例子:
cutomer表:
orders表, 其中CUSTOMER_ID是外键,关联的是customer表的主键:CUSTOMER_ID:
使用左外连接查询:
命令:
SELECT customer0_.CUSTOMER_ID AS CUSTOMER_ID, customer0_.CUSTOMERNAME AS CUSTOMER_NAME, order1_.CUSTOMER_ID AS CUSTOMER3_1_1_, order1_.ORDER_ID AS ORDER_ID1_1_1_, order1_.ORDER_NAME AS ORDER_NA2_1_2_ FROM CUSTOMER customer0_ LEFT OUTER JOIN ORDERS order1_ ON customer0_.CUSTOMER_ID = order1_.CUSTOMER_ID
查询结果:
使用右外连接查询:
命令:
SELECT customer0_.CUSTOMER_ID AS CUSTOMER_ID, customer0_.CUSTOMERNAME AS CUSTOMER_NAME, order1_.CUSTOMER_ID AS CUSTOMER3_1_1_, order1_.ORDER_ID AS ORDER_ID1_1_1_, order1_.ORDER_NAME AS ORDER_NA2_1_2_ FROM CUSTOMER customer0_ RIGHT OUTER JOIN ORDERS order1_ ON customer0_.CUSTOMER_ID = order1_.CUSTOMER_ID
结果:
内连接查询
命令:
SELECT customer0_.CUSTOMER_ID AS CUSTOMER_ID, customer0_.CUSTOMERNAME AS CUSTOMER_NAME, order1_.CUSTOMER_ID AS CUSTOMER3_1_1_, order1_.ORDER_ID AS ORDER_ID1_1_1_, order1_.ORDER_NAME AS ORDER_NA2_1_2_ FROM CUSTOMER customer0_ INNER JOIN ORDERS order1_ ON customer0_.CUSTOMER_ID = order1_.CUSTOMER_ID
结果: