SQL JOIN和不同类型的JOIN

什么是SQL JOIN，有哪些不同的类型?

当前回答

什么是SQL JOIN ?

SQL JOIN是从两个或多个数据库表中检索数据的方法。

有哪些不同的SQL连接?

总共有5个join。它们是:

  1. JOIN or INNER JOIN
  2. OUTER JOIN

     2.1 LEFT OUTER JOIN or LEFT JOIN
     2.2 RIGHT OUTER JOIN or RIGHT JOIN
     2.3 FULL OUTER JOIN or FULL JOIN

  3. NATURAL JOIN
  4. CROSS JOIN
  5. SELF JOIN

1. 连接或内连接:

在这种JOIN中，我们获得两个表中与条件匹配的所有记录，而两个表中不匹配的记录将不被报告。

换句话说，INNER JOIN基于这样一个事实:只有两个表中匹配的条目才应该被列出。

注意，没有任何其他JOIN关键字(如INNER, OUTER, LEFT等)的JOIN是INNER JOIN。换句话说，JOIN是内部连接的语法糖(参见:连接和内部连接的区别)。

2. 外部连接:

OUTER JOIN检索

要么, 一个表中的匹配行和另一个表中的所有行或者, 所有表中的所有行(是否匹配无关紧要)。

有三种外部连接:

2.1左外连接或左连接

属性中的匹配行返回左表中的所有行正确的表。如果在正确的表中没有匹配的列，则返回NULL值。

2.2右外连接或右连接

属性中的匹配行返回来自正确表的所有行左表。如果左侧表中没有匹配的列，则返回NULL值。

2.3完全外部连接或完全连接

这个连接结合了左外连接和右外连接。当满足条件时，它从任意一个表中返回行，当不匹配时返回NULL值。

换句话说，OUTER JOIN基于以下事实:只列出其中一个表(右或左)或两个表(FULL)中的匹配项。

Note that `OUTER JOIN` is a loosened form of `INNER JOIN`.

3.自然连接:

它基于两个条件:

为了相等，在所有具有相同名称的列上执行JOIN。从结果中删除重复的列。

这在本质上似乎更多的是理论的，因此(可能)大多数DBMS 不要支持这个。

4. 交叉连接:

它是两个表的笛卡尔积。CROSS JOIN的结果没有意义在大多数情况下。此外，我们根本不需要这些(确切地说，至少不需要)。

5. 自接:

它不是JOIN的另一种形式，而是表对自身的JOIN (INNER、OUTER等)。

基于操作符的join

根据用于JOIN子句的操作符，可以有两种类型的JOIN。他们是

均匀加入 θ加入

1. Equi JOIN:

对于任何JOIN类型(INNER, OUTER等)，如果我们只使用相等操作符(=)，那么我们说 JOIN是EQUI JOIN。

2. Theta JOIN:

这与EQUI JOIN相同，但它允许所有其他操作符，如>，<，>=等。

Many consider both EQUI JOIN and Theta JOIN similar to INNER, OUTER etc JOINs. But I strongly believe that its a mistake and makes the ideas vague. Because INNER JOIN, OUTER JOIN etc are all connected with the tables and their data whereas EQUI JOIN and THETA JOIN are only connected with the operators we use in the former. Again, there are many who consider NATURAL JOIN as some sort of "peculiar" EQUI JOIN. In fact, it is true, because of the first condition I mentioned for NATURAL JOIN. However, we don't have to restrict that simply to NATURAL JOINs alone. INNER JOINs, OUTER JOINs etc could be an EQUI JOIN too.

2013-07-30 11:48:12

其他回答

有趣的是，大多数其他答案都存在以下两个问题:

它们只关注连接的基本形式他们(ab)使用维恩图，这是一个不准确的工具来可视化连接(他们更适合于联合)。

我最近写了一篇关于这个主题的文章:关于在SQL中连接表的许多不同方法的可能不完整的全面指南，我将在这里总结。

首先也是最重要的:join是笛卡尔积

这就是为什么维恩图解释得如此不准确，因为JOIN在两个连接的表之间创建了一个笛卡尔积。维基百科很好地说明了这一点:

笛卡尔积的SQL语法是CROSS JOIN。例如:

SELECT *

-- This just generates all the days in January 2017
FROM generate_series(
  '2017-01-01'::TIMESTAMP,
  '2017-01-01'::TIMESTAMP + INTERVAL '1 month -1 day',
  INTERVAL '1 day'
) AS days(day)

-- Here, we're combining all days with all departments
CROSS JOIN departments

它将一个表中的所有行与另一个表中的所有行组合在一起:

来源:

+--------+   +------------+
| day    |   | department |
+--------+   +------------+
| Jan 01 |   | Dept 1     |
| Jan 02 |   | Dept 2     |
| ...    |   | Dept 3     |
| Jan 30 |   +------------+
| Jan 31 |
+--------+

结果:

