sql 中inner join和left join的区别 sql中left join、right join、inner...

SQL\u4e2d inner join\u3001 left join \u3001right join\u3001 outer join\u4e4b\u95f4\u7684\u533a\u522b

\u4e3e\u4e2a\u4f8b\u5b50\u4f60\u5c31\u80fd\u77e5\u9053\u4e86\uff01
A\u8868(a1,b1,c1) B\u8868(a2,b2)
a1 b1 c1 a2 b2
01 \u6570\u5b66 95 01 \u5f20\u4e09
02 \u8bed\u6587 90 02 \u674e\u56db
03 \u82f1\u8bed 80 04 \u738b\u4e94
select A.*,B.* from A
inner join B on(A.a1=B.a2)
\u7ed3\u679c\u662f\uff1a
a1 b1 c1 a2 b2
01 \u6570\u5b66 95 01 \u5f20\u4e09
02 \u8bed\u6587 90 02 \u674e\u56db

select A.*,B.* from A
left outer join B on(A.a1=B.a2)
\u7ed3\u679c\u662f:
a1 b1 c1 a2 b2
01 \u6570\u5b66 95 01 \u5f20\u4e09
02 \u8bed\u6587 90 02 \u674e\u56db
03 \u82f1\u8bed 80 NULL NULL

select A.*,B.* from A
right outer join B on(A.a1=B.a2)
\u7ed3\u679c\u662f:
a1 b1 c1 a2 b2
01 \u6570\u5b66 95 01 \u5f20\u4e09
02 \u8bed\u6587 90 02 \u674e\u56db
NULL NULL NULL 04 \u738b\u4e94

select A.*,B.* from A
full outer join B on(A.a1=B.a2)
\u7ed3\u679c\u662f:
a1 b1 c1 a2 b2
01 \u6570\u5b66 95 01 \u5f20\u4e09
02 \u8bed\u6587 90 02 \u674e\u56db
03 \u82f1\u8bed 80 NULL NULL
NULL NULL NULL 04 \u738b\u4e94

\u4e8c\u3001\u5185\u8fde\u63a5\uff08INNER JOIN\uff09

\u5185\u8fde\u63a5\uff08INNER JOIN\uff09\uff1a\u6709\u4e24\u79cd\uff0c\u663e\u5f0f\u7684\u548c\u9690\u5f0f\u7684\uff0c\u8fd4\u56de\u8fde\u63a5\u8868\u4e2d\u7b26\u5408\u8fde\u63a5\u6761\u4ef6\u548c\u67e5\u8be2\u6761\u4ef6\u7684\u6570\u636e\u884c\u3002\uff08\u6240\u8c13\u7684\u94fe\u63a5\u8868\u5c31\u662f\u6570\u636e\u5e93\u5728\u505a\u67e5\u8be2\u5f62\u6210\u7684\u4e2d\u95f4\u8868\uff09\u3002

\u4f8b\u5982\uff1a\u4e0b\u9762\u7684\u8bed\u53e53\u548c\u8bed\u53e54\u7684\u7ed3\u679c\u662f\u76f8\u540c\u7684\u3002

\u8bed\u53e53\uff1a\u9690\u5f0f\u7684\u5185\u8fde\u63a5\uff0c\u6ca1\u6709INNER JOIN\uff0c\u5f62\u6210\u7684\u4e2d\u95f4\u8868\u4e3a\u4e24\u4e2a\u8868\u7684\u7b1b\u5361\u5c14\u79ef(\u5982\u679c\u6ca1\u6709where\u6761\u4ef6)\u3002

SELECT O.ID,O.ORDER_NUMBER,C.ID,C.NAMEFROM CUSTOMERS C,ORDERS O WHERE C.ID=O.CUSTOMER_ID;

\u8bed\u53e54\uff1a\u663e\u793a\u7684\u5185\u8fde\u63a5\uff0c\u4e00\u822c\u79f0\u4e3a\u5185\u8fde\u63a5\uff0c\u6709INNER JOIN\uff0c\u5f62\u6210\u7684\u4e2d\u95f4\u8868\u4e3a\u4e24\u4e2a\u8868\u7ecf\u8fc7ON\u6761\u4ef6\u8fc7\u6ee4\u540e\u7684\u7b1b\u5361\u5c14\u79ef\u3002

