SQL JOIN和不同类型的JOIN [英] SQL JOIN and different types of JOINs

问题描述

我在 SO 和其他一些论坛上经历过许多主题。所以我想我会总结什么是SQL JOIN？和什么是不同类型的SQL JOIN？。

解决方案

什么是 SQL JOIN ？

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

有什么不同 SQL JOIN s？

共有五个 JOIN 它们是：

  1. JOIN或INNER JOIN 
 2. OUTER JOIN 
 
 LEFT OUTER JOIN或LEFT JOIN 
 2.2右外部加入或右加入
 2.3全部加入或完全加入
 
 3.自然加入
 4. CROSS JOIN 
 5. SELF JOIN 
  

1。 JOIN或INNER JOIN：

在这种 JOIN 中，我们获得所有符合条件的记录

换句话说， INNER JOIN

code>基于以下单一事实：只有在BOTH表中匹配的条目应该被列出。

注意 JOIN 没有任何其他 JOIN 关键字（如 INNER ， OUTER ， LEFT 等）是 INNER JOIN 。换句话说， INNER JOIN 是
a JOIN的语法糖（参见： JOIN和INNER JOIN之间的差异）。

2。 OUTER JOIN：

OUTER JOIN 检索

，
一个表中的匹配行和另一个表中的所有行
或者，
所有表中的所有行（无论是否存在匹配） p>

有三种外部加入：

2.1 LEFT OUTER JOIN或LEFT JOIN

此连接将返回左表中的所有行以及
右表中匹配的行。如果右表中没有匹配的列，则返回 NULL 值。

JOIN或RIGHT JOIN

此 JOIN 返回右表中的所有行，匹配
左表中的行。如果左表中没有匹配的列，则返回 NULL 值。

2.3 FULL OUTER JOIN或FULL JOIN

此 JOIN 结合 LEFT OUTER JOIN 和 RIGHT OUTER JOIN 。当满足
条件时，它从任一表返回行，并且在没有匹配时返回 NULL 。

换句话说， OUTER JOIN 是基于以下事实：只有一个表中的匹配条目（RIGHT或LEFT）
或BOTH

 请注意，OUTER JOIN是一个松散的形式的INNER JOIN。 
  

3。 NATURAL JOIN：

它基于以下两个条件：

<

1. 从结果中删除重复的列。

这似乎更多的是理论性的，结果（可能）大多数DBMS
甚至不打扰支持这个。 >

4。 CROSS JOIN：

这是两个表的笛卡尔乘积。在大多数情况下， CROSS JOIN 的结果将没有意义
。此外，我们根本不需要这个（或需要最少，更精确）。

5。 SELF JOIN：

它不是 JOIN 的不同形式，而是 JOIN （ INNER ， OUTER 等） >

基于运算符的JOIN

根据 JOIN 子句，可以有两种类型 JOIN s。它们是

1. Equi JOIN


2. Theta JOIN

1。 Equi JOIN：

对于 JOIN 类型（ INNER ， OUTER 等），如果我们只使用相等运算符（=），那么我们说
JOIN 是 EQUI JOIN 。

2。 Theta JOIN：

这与 EQUI JOIN 相同，但它允许所有其他运算符，如>，< = etc。

许多人认为 EQUI JOIN 和Theta JOIN 类似 INNER ， OUTER
etc JOIN 。但我坚信，它的一个错误，使得
的想法模糊。因为 INNER JOIN ， OUTER JOIN 等都与
连接表和其数据，其中 EQUI JOIN 和 THETA JOIN 只有
与我们在前者使用的运算符连接。

同样，有很多人认为 NATURAL JOIN 是某种
特殊的 EQUI JOIN 。事实上，这是真的，因为第一个
条件我提到 NATURAL JOIN 。然而，我们不必
限制只是到 NATURAL JOIN 单独。 INNER JOIN s， OUTER JOIN s
等可以是 EQUI JOIN 。

I have been going through many threads on SO and some other forums. So I thought I would summarize "What is SQL JOIN?" and "What are different types of SQL JOINs?".

解决方案

What is SQL JOIN ?

SQL JOIN is a method to retrieve data from two or more database tables.

What are the different SQL JOINs ?

There are a total of five JOINs. They are :

  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 or INNER JOIN :

In this kind of a JOIN, we get all records that match the condition in both the tables, and records in both the tables that do not match are not reported.

In other words, INNER JOIN is based on the single fact that : ONLY the matching entries in BOTH the tables SHOULD be listed.

Note that a JOIN without any other JOIN keywords (like INNER, OUTER, LEFT, etc) is an INNER JOIN. In other words, INNER JOIN is a Syntactic sugar for JOIN (see : Difference between JOIN and INNER JOIN).

2. OUTER JOIN :

OUTER JOIN retrieves

Either, the matched rows from one table and all rows in the other table Or, all rows in all tables (it doesn't matter whether or not there is a match).

There are three kinds of Outer Join :

2.1 LEFT OUTER JOIN or LEFT JOIN

This join returns all the rows from the left table in conjunction with the matching rows from the right table. If there are no columns matching in the right table, it returns NULL values.

2.2 RIGHT OUTER JOIN or RIGHT JOIN

This JOIN returns all the rows from the right table in conjunction with the matching rows from the left table. If there are no columns matching in the left table, it returns NULL values.

2.3 FULL OUTER JOIN or FULL JOIN

This JOIN combines LEFT OUTER JOIN and RIGHT OUTER JOIN. It returns row from either table when the conditions are met and returns NULL value when there is no match.

In other words, OUTER JOIN is based on the fact that : ONLY the matching entries in ONE OF the tables (RIGHT or LEFT) or BOTH of the tables(FULL) SHOULD be listed.

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

3. NATURAL JOIN :

It is based on the two conditions :

1. the JOIN is made on all the columns with the same name for equality.
2. Removes duplicate columns from the result.

This seems to be more of theoretical in nature and as a result (probably) most DBMS don't even bother supporting this.

4. CROSS JOIN :

It is the Cartesian product of the two tables involved. The result of a CROSS JOIN will not make sense in most of the situations. Moreover, we wont need this at all (or needs the least, to be precise).

5. SELF JOIN :

It is not a different form of JOIN, rather it is a JOIN (INNER, OUTER, etc) of a table to itself.

JOINs based on Operators

Depending on the operator used for a JOIN clause, there can be two types of JOINs. They are

1. Equi JOIN
2. Theta JOIN

1. Equi JOIN :

For whatever JOIN type (INNER, OUTER, etc), if we use ONLY the equality operator (=), then we say that the JOIN is an EQUI JOIN.

2. Theta JOIN :

This is same as EQUI JOIN but it allows all other operators like >, <, >= etc.

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 where as 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 dont have to restrict that simply to NATURAL JOINs alone. INNER JOINs, OUTER JOINs etc could be an EQUI JOIN too.

这篇关于SQL JOIN和不同类型的JOIN的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！