MySQL查询中几种表连接整理

MySQL查询中几种表连接整理

[整理人:遥方  整理时间:2010-3-8]

在实际的编程过程中会经常看到表与表之间的外连接。包括左外连接、右外连接及内连接等等。具体有什么样的区别呢。还有如何在实际的应用中灵活应用这些连接操作呢。现在整理出来以备将来的快速应用

一、left join 操作

示例：先创建两个备用表其创建表结构语法如下

create table table1(id int,name char(20));

create table table2(id int,name char(20));

现在往里面先插入三条记录SQL语句

mysql> select * from table1;

+------+------+

| id   | name |

+------+------+

|    1 | tom  |

|    2 | jack |

|    3 | buck |

+------+------+

mysql> select * from table2;

+------+------------+

| id   | name       |

+------+------------+

|    4 | table2buck |

|    2 | table2jack |

|    1 | table2tom  |

+------+------------+

现在我写一条SQL语句：select * from table1 left join table2 on table1.id=table2.id;

结果：

+------+------+------+------------+

| id   | name | id   | name       |

+------+------+------+------------+

|    1 | tom  |    1 | table2tom  |

|    2 | jack |    2 | table2jack |

|    3 | buck | NULL | NULL       |

+------+------+------+------------+

结论：

left join是以A表的记录为基础的,A可以看成左表,B可以看成右表,left join是以左表为准的.
换句话说,左表(A)的记录将会全部表示出来,而右表(B)只会显示符合搜索条件的记录(例子中为: A.aID = B.bID). B表记录不足的地方均为NULL.

我的理解

左连接的话 位于左边的表的内容将会全部查询出来。而右边的表只会提取符合条件(on) 的记录出来！

 

二、right left 操作

mysql> select * from table1 right join table2 on table1.id=table2.id;

+------+------+------+------------+

| id   | name | id   | name       |

+------+------+------+------------+

| NULL | NULL |    4 | table2buck |

|    2 | jack |    2 | table2jack |

|    1 | tom  |    1 | table2tom  |

+------+------+------+------------+

结论：右连接刚好与左连接相反。即以右边的表为基准。它会将全部的记录给提取出来的。对于左边的表如果没有记录就为空了。查询出来的总数=右边的记录数

 

我的理解：

右连接查询  相当于先将右边的记录全部提取出来，然后对于左边的表记录只有符合条件的才会提取出来

是右边条件的值映射到左边去 在左边如果符合条件的就会提取出来！

 

如果左边没有的话就直接填充为null 即可！

 

三、inner join 操作

如下

mysql> select * from table1 inner join table2 on table1.id=table2.id;

+------+------+------+------------+

| id   | name | id   | name       |

+------+------+------+------------+

|    2 | jack |    2 | table2jack |

|    1 | tom  |    1 | table2tom  |

+------+------+------+------------+

 

另外一种SQL写法：mysql> select * from table1,table2 where  table1.id=table2.id;

 

PS： 内连接表示只会查询出来符合条件的记录出来。它不会说以谁为基准的。只会提取全部符合条件的记录集出来的！

 

我的理解：

原来我们的这种写法 select * from table a ,table b where a.id = b.id

这样的两个表的关联查询其原理与inner join是一样的呀。

 

总结：

内连接取交集，外连接分左和右， 左连接左边的全取，右连接右边的全取！

其中说明几点：

左外连接的两种写法：

select * from table1 left outer join table2 on table1.id=table2.id;

select * from table1 left  join table2 on table1.id=table2.id;

内连接的三种写法：

mysql> select * from table1 join table2 on table1.id=table2.id;

mysql> select * from table1 inner join table2 on table1.id=table2.id;

mysql> select * from table1,table2 where  table1.id=table2.id;

结论：

外连接分两种：可以写成left join也可以写成outer join left

内连接分三种：可以写成inner join也可以写成join还可以直接写成两个表的关联查询

 

补充：

在数据库理论书上还有一种是 全外连接 即是左、右外连接的超集。

但是查询资料显示

至少在5.1。17中不支持FULL　JOIN，用LEFT　JOIN+RIGHT　JOIN解决

即不可以直接傅full join来操作。可以使用左、右外连接的方法来选择！

具体的操作：

select * from table1 left join table2 on table1.id=table2.id union all select * from table1 right join table2 on table1.id=table2.id where table1.id is null;

结果：

+------+------+------+------------+

| id   | name | id   | name       |

+------+------+------+------------+

|    1 | tom  |    1 | table2tom  |

|    2 | jack |    2 | table2jack |

|    3 | buck | NULL | NULL       |

| NULL | NULL |    4 | table2buck |

+------+------+------+------------+

 

心得：实现外连接的话就要考虑使用union关键字进行联合查询出来