+--------+------------+
| day    | department |
+--------+------------+
| Jan 01 | Dept 1     |
| Jan 01 | Dept 2     |
| Jan 01 | Dept 3     |
| Jan 02 | Dept 1     |
| Jan 02 | Dept 2     |
| Jan 02 | Dept 3     |
| ...    | ...        |
| Jan 31 | Dept 1     |
| Jan 31 | Dept 2     |
| Jan 31 | Dept 3     |
+--------+------------+

如果我们只是写一个逗号分隔的表列表，我们会得到相同的结果:

-- CROSS JOINing two tables:
SELECT * FROM table1, table2

内部连接(Theta-JOIN)

INNER JOIN只是一个经过过滤的CROSS JOIN，其中过滤器谓词在关系代数中称为Theta。

例如:

SELECT *

-- Same as before
FROM generate_series(
  '2017-01-01'::TIMESTAMP,
  '2017-01-01'::TIMESTAMP + INTERVAL '1 month -1 day',
  INTERVAL '1 day'
) AS days(day)

-- Now, exclude all days/departments combinations for
-- days before the department was created
JOIN departments AS d ON day >= d.created_at

注意关键字INNER是可选的(在MS Access中除外)。

(请参阅文章中的结果示例)

均匀加入

一种特殊的Theta-JOIN是我们最常用的equi JOIN。谓词将一个表的主键与另一个表的外键连接起来。如果我们使用Sakila数据库进行说明，我们可以这样写:

SELECT *
FROM actor AS a
JOIN film_actor AS fa ON a.actor_id = fa.actor_id
JOIN film AS f ON f.film_id = fa.film_id

这结合了所有演员和他们的电影。

或者，在一些数据库中:

SELECT *
FROM actor
JOIN film_actor USING (actor_id)
JOIN film USING (film_id)

USING()语法允许指定必须出现在JOIN操作表两侧的列，并在这两列上创建一个相等谓词。

自然的加入

其他答案单独列出了这个“JOIN类型”，但这没有意义。它只是equi JOIN的语法糖形式，它是Theta-JOIN或INNER JOIN的一种特殊情况。NATURAL JOIN简单地收集被连接的表和USING()连接这些列所共有的所有列。这几乎没什么用，因为会出现意外匹配(比如Sakila数据库中的LAST_UPDATE列)。

语法如下:

SELECT *
FROM actor
NATURAL JOIN film_actor
NATURAL JOIN film

外连接

现在，OUTER JOIN与INNER JOIN有点不同，因为它创建了几个笛卡尔积的UNION。我们可以写成:

-- Convenient syntax:
SELECT *
FROM a LEFT JOIN b ON <predicate>

-- Cumbersome, equivalent syntax:
SELECT a.*, b.*
FROM a JOIN b ON <predicate>
UNION ALL
SELECT a.*, NULL, NULL, ..., NULL
FROM a
WHERE NOT EXISTS (
  SELECT * FROM b WHERE <predicate>
)

没有人想要编写后者，所以我们编写OUTER JOIN(通常由数据库更好地优化)。

与INNER一样，关键字OUTER在这里也是可选的。

OUTER JOIN有三种口味:

LEFT [OUTER] JOIN: JOIN表达式的左表被添加到联合中，如上所示。 RIGHT [OUTER] JOIN:将JOIN表达式的右边表添加到联合中，如上图所示。 FULL [OUTER] JOIN:如上所示，JOIN表达式的两个表都被添加到联合中。

所有这些都可以与关键字USING()或NATURAL组合(我最近实际上有一个NATURAL FULL JOIN的真实用例)

替代语法

在Oracle和SQL Server中有一些历史悠久的，已弃用的语法，在SQL标准有此语法之前，它们已经支持OUTER JOIN:

-- Oracle
SELECT *
FROM actor a, film_actor fa, film f
WHERE a.actor_id = fa.actor_id(+)
AND fa.film_id = f.film_id(+)

-- SQL Server
SELECT *
FROM actor a, film_actor fa, film f
WHERE a.actor_id *= fa.actor_id
AND fa.film_id *= f.film_id

话虽如此，但不要使用这种语法。我只是在这里列出它，以便您可以从旧的博客文章/遗留代码中识别它。

分区OUTER连接

很少有人知道这一点，但是SQL标准指定了分区OUTER JOIN (Oracle实现了它)。你可以这样写:

WITH

  -- Using CONNECT BY to generate all dates in January
  days(day) AS (
    SELECT DATE '2017-01-01' + LEVEL - 1
    FROM dual
    CONNECT BY LEVEL <= 31
  ),

  -- Our departments
  departments(department, created_at) AS (
    SELECT 'Dept 1', DATE '2017-01-10' FROM dual UNION ALL
    SELECT 'Dept 2', DATE '2017-01-11' FROM dual UNION ALL
    SELECT 'Dept 3', DATE '2017-01-12' FROM dual UNION ALL
    SELECT 'Dept 4', DATE '2017-04-01' FROM dual UNION ALL
    SELECT 'Dept 5', DATE '2017-04-02' FROM dual
  )
SELECT *
FROM days 
LEFT JOIN departments 
  PARTITION BY (department) -- This is where the magic happens
  ON day >= created_at

结果如下:

