mysql left join,right join,inner join用法与区别

在mysql中left join,right join,inner join这种三查询我想是我们常用的语句了,但是又有几个朋友具体知道他们三个的区别呢,下面我来来给各位同学介绍.

LEFT JOIN

会将联结关系中左右两个资料表。左边资料表在经过「联结」后，不管是否存在右边资料表资料与之对应，仍然会将资料全部列出，相关范例如下：

旧式WHERE「联结」语法则以WHERE子句中的「*=」表示「左联结（left join）」。

旧式 WHERE 语法,代码如下:

SELECT C.First_Name, C.Last_Name, O.Order_ID FROM Customer AS C, Order AS O WHERE C.Customer_ID *= O.Order_ID

这种语法经过MySQL的发展小组决定在MySQL不支援。他们所持的理由有两个：一是此类 WHERE「左联结」鲜少使用，如果要用，可以使用标??NSI-92的新式「LEFT JOIN」语法；另一个原因是是，MySQL发展经费及相关人员发展时间有限，此类少用的语法，似乎不需要浪费金钱与人力来做。

新式 JOIN 语法一,代码如下:

SELECT C.First_Name, C.Last_Name, O.Order_Id FORM Customer AS C　LEFT JOIN Order AS O ON C.Customer_ID = O.Customer_ID

这里Customer资料表中所有纪录都会列出，经过JOIN后，如果它的纪录在Order中找不到对应纪录的话，则会以NULL值代替。

ANSI-92 的 LEFT JOIN 相容语法还有另一种表达方式是利用 USING,上例可以改写为:

新式 JOIN 语法二,代码如下:

SELECT C.First_Name,C.Last_Name,O.Orders_ID FORM Customers AS C LEFT JOIN Orders AS O USING Customer_ID

这里使用 USING Customer_ID 取代 C.Customer_ID = O.Customer_ID,这种写法也可参考看看.

NATURAL LEFT JOIN

其实就是 LEFT JOIN,差是所有符合「联结」关系的字段都自动选取,不需要在用 USING 或是 ON 之类的子句来特别注明,所以上述 LEFT JOIN 范例又可改写为:

新式 JOIN 语法,代码如下:

SELECT C.First_Name, C.Last_Name, O.Orders_ID FORM Customers AS C NATURAL LEFT JOIN Orders AS O

由于Customers和Orders中都有Customer_ID，经由NATURAL LEFT JOIN关键字之后，便自动 LEFT JOIN 起来。这种「联结」用法不常见，所以要用的话，也跟 LEFT JOIN 一样.

只有 RIGHT JOIN 吗?

几乎每一种 RDMS 都有 RIGHT JOIN 这种语法,但是 MySQL 没有支援 RIGHT JOIN 语法,不支援的原因很简单,因为把 LEFT JOIN 的资料表对调再使用 LEFT JOIN 就可以得到 RIGHT JOIN 的效果,例如：

SELECT C.FIIRST_NAME FROM CUSTOMERS AS C LEFT JOIN ORDERS AS O ON C_CUSTOMER_ID=O.CUSTOMER_ID;

现在要取得 RIGHT JOIN 的效果只要两个资料表对调,代码如下:

SELECT C.FIIRST_NAME FROM ORDERS AS O LEFT JOIN CUSTOMERS AS C ON C_CUSTOMER_ID=O.CUSTOMER_ID;

因此实在没有必要在 MySQL 中做一个 RIGHT JOIN，做一个 RIGHT JOIN 甚至还会降低资料库的效能.

下面是例子分析,代码如下:

1. --表A记录如下： 
2. aID aNum 
3. 1 a20050111 
4. 2 a20050112 
5. 3 a20050113 
6. 4 a20050114 
7. 5 a20050115 
8. --表B记录如下: 
9. bID bName 
10. 1 2006032401 
11. 2 2006032402 
12. 3 2006032403 
13. 4 2006032404 
14. 8 2006032408 

