常见通用的join查询
我用下面两张表来演示上面七种情况:
t_nation表
t_hero表
1.A∩B (内连接 B图)
select * from t_hero as h join
t_nation n on h.hc_id=n.n_id;
2.A-B ( D图)
select * from t_hero as h left join
t_nation as n on h.hc_id=n.n_id where n.n_id is null;
3.B-A (E图)
select * from t_hero as h right join
t_nation as n on h.hc_id=n.n_id where h.h_id is null;
4.A∪B (F图 全外连接 mysql不支持)
select * from t_hero as h right join
t_nation as n on h.hc_id=n.n_id
union
select * from t_hero as h left join
t_nation as n on h.hc_id=n.n_id;
5.A-B∪ A∩B (A图 左连接)
select * from t_hero as h left join
t_nation as n on h.hc_id=n.n_id
6.B-A∪ A∩B (B图 右连接)
select * from t_hero as h right join
t_nation as n on h.hc_id=n.n_id
7.A-B∪B-A (G图)
select * from t_hero as h right join
t_nation as n on h.hc_id=n.n_id where h.hc_id is null
union
select * from t_hero as h left join
t_nation as n on h.hc_id=n.n_id where n.n_id is null;