SQL中inner join、left join、right join、outer join之间的区别 - 我表情悠哉
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
结果：