创建这两个表SQL语句如下:

1. CREATE TABLE a 
2. aID int( 1 ) AUTO_INCREMENT PRIMARY KEY , 
3. aNum char( 20 ) 
4. ) 
5. CREATE TABLE b( 
6. bID int( 1 ) NOT NULL AUTO_INCREMENT PRIMARY KEY , 
7. bName char( 20 ) 
8. ) 
9. INSERT INTO a 
10. VALUES ( 1, ‘a20050111′ ) , ( 2, ‘a20050112′ ) , ( 3, ‘a20050113′ ) , ( 4, ‘a20050114′ ) , ( 5, ‘a20050115′ ) ; 
11. INSERT INTO b 
12. VALUES ( 1, ‘ 2006032401′ ) , ( 2, ’2006032402′ ) , ( 3, ’2006032403′ ) , ( 4, ’2006032404′ ) , ( 8, ’2006032408′ ) ; 

实验如下:

1.left join(左联接)

sql语句如下:

1. SELECT * FROM a 
2. LEFT JOIN b 
3. ON a.aID =b.bID 
4. --结果如下: 
5. aID aNum bID bName 
6. 1 a20050111 1 2006032401 
7. 2 a20050112 2 2006032402 
8. 3 a20050113 3 2006032403 
9. 4 a20050114 4 2006032404 
10. 5 a20050115 NULL NULL 

所影响的行数为 5 行.

结果说明:left join是以A表的记录为基础的,A可以看成左表,B可以看成右表,left join是以左表为准的.

换句话说,左表(A)的记录将会全部表示出来,而右表(B)只会显示符合搜索条件的记录(例子中为: A.aID = B.bID).

B表记录不足的地方均为NULL.

2.right join(右联接)

sql语句如下,代码如下:

1. SELECT * FROM a 
2. RIGHT JOING b 
3. ON a.aID = b.bID 
4. --结果如下: 
5. aID aNum bID bName 
6. 1 a20050111 1 2006032401 
7. 2 a20050112 2 2006032402 
8. 3 a20050113 3 2006032403 
9. 4 a20050114 4 2006032404 
10. NULL NULL 8 2006032408 

所影响的行数为 5 行.

结果说明:仔细观察一下,就会发现,和left join的结果刚好相反,这次是以右表(B)为基础的,A表不足的地方用NULL填充.

3.inner join(相等联接或内联接)

sql语句如下,代码如下:

1. SELECT * FROM a 
2. INNER JOIN b 
3. ON a.aID =b.bID 

等同于以下SQL句,代码如下:

1. SELECT * 
2. FROM a,b 
3. WHERE a.aID = b.bID 
4. --结果如下: 
5. aID aNum bID bName 
6. 1 a20050111 1 2006032401 
7. 2 a20050112 2 2006032402 
8. 3 a20050113 3 2006032403 
9. 4 a20050114 4 2006032404 

结果说明:很明显,这里只显示出了 A.aID = B.bID的记录.这说明inner join并不以谁为基础,它只显示符合条件的记录.

LEFT JOIN操作用于在任何的 FROM 子句中,组合来源表的记录。使用 LEFT JOIN 运算来创建一个左边外部联接,左边外部联接将包含了从第一个（左边）开始的两个表中的全部记录,即使在第二个（右边）表中并没有相符值的记录。

语法:FROM table1 LEFT JOIN table2 ON table1.field1 compopr table2.field2

说明:table1, table2参数用于指定要将记录组合的表的名称。

field1, field2参数指定被联接的字段的名称,且这些字段必须有相同的数据类型及包含相同类型的数据,但它们不需要有相同的名称.

compopr参数指定关系比较运算符:”=”,“<"， ">“,“<=", ">=” 或 “<>“.

如果在INNER JOIN操作中要联接包含Memo 数据类型或 OLE Object 数据类型数据的字段,将会发生错误.