SELECT O.ID,O.ORDER_NUMBER,C.ID,C.NAMEFROM CUSTOMERS C INNER JOIN ORDERS O ON C.ID=O.CUSTOMER_ID;

\u4e09\u3001\u5916\u8fde\u63a5\uff08OUTER JOIN\uff09(\u5fc5\u987b\u6709ON\u6761\u4ef6)\uff1a
\u5916\u8fde\u4e0d\u4f46\u8fd4\u56de\u7b26\u5408\u8fde\u63a5\u548c\u67e5\u8be2\u6761\u4ef6\u7684\u6570\u636e\u884c\uff0c\u8fd8\u8fd4\u56de\u4e0d\u7b26\u5408\u6761\u4ef6\u7684\u4e00\u4e9b\u884c\u3002
\u5916\u8fde\u63a5\u5206\u4e09\u7c7b\uff1a\u5de6\u5916\u8fde\u63a5\uff08LEFT OUTER JOIN\uff09\u3001\u53f3\u5916\u8fde\u63a5\uff08RIGHT OUTER JOIN\uff09\u548c\u5168\u5916\u8fde\u63a5\uff08FULL OUTER JOIN\uff09\u3002
\u4e09\u8005\u7684\u5171\u540c\u70b9\u662f\u90fd\u8fd4\u56de\u7b26\u5408\u8fde\u63a5\u6761\u4ef6\u548c\u67e5\u8be2\u6761\u4ef6\uff08\u5373\uff1a\u5185\u8fde\u63a5\uff09\u7684\u6570\u636e\u884c\u3002\u4e0d\u540c\u70b9\u5982\u4e0b\uff1a
\u5de6\u5916\u8fde\u63a5\u8fd8\u8fd4\u56de\u5de6\u8868\u4e2d\u4e0d\u7b26\u5408\u8fde\u63a5\u6761\u4ef6\u5355\u7b26\u5408\u67e5\u8be2\u6761\u4ef6\u7684\u6570\u636e\u884c\u3002
\u53f3\u5916\u8fde\u63a5\u8fd8\u8fd4\u56de\u53f3\u8868\u4e2d\u4e0d\u7b26\u5408\u8fde\u63a5\u6761\u4ef6\u5355\u7b26\u5408\u67e5\u8be2\u6761\u4ef6\u7684\u6570\u636e\u884c\u3002
\u5168\u5916\u8fde\u63a5\u8fd8\u8fd4\u56de\u5de6\u8868\u4e2d\u4e0d\u7b26\u5408\u8fde\u63a5\u6761\u4ef6\u5355\u7b26\u5408\u67e5\u8be2\u6761\u4ef6\u7684\u6570\u636e\u884c\uff0c\u5e76\u4e14\u8fd8\u8fd4\u56de\u53f3\u8868\u4e2d\u4e0d\u7b26\u5408\u8fde\u63a5\u6761\u4ef6\u5355\u7b26\u5408\u67e5\u8be2\u6761\u4ef6\u7684\u6570\u636e\u884c\u3002\u5168\u5916\u8fde\u63a5\u5b9e\u9645\u662f\u4e0a\u5de6\u5916\u8fde\u63a5\u548c\u53f3\u5916\u8fde\u63a5\u7684\u6570\u5b66\u5408\u96c6\uff08\u53bb\u6389\u91cd\u590d\uff09\uff0c\u5373\u201c\u5168\u5916=\u5de6\u5916 UNION \u53f3\u5916\u201d\u3002
\u8bf4\u660e\uff1a\u5de6\u8868\u5c31\u662f\u5728\u201c\uff08LEFT OUTER JOIN\uff09\u201d\u5173\u952e\u5b57\u5de6\u8fb9\u7684\u8868\u3002\u53f3\u8868\u5f53\u7136\u5c31\u662f\u53f3\u8fb9\u7684\u4e86\u3002\u5728\u4e09\u79cd\u7c7b\u578b\u7684\u5916\u8fde\u63a5\u4e2d\uff0cOUTER \u5173\u952e\u5b57\u662f\u53ef\u7701\u7565\u7684\u3002
\u4e0b\u9762\u4e3e\u4f8b\u8bf4\u660e\uff1a

