问题描述
什么是 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
isa 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 :
JOIN
是在所有具有相同名称的列上进行的.- 从结果中删除重复的列.
- the
JOIN
is made on all the columns with the same name for equality. - Removes duplicate columns from the result.
这在本质上似乎更具有理论性,因此(可能)大多数 DBMS甚至不用费心支持这个.
This seems to be more of theoretical in nature and as a result (probably) most DBMSdon'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 sensein 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 JOIN
s. They are
- 平等加入
- 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 thatthe 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
只是与我们在前者中使用的运算符相关联.
同样,很多人认为 NATURAL JOIN
是某种奇特的"EQUI JOIN
.事实上,这是真的,因为第一个我为 NATURAL JOIN
提到的条件.然而,我们不必仅将其限制为 NATURAL JOIN
.INNER JOIN
s, OUTER JOIN
setc 也可以是 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 JOIN
s alone. INNER JOIN
s, OUTER JOIN
s etc could be an EQUI JOIN
too.
这篇关于SQL JOIN 和不同类型的 JOIN的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!