图解SQL关联查询

September 20, 2016
alitrack SQL, Uncategorized

图解SQL关联查询

SQL 的连接(JOIN)语句将数据库中的两个或多个表组合起来.[1] 由”连接”生成的集合, 可以被保存为表, 或者当成表来使用. JOIN 语句的含义是把两张表的属性通过它们的值组合在一起. 基于 ANSI 标准的 SQL 列出了五种 JOIN 方式: 内连接(INNER), 全外连接(FULL OUTER), 左外连接(LEFT OUTER), 右外连接(RIGHT OUTER)和交叉连接(CROSS). 在特定的情况下, 一张表(基本表, 视图, 或连接表)可以和自身进行连接, 成为自连接(self-join).

-- 创建两个测试用表

create table tablea (
id int,
name varchar(50)
);

create table tableb (
id int,
name varchar(50)
);

insert into tablea values(1,'Pirate'),(2,'Monkey'),(3,'Ninja'),(4,'Spaghetti');

insert into tableb values(1,'Rutabaga'),(2,'Pirate'),(3,'Darth Vader'),(4,'Ninja');

 

  • Inner Join（内关联，内连接）

内连接(inner join)是应用程序中用的普遍的”连接”操作，它一般都是默认连接类型。

inner_join

-- 交集，Intersection

SELECT * FROM tablea
INNER JOIN tableb
ON tablea.name = tableb.name

id  name       id   name
--  ----       --   ----
1   Pirate     2    Pirate
3   Ninja      4    Ninja

 

 

  • Full Outer Join（全连接）

full_outer_join

-- 并集， Union

SELECT * FROM TableA
FULL OUTER JOIN TableB
ON TableA.name = TableB.name

id    name       id    name
--    ----       --    ----
1     Pirate     2     Pirate
2     Monkey     null  null
3     Ninja      4     Ninja
4     Spaghetti  null  null
null  null       1     Rutabaga
null  null       3     Darth Vader

 

  • Left Outer Join（左连接）与 Right Outer Join（右连接）

left_join

SELECT * FROM tablea
LEFT OUTER JOIN tableb
ON tablea.name = tableb.name

SELECT * FROM tableb
RIGHT OUTER JOIN tablea
ON tablea.name = tableb.name

id  name       id    name
--  ----       --    ----
1   Pirate     2     Pirate
2   Monkey     null  null
3   Ninja      4     Ninja
4   Spaghetti  null  null

left_join_1

-- 相对补集，Relative complement

SELECT * FROM tablea
LEFT OUTER JOIN tableb
ON tablea.name = tableb.name
WHERE tableb.id IS null

id  name       id     name
--  ----       --     ----
2   Monkey     null   null
4   Spaghetti  null   null

 

full_outer_join_1

-- 对称差，Symmetric difference

SELECT * FROM TableA
FULL OUTER JOIN TableB
ON TableA.name = TableB.name
WHERE TableA.id IS null
OR TableB.id IS null

id    name       id    name
--    ----       --    ----
2     Monkey     null  null
4     Spaghetti  null  null
null  null       1     Rutabaga
null  null       3     Darth Vader

 

  • 交叉连接(cross join)

又称笛卡尔连接(cartesian join)或叉乘(Product)，它是所有类型的内连接的基础。把表视为行记录的集合，交叉连接即返回这两个集合的笛卡尔积。这其实等价于内连接的链接条件为”永真”，或连接条件不存在.

SELECT * FROM tablea
CROSS JOIN tableb；

-- 或者
SELECT * FROM tablea,tableb;

--结果是一个4x4，共计16条记录

id name id name
1 Pirate 1 Rutabaga
1 Pirate 2 Pirate
1 Pirate 3 Darth Vader
1 Pirate 4 Ninja
2 Monkey 1 Rutabaga
2 Monkey 2 Pirate
2 Monkey 3 Darth Vader
2 Monkey 4 Ninja
3 Ninja 1 Rutabaga
3 Ninja 2 Pirate
3 Ninja 3 Darth Vader
3 Ninja 4 Ninja
4 Spaghetti 1 Rutabaga
4 Spaghetti 2 Pirate
4 Spaghetti 3 Darth Vader
4 Spaghetti 4 Ninja

除非你真的需要这种连接，否则往往是你的SQL写错，或者条件不完整。

 

Reference，

 

 

Leave a Reply