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英语 8004王五
select A.*, B.* from A
inner joinB on(A.a1=B.a2)
结果是:
a1 b1 c1 a2 b2
01 数学 95 01 张三
02 语文 90 02 李四
select A.*, B.* from A
left outerjoin B on(A.a1=B.a2)
结果是:
a1 b1 c1 a2 b2
01 数学 95 01 张三
02 语文 90 02 李四
03 英语 80NULL NULL
select A.*, B.* from A
right outerjoin B on(A.a1=B.a2)
结果是:
a1 b1 c1 a2 b2
01 数学 95 01 张三
02 语文 90 02 李四
NULL NULL NULL04 王五
select A.*,B.* from A
full outerjoin B on(A.a1=B.a2)
结果是:
a1 b1 c1 a2 b2
01 数学 95 01 张三
02 语文 90 02 李四
03 英语 80NULL NULL
NULL NULL NULL04 王五
再举一个例子:
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_FROMCUSTOMER 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_FROMCUSTOMER 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_FROMCUSTOMER customer0_ INNER JOIN ORDERS order1_ ON customer0_.CUSTOMER_ID = order1_.CUSTOMER_ID
结果: