7种SQL Join语句

1、SQL语句结构

  select  distinct  < select_list >

    from  < left_table > < join_type >

    join  < right_table >

      on  < join_condition >

   where  < where_condition >

group by  < group_by_list >

  having  < having_condition >

order by  < order_by_condition >

   limit  < limit_number >

2、7种Join方式及实例

实验脚本：

drop table IF EXISTS shuzi ;

create table shuzi (id tinyint,note varchar(20));

insert into shuzi values (1,'一'),(2,'二'),(3,'三'),(4,'四'),(5,'五'),(6,'六'),(7,'七'),(8,'八'),(9,'九'),(10,'十');

select * from shuzi;

drop table IF EXISTS qianshu ;

create table qianshu (id int,des varchar(20));

insert into qianshu values (1,'壹'),(2,'贰'),(4,'肆'),(5,'伍'),(6,'陆'),(10,'拾'),(100,'佰'),(1000,'仟'),(10000,'万');

select * from qianshu;

如果需要取的表中的字段信息为必须不为空字段，则用inner join；

如果可为空，或者可有可没有，则用left join；right join 

• 左连接，左表的全部，右表不满足的列补空

select a.id,a.note,b.id,b.des from shuzi a left join qianshu  b on a.id=b.id order by a.id;

• 右连接，右表的全部，左表不满足的列补空

select a.id,a.note,b.id,b.des from shuzi a right join qianshu  b on a.id=b.id order by b.id;

• 内连接，只输出左右表均存在的记录（默认from a,b方式）

SELECT a.id,a.note,b.id,b.des FROM shuzi a INNER JOIN qianshu  b ON a.id=b.id ORDER BY b.id;

• 左连接，只保留左表特有数据（差集）

select a.id,a.note,b.id,b.des from shuzi a left join qianshu  b on a.id=b.id where b.id is null order by a.id

• 右连接，只保留右表特有数据（差集）

SELECT a.id,a.note,b.id,b.des FROM shuzi a RIGHT JOIN qianshu  b ON a.id=b.id WHERE a.id IS NULL ORDER BY b.id;

• 全外连接，获取左右表的所有记录，各自没有时补空

mysql不支持full outer join，要实现全外连接可以通过合并左，右外连接结果集实现

select a.id,a.note,b.id,b.des from shuzi a left join qianshu  b on a.id=b.id

union

select a.id,a.note,b.id,b.des from shuzi a right join qianshu  b on a.id=b.id

• 获取两表连接交集的补集（最后一个）

SELECT * FROM (

SELECT a.id aid,a.note,b.id bid,b.des FROM shuzi a LEFT JOIN qianshu  b ON a.id=b.id

UNION

SELECT a.id aid,a.note,b.id bid,b.des FROM shuzi a RIGHT JOIN qianshu  b ON a.id=b.id) v_a

WHERE aid IS NULL OR bid IS NULL;

转载于:https://my.oschina.net/peakfang/blog/2875103