首页 > 代码库 > mysql连接内连接、左连接、右连接、全连接
mysql连接内连接、左连接、右连接、全连接
联接条件可在ROM或WHERE子句中指定，建议在FROM子句中指定联接条件。WHERE和HAVING子句也可以包含搜索条件，以进一步筛选联接条件所选的行。
联接可分为以下几类：
内联接（典型的联接运算，使用像 =或<>之类的比较运算符）。包括相等联接和自然联接。内联接使用比较运算符根据每个表共有的列的值匹配两个表中的行。例如，检索students和courses表中学生标识号相同的所有行。
外联接。
外联接可以是左向外联接、右向外联接或完整外部联接。
在 FROM子句中指定外联接时，可以由下列几组关键字中的一组指定：
LEFT JOIN或LEFT OUTER JOIN。
左向外联接的结果集包括LEFT OUTER子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。
RIGHT JOIN或RIGHT OUTER JOIN。
右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。
FULL JOIN或FULL OUTER JOIN。
完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。
交叉联接。
交叉联接返回左表中的所有行，左表中的每一行与右表中的所有行组合。交叉联接也称作笛卡尔积。
例如，下面的内联接检索与某个出版商居住在相同州和城市的作者：
USE pubs
SELECT a.au_fname, a.au_lname, p.pub_name
FROM authors AS a INNER JOIN publishers AS p
ON a.city = p.city
AND a.state = p.state
ORDER BY a.au_lname ASC, a.au_fname ASC
FROM 子句中的表或视图可通过内联接或完整外部联接按任意顺序指定；但是，用左或右向外联接指定表或视图时，表或视图的顺序很重要。有关使用左或右向外联接排列表的更多信息，请参见使用外联接。
例子：
a表 id name b表 id job parent_id
1 张3 1 23 1
2 李四 2 34 2
3 王武 3 34 4
a.id同parent_id 存在关系
内连接
select a.*,b.* from a inner join b on a.id=b.parent_id
结果是
1 张3 1 23 1
2 李四 2 34 2
左连接
select a.*,b.* from a left join b on a.id=b.parent_id
结果是
1 张3 1 23 1
2 李四 2 34 2
3 王武 null
右连接
select a.*,b.* from a right join b on a.id=b.parent_id
结果是
1 张3 1 23 1
2 李四 2 34 2
null 3 34 4
完全连接
select a.*,b.* from a full join b on a.id=b.parent_id
结果是
1 张3 1 23 1
2 李四 2 34 2
null 3 34 4
3 王武 null
MySQL内连接&左外连接&右外连接&完全连接和交叉连接
MySql表连接查询，两个表之间的连接查
tb_order表
| orderId | orderNo | personId |
| 1 | 12345 | 3 |
| 2 | 23456 | 3 |
| 3 | 34567 | 1 |
| 4 | 45678 | 1 |
| 5 | 56789 | 2 |
tb_person表
| personId | last_name | first_name | address | city |
| 1 | hh | aa | ee | dd |
| 2 | ww | ee | rr | |
| 3 | yy | ii | kk | nn |
| 4 | tr | rt | hg | mn |
| 5 | sdf | sd | sd | ds |
--------------------------------------------------------------------------------------------------------------------------------------------
1.内连接
内连接(inner join)是应用程序中用的普遍的"连接"操作，它一般都是默认连接类型。
mysql> select a.*,b.* from tb_person as a inner join tb_order as b;+----------+-----------+------------+---------+------+---------+---------+----------+| personId | last_name | first_name | address | city | orderId | orderNo | personId |+----------+-----------+------------+---------+------+---------+---------+----------+| 1 | hh | aa | ee | dd | 1 | 12345 | 3 || 2 | qq | ww | ee | rr | 1 | 12345 | 3 || 3 | yy | ii | kk | nn | 1 | 12345 | 3 || 4 | tr | rt | hg | mn | 1 | 12345 | 3 || 5 | sdf | sd | sd | ds | 1 | 12345 | 3 || 1 | hh | aa | ee | dd | 2 | 23456 | 3 || 2 | qq | ww | ee | rr | 2 | 23456 | 3 || 3 | yy | ii | kk | nn | 2 | 23456 | 3 || 4 | tr | rt | hg | mn | 2 | 23456 | 3 || 5 | sdf | sd | sd | ds | 2 | 23456 | 3 || 1 | hh | aa | ee | dd | 3 | 34567 | 1 || 2 | qq | ww | ee | rr | 3 | 34567 | 1 || 3 | yy | ii | kk | nn | 3 | 34567 | 1 || 4 | tr | rt | hg | mn | 3 | 34567 | 1 || 5 | sdf | sd | sd | ds | 3 | 34567 | 1 || 1 | hh | aa | ee | dd | 4 | 45678 | 1 || 2 | qq | ww | ee | rr | 4 | 45678 | 1 || 3 | yy | ii | kk | nn | 4 | 45678 | 1 || 4 | tr | rt | hg | mn | 4 | 45678 | 1 || 5 | sdf | sd | sd | ds | 4 | 45678 | 1 || 1 | hh | aa | ee | dd | 5 | 56789 | 2 || 2 | qq | ww | ee | rr | 5 | 56789 | 2 || 3 | yy | ii | kk | nn | 5 | 56789 | 2 || 4 | tr | rt | hg | mn | 5 | 56789 | 2 || 5 | sdf | sd | sd | ds | 5 | 56789 | 2 |+----------+-----------+------------+---------+------+---------+---------+----------+25 rows in set
内连接使用ON子句指定两个表的连接条件，WHERE子句来指定条件子句。
mysql> select a.*,b.* from tb_person as a inner join tb_order as b on a.personId=b.personId;+----------+-----------+------------+---------+------+---------+---------+----------+| personId | last_name | first_name | address | city | orderId | orderNo | personId |+----------+-----------+------------+---------+------+---------+---------+----------+| 3 | yy | ii | kk | nn | 1 | 12345 | 3 || 3 | yy | ii | kk | nn | 2 | 23456 | 3 || 1 | hh | aa | ee | dd | 3 | 34567 | 1 || 1 | hh | aa | ee | dd | 4 | 45678 | 1 || 2 | qq | ww | ee | rr | 5 | 56789 | 2 |+----------+-----------+------------+---------+------+---------+---------+----------+5 rows in set
上面sql语句的作用就是查询出所有人的订单。其实上面那条查询等价于：
mysql> select * from tb_person a,tb_order b where a.personId=b.personId;
2.左外连接
使用ON子句指定两个表的连接条件，WHERE子句来指定条件子句
LEFT JOIN 关键字会从左表那里返回所有的行，即使在右表中没有匹配的行。
查询personId为1的人的名字和所有的订单号
mysql> select a.orderNo,b.last_name from tb_order as a left join tb_person as b on a.personId=b.personId where b.personId=1;+---------+-----------+| orderNo | last_name |+---------+-----------+| 34567 | hh || 45678 | hh |+---------+-----------+2 rows in set
左外连接不加查询条件
这条sql语句作用：查询所有人的订单
mysql> select a.orderNo,b.last_name from tb_order as a left join tb_person as b on a.personId=b.personId;+---------+-----------+| orderNo | last_name |+---------+-----------+| 12345 | yy || 23456 | yy || 34567 | hh || 45678 | hh || 56789 | qq |+---------+-----------+5 rows in set
3.右外连接
使用ON子句指定两个表的连接条件，WHERE子句来指定条件子句 。
mysql> select * from tb_order as a right join tb_person as b on a.personId=b.personId;+---------+---------+----------+----------+-----------+------------+---------+------+| orderId | orderNo | personId | personId | last_name | first_name | address | city |+---------+---------+----------+----------+-----------+------------+---------+------+| 3 | 34567 | 1 | 1 | hh | aa | ee | dd || 4 | 45678 | 1 | 1 | hh | aa | ee | dd || 5 | 56789 | 2 | 2 | qq | ww | ee | rr || 1 | 12345 | 3 | 3 | yy | ii | kk | nn || 2 | 23456 | 3 | 3 | yy | ii | kk | nn || NULL | NULL | NULL | 4 | tr | rt | hg | mn || NULL | NULL | NULL | 5 | sdf | sd | sd | ds |+---------+---------+----------+----------+-----------+------------+---------+------+7 rows in set
可以看到左边的表中的记录都被查询出来了
sql-1
mysql> select * from tb_order as a right join tb_person as b on a.personId=b.personId and a.orderId=1;+---------+---------+----------+----------+-----------+------------+---------+------+| orderId | orderNo | personId | personId | last_name | first_name | address | city |+---------+---------+----------+----------+-----------+------------+---------+------+| NULL | NULL | NULL | 1 | hh | aa | ee | dd || NULL | NULL | NULL | 2 | qq | ww | ee | rr || 1 | 12345 | 3 | 3 | yy | ii | kk | nn || NULL | NULL | NULL | 4 | tr | rt | hg | mn || NULL | NULL | NULL | 5 | sdf | sd | sd | ds |+---------+---------+----------+----------+-----------+------------+---------+------+5 rows in set
通过该结果我们可以猜想到这条sql语句是这样工作的：
从右表中读出一条记录，选出所有与on匹配的右表纪录(n条)进行连接，但没有符合连接条件（a.personId=b.personId and a.orderId=1）的记录，所以匹配为空记录。当personId = 3 时，根据连接条件有一条记录匹配。。
sql-2
mysql> select * from tb_order as a right join tb_person as b on a.personId=b.personId and a.orderId=1 where b.personId=1;+---------+---------+----------+----------+-----------+------------+---------+------+| orderId | orderNo | personId | personId | last_name | first_name | address | city |+---------+---------+----------+----------+-----------+------------+---------+------+| NULL | NULL | NULL | 1 | hh | aa | ee | dd |+---------+---------+----------+----------+-----------+------------+---------+------+1 row in set
这条语句比上一条语句多了一个限定条件。。。
4.完全连接
只要其中某个表存在匹配，FULL JOIN 关键字就会返回行。
注：MySQL lacks support for FULL OUTER JOIN。
5.交叉连接
使用ON子句指定两个表的连接条件
sql-1
mysql> select * from tb_order as a cross join tb_person as b on a.personId=b.personId;+---------+---------+----------+----------+-----------+------------+---------+------+| orderId | orderNo | personId | personId | last_name | first_name | address | city |+---------+---------+----------+----------+-----------+------------+---------+------+| 3 | 34567 | 1 | 1 | hh | aa | ee | dd || 4 | 45678 | 1 | 1 | hh | aa | ee | dd || 5 | 56789 | 2 | 2 | qq | ww | ee | rr || 1 | 12345 | 3 | 3 | yy | ii | kk | nn || 2 | 23456 | 3 | 3 | yy | ii | kk | nn |+---------+---------+----------+----------+-----------+------------+---------+------+5 rows in set
sql-2
mysql> select * from tb_order as a cross join tb_person as b where a.personId = b.personId;+---------+---------+----------+----------+-----------+------------+---------+------+| orderId | orderNo | personId | personId | last_name | first_name | address | city |+---------+---------+----------+----------+-----------+------------+---------+------+| 3 | 34567 | 1 | 1 | hh | aa | ee | dd || 4 | 45678 | 1 | 1 | hh | aa | ee | dd || 5 | 56789 | 2 | 2 | qq | ww | ee | rr || 1 | 12345 | 3 | 3 | yy | ii | kk | nn || 2 | 23456 | 3 | 3 | yy | ii | kk | nn |+---------+---------+----------+----------+-----------+------------+---------+------+5 rows in set
交叉连接时使用where语句过滤结果。
mysql连接内连接、左连接、右连接、全连接