Mysql之我见三（join查询）

1.Sql解析

2.常见join查询

SELECT * FROM staff;

SELECT * FROM department;

（1）内链接（通俗的说就是取2张表的公共部分）

select <slect_list> from TableA A inner join TableB B on A.Key=B.Key;

SELECT * FROM staff a INNER JOIN department b ON a.`depart_id`=b.`id`;

等价于

SELECT * FROM staff a , department b WHERE a.`depart_id`=b.`id`;

（2）左连接（取a表的所有的数据,b表相关联字段没有数据就取null）

select <slect_list> from TableA A left join TableB B on A.Key=B.Key;

SELECT * FROM staff a LEFT JOIN department b ON a.`depart_id`=b.`id`;

(3)右连接（取b表的所有的数据，b表相关联字段没有数据就取null)

select <slect_list> from TableA A right join TableB B on A.Key=B.Key;

SELECT * FROM staff a RIGHT JOIN department b ON a.`depart_id`=b.`id`;

（4）左连接（取a表的所特有的数据，把b表中null的数据去掉）

select <slect_list> from TableA A left join TableB B on A.Key=B.Key where B.Key is null;

SELECT * FROM staff a LEFT JOIN department b ON a.`depart_id`=b.`id` WHERE b.id IS NULL;

（5）右连接（取b表所特有的数据，a表相关联字段没有数据就取null)

select <slect_list> from TableA A right join TableB B on A.Key=B.Key where A.Key is null;

SELECT * FROM staff a RIGHT JOIN department b ON a.`depart_id`=b.`id` WHERE a.`depart_id` IS NULL;

（6）全连接(a表和b表中所有的数据)

select <slect_list> from TableA A full out join TableB B on A.Key=B.Key; (mysql5.5)

SELECT * FROM staff a LEFT JOIN department b ON a.`depart_id`=b.`id`

UNION

SELECT * FROM staff a RIGHT JOIN department b ON a.`depart_id`=b.`id`;

(7)表a所特有union表b所特有的

select <slect_list> from TableA A full out join TableB B on A.Key=B.Key where A.Key is null or B.Key is null;（mysql5.5）

等价

select <slect_list> from TableA A left join TableB B on A.Key=B.Key where B.Key is null

union

select <slect_list> from TableA A right join TableB B on A.Key=B.Key where A.Key is null;