\u8bed\u53e55\uff1a\u5de6\u5916\u8fde\u63a5\uff08LEFT OUTER JOIN\uff09
SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAMEFROM ORDERS O LEFT OUTER JOIN CUSTOMERS C ON C.ID=O.CUSTOMER_ID;

\u8bed\u53e56\uff1a\u53f3\u5916\u8fde\u63a5\uff08RIGHT OUTER JOIN\uff09
SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAMEFROM ORDERS O RIGHT OUTER JOIN CUSTOMERS C ON C.ID=O.CUSTOMER_ID;\u6ce8\u610f\uff1aWHERE\u6761\u4ef6\u653e\u5728ON\u540e\u9762\u67e5\u8be2\u7684\u7ed3\u679c\u662f\u4e0d\u4e00\u6837\u7684\u3002\u4f8b\u5982\uff1a

\u8bed\u53e57\uff1aWHERE\u6761\u4ef6\u72ec\u7acb\u3002
SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAMEFROM ORDERS O LEFT OUTER JOIN CUSTOMERS C ON C.ID=O.CUSTOMER_IDWHERE O.ORDER_NUMBER'MIKE_ORDER001';

\u8bed\u53e58\uff1a\u5c06\u8bed\u53e57\u4e2d\u7684WHERE\u6761\u4ef6\u653e\u5230ON\u540e\u9762\u3002
SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAMEFROM ORDERS O LEFT OUTER JOIN CUSTOMERS C ON C.ID=O.CUSTOMER_ID AND O.ORDER_NUMBER'MIKE_ORDER001';

\u4ece\u8bed\u53e57\u548c\u8bed\u53e58\u67e5\u8be2\u7684\u7ed3\u679c\u6765\u770b\uff0c\u663e\u7136\u662f\u4e0d\u76f8\u540c\u7684\uff0c\u8bed\u53e58\u663e\u793a\u7684\u7ed3\u679c\u662f\u96be\u4ee5\u7406\u89e3\u7684\u3002\u56e0\u6b64\uff0c\u63a8\u8350\u5728\u5199\u8fde\u63a5\u67e5\u8be2\u7684\u65f6\u5019\uff0cON\u540e\u9762\u53ea\u8ddf\u8fde\u63a5\u6761\u4ef6\uff0c\u800c\u5bf9\u4e2d\u95f4\u8868\u9650\u5236\u7684\u6761\u4ef6\u90fd\u5199\u5230WHERE\u5b50\u53e5\u4e2d\u3002

说起这两种联接方式，一定要把Right Join联系起来。

一、释义。

1、Left Join（左联接）

    以左表为中心，返回左表中符合条件的所有记录以及右表中联结字段相等的记录——当右表中无相应联接记录时，返回空值。
2、Right Join（右联接）    

    以右表为中心，返回右表中符合条件的所有记录以及左表中联结字段相等的记录——当左表中无相应联接记录时，返回空值。

3、Inner Join（等值连接） 

    返回两个表中联结字段相等的行。

二、示例。

1、插入测试表(test1,test2)

create table test1  --测试表1

(id int not null,

 value char(10) )

create table test2  --测试表2

(id int not null,

 value char(10) )

2、插入数据

--insert into test1

 insert into test1

 values (1,'testaa')

 insert into test1

 values (2,'testaa')

 insert into test1

 values (3,'testaa')

--insert into test2

  insert into test2

 values (1,'testaa2')

 insert into test2

 values (2,'testaa2')

 insert into test2

 values (4,'testaa2')

3、查询结果比较（附图）

 select * from test1 a left join test2 b on a.id = b.id

 select * from test1 a right join test2 b on a.id = b.id

 select * from test1 a inner join test2 b on a.id = b.id

4、删除测试表

drop table test1 

drop table test2 

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

INNER JOIN 等价于 JOIN
你可以理解为 JOIN 　　是 INNER JOIN 的缩写。

LEFT JOIN 等价于 LEFT OUTER JOIN
RIGHT JOIN 等价于 RIGHT OUTER JOIN

扩展阅读：java windowbuilder ... wiley online library ... vb6 sql inner jion ... spline design ... sql inner join where ... inner join 多个on ... 16 personality ... springer journals ... link.springer.com ...