+--------+------------+------------+
| day    | department | created_at |
+--------+------------+------------+
| Jan 01 | Dept 1     |            | -- Didn't match, but still get row
| Jan 02 | Dept 1     |            | -- Didn't match, but still get row
| ...    | Dept 1     |            | -- Didn't match, but still get row
| Jan 09 | Dept 1     |            | -- Didn't match, but still get row
| Jan 10 | Dept 1     | Jan 10     | -- Matches, so get join result
| Jan 11 | Dept 1     | Jan 10     | -- Matches, so get join result
| Jan 12 | Dept 1     | Jan 10     | -- Matches, so get join result
| ...    | Dept 1     | Jan 10     | -- Matches, so get join result
| Jan 31 | Dept 1     | Jan 10     | -- Matches, so get join result

这里的重点是，无论join是否匹配“join的另一端”上的任何内容，来自连接的已分区一侧的所有行都将在结果中结束。长话短说:这就是在报告中填充稀疏数据。非常有用!

半连接

严重吗?没有其他答案了吗?当然不是，因为它在SQL中没有原生语法，很不幸(就像下面的ANTI JOIN一样)。但我们可以使用IN()和EXISTS()，例如，找到所有在电影中演出过的演员:

SELECT *
FROM actor a
WHERE EXISTS (
  SELECT * FROM film_actor fa
  WHERE a.actor_id = fa.actor_id
)

WHERE a.actor_id = fa。Actor_id谓词充当半连接谓词。如果你不相信，看看执行计划，比如Oracle。您将看到数据库执行了一个SEMI JOIN操作，而不是EXISTS()谓词。

反加入

这与SEMI JOIN正好相反(注意不要使用not IN，因为它有一个重要的警告)

以下是所有没有拍过电影的演员:

SELECT *
FROM actor a
WHERE NOT EXISTS (
  SELECT * FROM film_actor fa
  WHERE a.actor_id = fa.actor_id
)

有些人(尤其是MySQL的人)也会这样写ANTI - JOIN:

SELECT *
FROM actor a
LEFT JOIN film_actor fa
USING (actor_id)
WHERE film_id IS NULL

我认为历史原因是表现。

横向连接

天哪，这个太酷了。只有我一个人提起这件事?这是一个很酷的问题:

SELECT a.first_name, a.last_name, f.*
FROM actor AS a
LEFT OUTER JOIN LATERAL (
  SELECT f.title, SUM(amount) AS revenue
  FROM film AS f
  JOIN film_actor AS fa USING (film_id)
  JOIN inventory AS i USING (film_id)
  JOIN rental AS r USING (inventory_id)
  JOIN payment AS p USING (rental_id)
  WHERE fa.actor_id = a.actor_id -- JOIN predicate with the outer query!
  GROUP BY f.film_id
  ORDER BY revenue DESC
  LIMIT 5
) AS f
ON true

它将找出每位演员收入最高的5部电影。每当你需要一个TOP-N-per-something查询时，LATERAL JOIN将是你的朋友。如果您是SQL Server使用者，那么您应该知道这个JOIN类型的名称是APPLY

SELECT a.first_name, a.last_name, f.*
FROM actor AS a
OUTER APPLY (
  SELECT f.title, SUM(amount) AS revenue
  FROM film AS f
  JOIN film_actor AS fa ON f.film_id = fa.film_id
  JOIN inventory AS i ON f.film_id = i.film_id
  JOIN rental AS r ON i.inventory_id = r.inventory_id
  JOIN payment AS p ON r.rental_id = p.rental_id
  WHERE fa.actor_id = a.actor_id -- JOIN predicate with the outer query!
  GROUP BY f.film_id
  ORDER BY revenue DESC
  LIMIT 5
) AS f

好吧，也许这是欺骗，因为LATERAL JOIN或APPLY表达式实际上是一个产生几行的“相关子查询”。但是如果我们允许“相关子查询”，我们还可以讨论……

多重集

这实际上只由Oracle和Informix实现(据我所知)，但它可以在PostgreSQL中使用数组和/或XML以及在SQL Server中使用XML进行模拟。

MULTISET生成一个相关的子查询，并将结果行集嵌套在外部查询中。下面的查询选择所有演员，并为每个演员在嵌套集合中收集他们的电影:

SELECT a.*, MULTISET (
  SELECT f.*
  FROM film AS f
  JOIN film_actor AS fa USING (film_id)
  WHERE a.actor_id = fa.actor_id
) AS films
FROM actor

正如您所看到的，除了通常提到的“无聊的”INNER、OUTER和CROSS JOIN之外，还有更多类型的JOIN。更多细节请见我的文章。请不要再用维恩图来说明它们了。

2017-04-21 17:08:07

在我看来，我创造了一个比文字更能解释的插图:

2015-08-12 22:22:18

我要推一下我最讨厌的:USING关键字。

如果JOIN两边的表都有正确命名的外键(即，相同的名称，而不仅仅是“id”)，那么可以使用:

SELECT ...
FROM customers JOIN orders USING (customer_id)

我发现这是非常实用的，可读的，但不经常使用。

2017-03-29 13:43:39