SQL JOIN是什么?

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

不同的__abc是什么?

总共有5个__abc。它们是:

  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基于这样一个事实:只有两个表中匹配的条目才应该被列出。

2. 外部连接:

OUTER JOIN检索

有三种外部连接:

2.1左外连接或左连接

2.2右外连接或右连接

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

JOIN结合了LEFT OUTER JOIN和RIGHT OUTER JOIN。当条件满足时，它从任意一个表中返回行，当不匹配时返回NULL值。

换句话说，OUTER JOIN基于这样一个事实:只有在其中一个表(右或左)或两个表(FULL)中匹配的条目应该被列出。

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

3.自然连接:

它基于两个条件:

1. 为了相等，在所有具有相同名称的列上创建JOIN。
2. 从结果中删除重复的列。

4. 交叉连接:

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

5. 自接:

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

基于操作符的join

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

1. 均匀加入
2. θ加入

1. Equi JOIN:

2. Theta JOIN:

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

许多人认为EQUI JOIN和Theta JOIN都类似于INNER， OUTER 等JOINs。但我坚信这是一个错误 模糊的想法。因为INNER JOIN， OUTER JOIN等都与 表和它们的数据，而EQUI JOIN和THETA JOIN只有

同样，有许多人认为NATURAL JOIN是某种 EQUI JOIN“特有的”。事实上，这是真的，因为第一个 我为NATURAL JOIN提到的条件。然而，我们不必这样做 仅将其限制为NATURAL JOINs。INNER JOINs, OUTER JOINs

. etc也可以是EQUI JOIN

来自W3schools的示例:

.

.c "的记录

定义:

join是一种同时从多个表中查询合并数据的方法。

join的类型:

对于RDBMS来说，有5种类型的连接:

• 等值连接:根据相等条件组合两个表中的公共记录。 技术上，使用相等操作符(=)来比较一个表的主键值和另一个表的外键值，因此结果集包括来自两个表的公共(匹配的)记录。实现参见INNER-JOIN。

• Natural-Join:是Equi-Join的增强版本 操作省略重复列。实现参见INNER-JOIN

• non - equal -join:与equal -join相反，其中连接条件使用的不是相等运算符(=)，例如!=，<=， >=， >， <或BETWEEN等。实现请参见INNER-JOIN。

• 自::一个自定义的连接行为，其中表与自身合并;这通常用于查询自引用表(或一元关系实体)。 实现参见INNER-JOINs。

• 笛卡尔积:它交叉结合了两个表的所有记录，没有任何条件。从技术上讲，它返回不包含WHERE-Clause子句的查询结果集。

根据SQL的关注和进展，有3种类型的连接，所有的RDBMS连接都可以使用这些类型的连接来实现。

1. INNER-JOIN:它合并(或组合)两个表中匹配的行。匹配是基于表的公共列及其比较操作完成的。如果基于相等的条件，则执行EQUI-JOIN，否则执行非EQUI-JOIN。

2. 外连接:它合并(或组合)两个表中匹配的行和NULL值的不匹配行。但是，可以自定义选择不匹配的行，例如，从第一个表或第二个表中选择不匹配的行子类型:LEFT OUTER JOIN和RIGHT OUTER JOIN。

   2.1. 左外连接(也就是LEFT- join):从两个表返回匹配的行，从LEFT表返回未匹配的行。E，第一张表)。

   2.2. 右外连接(也就是RIGHT- join):返回两个表中匹配的行，只返回RIGHT表中未匹配的行。

   2.3. 完全外连接 (a.k.a OUTER JOIN):从两个表中返回匹配的和未匹配的

3. CROSS-JOIN:该连接不合并/组合，而是执行笛卡尔积。

注意:Self-JOIN可以根据需要通过INNER-JOIN, OUTER-JOIN和CROSS-JOIN来实现，但是表必须与自身连接

更多信息: .b

例子:

1.1: INNER-JOIN:等价连接实现

SELECT  *
FROM Table1 A
INNER JOIN Table2 B ON A.<Primary-Key> =B.<Foreign-Key>;

1.2: INNER-JOIN:自然连接实现

Select A.*, B.Col1, B.Col2          --But no B.ForeignKeyColumn in Select
FROM Table1 A
INNER JOIN Table2 B On A.Pk = B.Fk;

1.3:带非等连接实现的INNER-JOIN

Select *
FROM Table1 A INNER JOIN Table2 B On A.Pk <= B.Fk;

1.4:内部连接与自我连接

Select *
FROM Table1 A1 INNER JOIN Table1 A2 On A1.Pk = A2.Fk;

2.1: OUTER JOIN(完全外部连接)

Select *
FROM Table1 A FULL OUTER JOIN Table2 B On A.Pk = B.Fk;

2.2:左连接

Select *
FROM Table1 A LEFT OUTER JOIN Table2 B On A.Pk = B.Fk;

2.3:右连接

Select *
FROM Table1 A RIGHT OUTER JOIN Table2 B On A.Pk = B.Fk;

3.1:交叉连接

Select *
FROM TableA CROSS JOIN TableB;

3.2:交叉连接-自连接

Select *
FROM Table1 A1 CROSS JOIN Table1 A2;

/ / / /

Select *
FROM Table1 A1,Table1 A2;

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

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

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

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

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

• 它们只关注连接的基本形式
• 他们(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 JOIN

很少有人知道这一点，但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用户，那么你应该知道这个名为APPLY的JOIN类型

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。更多细节请见我的文章。请不要再用维恩图来说明它们了。