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

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

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

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

来自W3schools的一个例子:

---

---

---

---

---

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

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

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

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

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

什么是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.

定义:

---

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

join的类型:

---

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

Equi-Join: Combines common records from two tables based on equality condition. Technically, Join made by using equality-operator (=) to compare values of Primary Key of one table and Foreign Key values of another table, hence result set includes common(matched) records from both tables. For implementation see INNER-JOIN. Natural-Join: It is enhanced version of Equi-Join, in which SELECT operation omits duplicate column. For implementation see INNER-JOIN Non-Equi-Join: It is reverse of Equi-join where joining condition is uses other than equal operator(=) e.g, !=, <=, >=, >, < or BETWEEN etc. For implementation see INNER-JOIN. Self-Join:: A customized behavior of join where a table combined with itself; This is typically needed for querying self-referencing tables (or Unary relationship entity). For implementation see INNER-JOINs. Cartesian Product: It cross combines all records of both tables without any condition. Technically, it returns the result set of a query without WHERE-Clause.

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

INNER-JOIN: It merges(or combines) matched rows from two tables. The matching is done based on common columns of tables and their comparing operation. If equality based condition then: EQUI-JOIN performed, otherwise Non-EQUI-Join. OUTER-JOIN: It merges(or combines) matched rows from two tables and unmatched rows with NULL values. However, can customized selection of un-matched rows e.g, selecting unmatched row from first table or second table by sub-types: LEFT OUTER JOIN and RIGHT OUTER JOIN. 2.1. LEFT Outer JOIN (a.k.a, LEFT-JOIN): Returns matched rows from two tables and unmatched from the LEFT table(i.e, first table) only. 2.2. RIGHT Outer JOIN (a.k.a, RIGHT-JOIN): Returns matched rows from two tables and unmatched from the RIGHT table only. 2.3. FULL OUTER JOIN (a.k.a OUTER JOIN): Returns matched and unmatched from both tables. CROSS-JOIN: This join does not merges/combines instead it performs Cartesian product.

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

欲了解更多信息:

例子:

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;