SQL join 查询总结

为什么80%的码农都做不了架构师？>>>   

对 7 种 SQL JOIN 查询的总结， 准备表和数据 SQL ：

create table person (
  p_id bigint unsigned not null auto_increment ,
  last_name varchar(30) not null ,
  first_name varchar(30) not null ,
  address varchar(100) not null ,
  city varchar(30) not null ,
  primary key (p_id)
) engine = InnoDB , charset = utf8 ;

insert into person (last_name, first_name, address, city)
values ('Tom' , 'John' , 'Oxford Street' , '伦敦') ,
       ('Bush' , 'George' , 'Fifth Avenue' , '纽约') ,
       ('Carter' , 'Thomas' , 'Changan Street' , '北京') ;

create table orders(
  o_id bigint unsigned not null auto_increment ,
  order_no bigint unsigned not null ,
  p_id bigint unsigned not null ,
  primary key (o_id)
) engine = innoDB , charset = utf8 ;
insert into orders (o_id, order_no, p_id)
values (NULL , 77895 , 3 ) ,
       (NULL , 44678 , 3 ) ,
       (NULL , 22456 , 1 ) ,
       (NULL , 24562 , 1 ) ,
       (NULL , 34764 , 66 ) ;

select * from person ;
select * from orders ;

 

内连接 （INNER JOIN）

内连接严格按照连接条件获取 A 表 ， B 表的交集。

select * from person
inner join orders
on person.p_id = orders.p_id ;

带有查询条件的内连接是可以正常执行的 ，下面这两条 SQL 的效果是一样的。

select * from person
inner join orders
on person.p_id = orders.p_id
and orders.order_no = 34764 ;


select * from person
inner join orders
on person.p_id = orders.p_id
where orders.order_no = 34764 ;


select * from person
inner join orders
on person.p_id = orders.p_id
and person.city = '北京' ;

这样的 SQL 也是合法的 ：

select * from person
inner join orders
on person.p_id = orders.p_id
and orders.order_no = 34764 
where person.city = '北京' ;

on 关键字后面的查询条件和 on 关键字共同构成了连接条件。所以连接条件不仅仅是满足 on 的查询条件即可， 要满足 on 的条件以及之后所有的 and 或者 or 条件 。

 

左连接 （LEFT JOIN）

直观的说左连接会获取到左表中的所有数据，无论左表中的数据是否符合连接条件。那么 SQL 语句中的那个表是左表呢 ？

select * from A left join B on ... 。 这个语句中 A 就是左表 ，也就是 from 关键字后面的表就是左表。

select * from person
left join orders
on person.p_id = orders.p_id ;

p_id = 2 的这条记录只有在 person 表中才有 ，orders 表中是没有 p_id = 2 的记录的 。 查询到的结果集中 orders 表中没有数据的列都用 NULL 补齐了 。

 

这个 SQL 是合理的 and orders.o_id = 3 起到了控制右表结果集的效果 。

select * from person
left join orders
on person.p_id = orders.p_id
and orders.o_id = 3;

 

下面这个 SQL and person.p_id = 2 并没有起到作用。结果集由 on person.p_id = orders.p_id 控制。

select * from person
left join orders
on person.p_id = orders.p_id
and person.p_id = 2 ;

 

下面这两个 SQL 的结果是一致的。可以发现 and person.p_id = 2 并没有起到作用。 但是同样的条件出现在 where 后面时就起作用了。

select * from person
left join orders
on person.p_id = orders.p_id
where person.p_id = 2 ;


select * from person
left join orders
on person.p_id = orders.p_id
and person.p_id = 2
where person.p_id = 2 ;

可见 left join on 之后的 and 是右表的条件时是有效的 ， and 是左表的条件时是无效的 ， 但是也起到了连接条件的作用 ，也是属于连接条件的一部分，会影响结果集的行数。最坏的情况是 on 关键字后的条件不成立 ， on 之后 and 的条件不成立 ，得到的记录数就是左表的记录数。

 

右连接 （RIGHT JOIN）

右连接会取到右表的记录 ， 即使该记录不符合连接条件。

select * from person
right join orders
on person.p_id = orders.p_id ;

在上面的 SQL 中 orders 表是"右表" , 也就是说 right join 关键字后的表是"右表" 。"右表"中的记录会查询出来，左表中不存在的记录都会用 NULL 补齐。

下面这样的 SQL 是合法有效的 。 能过过滤掉左表的记录，但是不能影响到查询出记录的行数。

select * from person
right join orders
on person.p_id = orders.p_id
and person.city = '北京';

 

下面的 SQL 使用的时候要小心了 ， 得到的结果和我们预期的并不一样 ， and orders.o_id = 1 , 这条语句并不能起到过滤 "右表" （orders 表）的作用 ， 反而会影响到查询"左表"的数据。

select * from person
right join orders
on person.p_id = orders.p_id
and orders.o_id = 1 ;

看到查询的结果 ， 除了 o_id = 1 , 行的左表记录被保留了下来 ，其余行的左表记录都被抛弃了 ， 但是总的结果集记录数以及"右表"数据是没有受到影响的。

 

全连接 （FULL JOIN）

MySQL 中不支持 full join 这样的语句 ， 所以只能使用 union ， union all 。全连接就是获取到左、右两表的并集。

union 是将两个查询结果集合并起来， union 的特点是会去除完全重复的记录 。 union all 不会去除完全重复的记录，会获取到重复的数据。

select * from person
left join orders
on person.p_id = orders.p_id
union all
select * from person
right join orders
on person.p_id = orders.p_id ;

可以看到结果集中用蓝色框线圈住的两个部分同一条记录数据是完全一致的。

 

union 是去除了完全一致的重复数据的结果集。

select * from person
left join orders
on person.p_id = orders.p_id
union
select * from person
right join orders
on person.p_id = orders.p_id ;

 

LEFT JOIN EXCLUDING INNER JOIN

看图很直观就是左表中排除了和右表的交集部分剩下的记录。

select * from person
left join orders
on person.p_id = orders.p_id
where orders.o_id is null ;

 

RIGHT JOIN EXCLUDING INNER JOIN

 

看图很直观就是右表中排除了和左表的交集部分剩下的记录。

select * from person
right join orders
on person.p_id = orders.p_id
where person.p_id is null ;

 

FULL OUTER JOIN EXCLUDING INNER JOIN

全连接排除了两表的交集剩下的记录。MySQL 中还是使用 union ， union all 语句。

select * from person
left join orders
on person.p_id = orders.p_id
where orders.o_id is null
union
select * from person
right join orders
on person.p_id = orders.p_id
where person.p_id is null ;

 

所以说 SQL JOIN 查询总共就包含以上 7 种类型 ， 7 种 JOIN 类型的总结图 ：

 

        

 

转载于:https://my.oschina.net/j4love/blog/1936254