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

问题描述

什么是 SQL JOIN 以及什么是不同的类型?

What is a SQL JOIN and what are different types?

推荐答案

什么是SQL JOIN?

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

What is SQL JOIN ?

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

总共有五个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中，我们得到两个表中符合条件的所有记录，不匹配的两个表中的记录都不报告.

1. JOIN or INNER JOIN :

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

换句话说，INNER JOIN 是基于一个事实:只应列出两个表中的匹配条目.

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

请注意，JOIN 没有任何其他 JOIN 关键字(如 INNER、OUTER、LEFT 等)是一个 INNER JOIN.换句话说， JOIN 是INNER JOIN 的语法糖(参见:JOIN 和内连接).

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

OUTER JOIN 检索

要么，一个表中匹配的行和另一个表中的所有行或者，所有表中的所有行(是否匹配并不重要).

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).

外连接分为三种:

2.1 LEFT OUTER JOIN 或 LEFT JOIN

此连接返回左表中的所有行以及来自左表的匹配行正确的表.如果右表中没有匹配的列，则返回 NULL 值.

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 或 RIGHT JOIN

这个 JOIN 返回右表中的所有行以及来自右表的匹配行左表.如果左表中没有匹配的列，则返回 NULL 值.

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 或 FULL JOIN

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

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

换句话说，OUTER JOIN 基于以下事实:仅应列出其中一个表(右或左)或两个表(完整)中的匹配条目.

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.自然加入:

它基于两个条件:

3. NATURAL JOIN :

It is based on the two conditions :

1. JOIN 是在所有具有相同名称的列上进行的.
2. 从结果中删除重复的列.

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

这在本质上似乎更具有理论性，因此(可能)大多数 DBMS甚至不用费心支持这个.

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

它是所涉及的两个表的笛卡尔积.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 won't need this at all (or needs the least, to be precise).

它不是 JOIN 的另一种形式，而是一种 JOIN (INNER, OUTER,等)的表本身.

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

根据用于JOIN 子句的运算符，可以有两种类型的JOIN.他们是

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

1. 平等加入
2. Theta 加入

1.平等加入:

对于任何 JOIN 类型(INNER、OUTER 等)，如果我们只使用相等运算符 (=)，那么我们说那JOIN 是一个 EQUI 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.

这与 EQUI JOIN 相同，但它允许所有其他运算符，例如 >、<、>= 等.

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

许多人认为 EQUI JOIN 和 Theta JOIN 类似于 INNER、OUTER等 JOIN s.但我坚信这是一个错误，并使想法模糊.因为INNER JOIN、OUTER JOIN等都与表及其数据，而 EQUI JOIN 和 THETA 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.

同样，很多人认为 NATURAL JOIN 是某种奇特的"EQUI JOIN.事实上，这是真的，因为第一个我为 NATURAL JOIN 提到的条件.然而，我们不必仅将其限制为 NATURAL JOIN .INNER JOINs, OUTER JOINsetc 也可以是 EQUI JOIN.

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.

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