SQL “内连接”和“外连接”有什么区别？

Question

提问by Chris de Vries

Also how do LEFT JOIN, RIGHT JOINand FULL JOINfit in?

此外怎么办LEFT JOIN，RIGHT JOIN和FULL JOIN适合吗？

Answer 1

回答by Mark Harrison

Assuming you're joining on columns with no duplicates, which is a very common case:

假设您加入没有重复的列，这是一个非常常见的情况：

An inner join of A and B gives the result of A intersect B, i.e. the inner part of a Venn diagramintersection.
An outer join of A and B gives the results of A union B, i.e. the outer parts of a Venn diagram union.

A 和 B 的内部连接给出了 A 与 B 相交的结果，即维恩图相交的内部部分。
A 和 B 的外连接给出了 A 联合 B 的结果，即维恩图联合的外部部分。

Examples

例子

Suppose you have two tables, with a single column each, and data as follows:

假设您有两个表，每个表只有一列，数据如下：

Note that (1,2) are unique to A, (3,4) are common, and (5,6) are unique to B.

请注意，(1,2) 是 A 独有的，(3,4) 是通用的，而 (5,6) 是 B 独有的。

Inner join

内部联接

An inner join using either of the equivalent queries gives the intersection of the two tables, i.e. the two rows they have in common.

使用任一等效查询的内部联接给出两个表的交集，即它们共有的两行。

select * from a INNER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a = b.b;

a | b
--+--
3 | 3
4 | 4

Left outer join

左外连接

A left outer join will give all rows in A, plus any common rows in B.

左外连接将给出 A 中的所有行，以及 B 中的所有公共行。

select * from a LEFT OUTER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a = b.b(+);

a |  b
--+-----
1 | null
2 | null
3 |    3
4 |    4

Right outer join

右外连接

A right outer join will give all rows in B, plus any common rows in A.

右外连接将给出 B 中的所有行，以及 A 中的任何公共行。

select * from a RIGHT OUTER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a(+) = b.b;

a    |  b
-----+----
3    |  3
4    |  4
null |  5
null |  6

Full outer join

全外连接

A full outer join will give you the union of A and B, i.e. all the rows in A and all the rows in B. If something in A doesn't have a corresponding datum in B, then the B portion is null, and vice versa.

一个完整的外连接会给你 A 和 B 的联合，即 A 中的所有行和 B 中的所有行。如果 A 中的某些内容在 B 中没有相应的数据，则 B 部分为空，反之亦然反之。

select * from a FULL OUTER JOIN b on a.a = b.b;

 a   |  b
-----+-----
   1 | null
   2 | null
   3 |    3
   4 |    4
null |    6
null |    5

Answer 2

回答by Martin Smith

The Venn diagrams don't really do it for me.

维恩图并不适合我。

They don't show any distinction between a cross join and an inner join, for example, or more generally show any distinction between different types of join predicate or provide a framework for reasoning about how they will operate.

例如，它们没有显示交叉连接和内部连接之间的任何区别，或者更普遍地显示不同类型的连接谓词之间的任何区别或提供推理它们将如何操作的框架。

There is no substitute for understanding the logical processing and it is relatively straightforward to grasp anyway.

理解逻辑处理是无可替代的，无论如何掌握起来都相对简单。

Imagine a cross join.
Evaluate the onclause against all rows from step 1 keeping those where the predicate evaluates to true
(For outer joins only) add back in any outer rows that were lost in step 2.

想象一个交叉连接。
on针对步骤 1 中的所有行评估子句，保留谓词计算结果为true
（仅适用于外部联接）在步骤 2 中丢失的任何外部行中重新添加。

(NB: In practice the query optimiser may find more efficient ways of executing the query than the purely logical description above but the final result must be the same)

（注意：实际上，查询优化器可能会找到比上面纯逻辑描述更有效的查询执行方式，但最终结果必须相同）

I'll start off with an animated version of a full outer join. Further explanation follows.

我将从一个完整的外连接的动画版本开始。进一步的解释如下。

Explanation

解释

Source Tables

源表

enter link description here

在此处输入链接描述

First start with a CROSS JOIN(AKA Cartesian Product). This does not have an ONclause and simply returns every combination of rows from the two tables.

首先从CROSS JOIN（又名笛卡尔积）开始。这没有ON子句，只是返回两个表中行的每个组合。

SELECT A.Colour, B.Colour FROM A CROSS JOIN B

SELECT A.Colour, B.Colour from A CROSS JOIN B

enter link description here

在此处输入链接描述

Inner and Outer joins have an "ON" clause predicate.

内连接和外连接有一个“ON”子句谓词。

Inner Join.Evaluate the condition in the "ON" clause for all rows in the cross join result. If true return the joined row. Otherwise discard it.
Left Outer Join.Same as inner join then for any rows in the left table that did not match anything output these with NULL values for the right table columns.
Right Outer Join.Same as inner join then for any rows in the right table that did not match anything output these with NULL values for the left table columns.
Full Outer Join.Same as inner join then preserve left non matched rows as in left outer join and right non matching rows as per right outer join.

内部联接。为交叉连接结果中的所有行评估“ON”子句中的条件。如果为 true，则返回连接的行。否则丢弃它。
左外连接。与内部连接相同，然后对于左表中不匹配任何内容的任何行，将这些行输出为右表列的 NULL 值。
右外连接。与内部联接相同，然后对于右表中不匹配任何内容的任何行，将这些行输出为左表列的 NULL 值。
完全外连接。与内连接相同，然后保留左外连接中的左非匹配行和右外连接中的右非匹配行。

Some examples

一些例子

SELECT A.Colour, B.Colour FROM A INNER JOIN B ON A.Colour = B.Colour

SELECT A.Colour, B.Colour from A INNER JOIN B ON A.Colour = B.Colour

The above is the classic equi join.

以上是经典的equi join。

Inner Join

内部联接

Animated Version

动画版

SELECT A.Colour, B.Colour FROM A INNER JOIN B ON A.Colour NOT IN ('Green','Blue')

SELECT A.Colour, B.Colour from A INNER JOIN B ON A.Colour NOT IN ('Green','Blue')

The inner join condition need not necessarily be an equality condition and it need not reference columns from both (or even either) of the tables. Evaluating A.Colour NOT IN ('Green','Blue')on each row of the cross join returns.

内连接条件不一定是相等条件，也不需要引用来自两个（或什至任一）表的列。A.Colour NOT IN ('Green','Blue')对交叉联接返回的每一行进行评估。

inner 2

内部 2

SELECT A.Colour, B.Colour FROM A INNER JOIN B ON 1 =1

SELECT A.Colour, B.Colour from A INNER JOIN B ON 1 =1

The join condition evaluates to true for all rows in the cross join result so this is just the same as a cross join. I won't repeat the picture of the 16 rows again.

对于交叉联接结果中的所有行，联接条件的计算结果为真，因此这与交叉联接相同。16行的图我就不重复了。

SELECT A.Colour, B.Colour FROM A LEFT OUTER JOIN B ON A.Colour = B.Colour

从 A.Colour = B.Colour 上的 A LEFT OUTER JOIN B 中选择 A.Colour, B.Colour

Outer Joins are logically evaluated in the same way as inner joins except that if a row from the left table (for a left join) does not join with any rows from the right hand table at all it is preserved in the result with NULLvalues for the right hand columns.

外连接的逻辑评估方式与内连接相同，不同之处在于如果左表中的一行（对于左连接）根本不与右手表中的任何行连接，它会保留在结果中，其NULL值为右手边的列。

LOJ

SELECT A.Colour, B.Colour FROM A LEFT OUTER JOIN B ON A.Colour = B.Colour WHERE B.Colour IS NULL

SELECT A.Colour, B.Colour FROM A LEFT OUTER JOIN B ON A.Colour = B.Colour WHERE B.Colour 为 NULL

This simply restricts the previous result to only return the rows where B.Colour IS NULL. In this particular case these will be the rows that were preserved as they had no match in the right hand table and the query returns the single red row not matched in table B. This is known as an anti semi join.

这只是将先前的结果限制为仅返回 where 的行B.Colour IS NULL。在这种特殊情况下，这些将是保留的行，因为它们在右侧表中没有匹配项，并且查询返回表中不匹配的单个红色行B。这被称为反半连接。

It is important to select a column for the IS NULLtest that is either not nullable or for which the join condition ensures that any NULLvalues will be excluded in order for this pattern to work correctly and avoid just bringing back rows which happen to have a NULLvalue for that column in addition to the un matched rows.

为IS NULL测试选择一列不可为空或连接条件确保NULL将排除任何值以便此模式正常工作并避免仅带回恰好具有该NULL值的行很重要除了未匹配的行之外的列。

loj is null

loj 为空

SELECT A.Colour, B.Colour FROM A RIGHT OUTER JOIN B ON A.Colour = B.Colour

SELECT A.Colour, B.Colour from A RIGHT OUTER JOIN B on A.Colour = B.Colour

Right outer joins act similarly to left outer joins except they preserve non matching rows from the right table and null extend the left hand columns.

右外连接的作用类似于左外连接，除了它们保留右表中不匹配的行并且 null 扩展左手列。

ROJ

注册会计师

SELECT A.Colour, B.Colour FROM A FULL OUTER JOIN B ON A.Colour = B.Colour

从 A.Colour = B.Colour 上的完全外部连接 B 中选择 A.Colour、B.Colour

Full outer joins combine the behaviour of left and right joins and preserve the non matching rows from both the left and the right tables.

全外连接结合了左右连接的行为，并保留了左右表中不匹配的行。

FOJ

SELECT A.Colour, B.Colour FROM A FULL OUTER JOIN B ON 1 = 0

SELECT A.Colour, B.Colour from A FULL OUTER JOIN B ON 1 = 0

No rows in the cross join match the 1=0predicate. All rows from both sides are preserved using normal outer join rules with NULL in the columns from the table on the other side.

交叉联接中没有任何行与1=0谓词匹配。两侧的所有行都使用普通外连接规则保留，另一侧表的列中为 NULL。

FOJ 2

SELECT COALESCE(A.Colour, B.Colour) AS Colour FROM A FULL OUTER JOIN B ON 1 = 0

SELECT COALESCE(A.Colour, B.Colour) 作为颜色从一个完整的外部连接 B ON 1 = 0

With a minor amend to the preceding query one could simulate a UNION ALLof the two tables.

对前面的查询稍作修改，就可以模拟UNION ALL两个表中的一个。

UNION ALL

联合所有

SELECT A.Colour, B.Colour FROM A LEFT OUTER JOIN B ON A.Colour = B.Colour WHERE B.Colour = 'Green'

在 A.Colour = B.Colour WHERE B.Colour = 'Green' 上从 A LEFT OUTER JOIN B 中选择 A.Colour, B.Colour

Note that the WHEREclause (if present) logically runs after the join. One common error is to perform a left outer join and then include a WHERE clause with a condition on the right table that ends up excluding the non matching rows. The above ends up performing the outer join...

请注意，WHERE子句（如果存在）在连接之后在逻辑上运行。一个常见的错误是执行左外连接，然后在右表上包含一个 WHERE 子句，该子句最终排除了不匹配的行。以上最终执行了外连接......

LOJ

... And then the "Where" clause runs. NULL= 'Green'does not evaluate to true so the row preserved by the outer join ends up discarded (along with the blue one) effectively converting the join back to an inner one.

...然后“Where”子句运行。NULL= 'Green'不评估为真，因此外部连接保留的行最终被丢弃（连同蓝色）有效地将连接转换回内部连接。

LOJtoInner

LOJ到内部

If the intention was to include only rows from B where Colour is Green and all rows from A regardless the correct syntax would be

如果意图是仅包括来自 B 的行，其中颜色为绿色，而不管正确的语法是来自 A 的所有行

SELECT A.Colour, B.Colour FROM A LEFT OUTER JOIN B ON A.Colour = B.Colour AND B.Colour = 'Green'

从 A.Colour = B.Colour AND B.Colour = 'Green' 上的 A LEFT OUTER JOIN B 中选择 A.Colour, B.Colour

enter image description here

在此处输入图片说明

SQL Fiddle

SQL小提琴

See these examples run live at SQLFiddle.com.

请参阅SQLFiddle.com 上实时运行的这些示例。

Answer 3

回答by ajitksharma

Joinsare used to combine the data from two tables, with the result being a new, temporary table. Joins are performed based on something called a predicate, which specifies the condition to use in order to perform a join. The difference between an inner join and an outer join is that an inner join will return only the rows that actually match based on the join predicate. For eg- Lets consider Employee and Location table:

联接用于组合来自两个表的数据，结果是一个新的临时表。连接是基于谓词执行的，谓词指定要使用的条件以执行连接。内连接和外连接之间的区别在于内连接将仅返回基于连接谓词实际匹配的行。例如，让我们考虑 Employee 和 Location 表：

enter image description here

在此处输入图片说明

Inner Join:-Inner join creates a new result table by combining column values of two tables (Employeeand Location) based upon the join-predicate. The query compares each row of Employeewith each row of Locationto find all pairs of rows which satisfy the join-predicate. When the join-predicate is satisfied by matching non-NULL values, column values for each matched pair of rows of Employeeand Locationare combined into a result row. Here's what the SQL for an inner join will look like:

内连接：-内连接通过基于连接谓词组合两个表（Employee和Location）的列值来创建一个新的结果表。该查询将Employee的每一行与Location 的每一行进行比较，以找到满足连接谓词的所有行对。当通过匹配非 NULL 值满足连接谓词时，每个匹配的Employee和Location行对的列值组合成一个结果行。下面是用于内部联接的 SQL 的样子：

select  * from employee inner join location on employee.empID = location.empID
OR
select  * from employee, location where employee.empID = location.empID

Now, here is what the result of running that SQL would look like: enter image description here

现在，这是运行该 SQL 的结果：在此处输入图片说明

Outer Join:-An outer join does not require each record in the two joined tables to have a matching record. The joined table retains each record—even if no other matching record exists. Outer joins subdivide further into left outer joins and right outer joins, depending on which table's rows are retained (left or right).

外连接：-外连接不需要两个连接表中的每条记录都有匹配的记录。连接表保留每条记录——即使不存在其他匹配记录。外连接进一步细分为左外连接和右外连接，具体取决于保留哪个表的行（左或右）。

Left Outer Join:-The result of a left outer join (or simply left join) for tables Employeeand Locationalways contains all records of the "left" table (Employee), even if the join-condition does not find any matching record in the "right" table (Location). Here is what the SQL for a left outer join would look like, using the tables above:

左外连接：-表Employee和Location的左外连接（或简单的左连接）的结果总是包含“左”表（Employee）的所有记录，即使连接条件在其中没有找到任何匹配的记录“右”表（Location）。下面是左外连接的 SQL 的样子，使用上表：

select  * from employee left outer join location on employee.empID = location.empID;
//Use of outer keyword is optional

Now, here is what the result of running this SQL would look like: enter image description here

现在，这是运行此 SQL 的结果：在此处输入图片说明

Right Outer Join:-A right outer join (or right join) closely resembles a left outer join, except with the treatment of the tables reversed. Every row from the "right" table (Location) will appear in the joined table at least once. If no matching row from the "left" table (Employee) exists, NULL will appear in columns from Employeefor those records that have no match in Location. This is what the SQL looks like:

右外连接：-右外连接（或右连接）与左外连接非常相似，但表的处理方式相反。“右”表 (Location) 中的每一行都将至少出现在连接表中一次。如果“左”表 (Employee) 中不存在匹配的行，则 NULL 将出现在Employee 的列中，这些记录在Location 中没有匹配项。这是 SQL 的样子：

select * from employee right outer join location  on employee.empID = location.empID;
//Use of outer keyword is optional

Using the tables above, we can show what the result set of a right outer join would look like:

使用上表，我们可以显示右外连接的结果集会是什么样子：

enter image description here

在此处输入图片说明

Full Outer Joins:-Full Outer Join or Full Join is to retain the nonmatching information by including nonmatching rows in the results of a join, use a full outer join. It includes all rows from both tables, regardless of whether or not the other table has a matching value.

全外联接：-全外联接或全联接是通过在联接结果中包含不匹配的行来保留不匹配的信息，使用全外联接。它包括两个表中的所有行，无论另一个表是否具有匹配值。

Image Source

图片来源

MySQL 8.0 Reference Manual - Join Syntax

MySQL 8.0 参考手册 - 连接语法

Oracle Join operations

Oracle 联接操作

Answer 4

回答by Tushar Gupta - curioustushar

Inner Join

内部联接

Retrieve the matched rows only, that is, A intersect B.

仅检索匹配的行，即A intersect B.

Enter image description here

在此处输入图片说明

SELECT *
FROM dbo.Students S
INNER JOIN dbo.Advisors A
    ON S.Advisor_ID = A.Advisor_ID

Left Outer Join

左外连接

Select all records from the first table, and any records in the second table that match the joined keys.

选择第一个表中的所有记录，以及第二个表中与连接键匹配的所有记录。

Enter image description here

在此处输入图片说明

SELECT *
FROM dbo.Students S
LEFT JOIN dbo.Advisors A
    ON S.Advisor_ID = A.Advisor_ID

Full Outer Join

全外连接

Select all records from the second table, and any records in the first table that match the joined keys.

选择第二个表中的所有记录，以及第一个表中与连接键匹配的所有记录。

Enter image description here

在此处输入图片说明

SELECT *
FROM dbo.Students S
FULL JOIN dbo.Advisors A
    ON S.Advisor_ID = A.Advisor_ID

References

参考

Answer 5

回答by vidyadhar

In simple words:

简单来说：

An inner joinretrieve the matched rows only.

一个内部联接仅检索匹配的行。

Whereas an outer joinretrieve the matched rows from one table and all rows in other table ....the result depends on which one you are using:

而一个外连接检索一个表匹配的行和其他表中的所有行....结果取决于哪一个您正在使用：

Left: Matched rows in the right table and all rows in the left table
Right: Matched rows in the left table and all rows in the right table or
Full: All rows in all tables. It doesn't matter if there is a match or not

Left：匹配右表中的行和左表中的所有行
右：左表中的匹配行和右表中的所有行或
完整：所有表中的所有行。是否匹配并不重要

Answer 6

回答by 1800 INFORMATION

A inner join only shows rows if there is a matching record on the other (right) side of the join.

如果连接的另一侧（右侧）有匹配的记录，则内部连接仅显示行。

A (left) outer join shows rows for each record on the left hand side, even if there are no matching rows on the other (right) side of the join. If there is no matching row, the columns for the other (right) side would show NULLs.

（左）外连接在左侧显示每条记录的行，即使连接的另一侧（右侧）没有匹配的行。如果没有匹配的行，另一侧（右侧）的列将显示 NULL。

Answer 7

回答by Brian Boatright

Inner joins require that a record with a related ID exist in the joined table.

内部联接要求联接表中存在具有相关 ID 的记录。

Outer joins will return records for the left side even if nothing exists for the right side.

即使右侧不存在任何内容，外连接也会返回左侧的记录。

For instance, you have an Orders and an OrderDetails table. They are related by an "OrderID".

例如，您有一个 Orders 和一个 OrderDetails 表。它们通过“OrderID”相关联。

Orders

订单

OrderID
CustomerName

订单号
顾客姓名

OrderDetails

订单详细信息

OrderDetailID
OrderID
ProductName
Qty
Price

订单详情ID
订单号
产品名称
数量
价钱

The request

请求

SELECT Orders.OrderID, Orders.CustomerName
  FROM Orders 
 INNER JOIN OrderDetails
    ON Orders.OrderID = OrderDetails.OrderID

will only return Orders that also have something in the OrderDetails table.

只会返回在 OrderDetails 表中也有内容的订单。

If you change it to OUTER LEFT JOIN

如果将其更改为 OUTER LEFT JOIN

SELECT Orders.OrderID, Orders.CustomerName
  FROM Orders 
  LEFT JOIN OrderDetails
    ON Orders.OrderID = OrderDetails.OrderID

then it will return records from the Orders table even if they have no OrderDetails records.

那么它会从 Orders 表中返回记录，即使它们没有 OrderDetails 记录。

You can use this to find Orders that do not have any OrderDetails indicating a possible orphaned order by adding a where clause like WHERE OrderDetails.OrderID IS NULL.

您可以使用它来查找没有任何 OrderDetails 的订单，通过添加类似的 where 子句来指示可能的孤立订单WHERE OrderDetails.OrderID IS NULL。

Answer 8

回答by Pratik

In simple words :

简单来说：

Inner join-> Take ONLY common records from parent and child tables WHERE primary key of Parent table matches Foreign key in Child table.

内连接-> 仅从父表和子表中获取公共记录，其中父表的主键与子表中的外键匹配。

Left join->

左连接->

pseudo code

伪代码

1.Take All records from left Table
2.for(each record in right table,) {
    if(Records from left & right table matching on primary & foreign key){
       use their values as it is as result of join at the right side for 2nd table.
    } else {
       put value NULL values in that particular record as result of join at the right side for 2nd table.
    }
  }

Right join: Exactly opposite of left join . Put name of table in LEFT JOIN at right side in Right join , you get same output as LEFT JOIN.

右连接：与左连接完全相反。将表名放在 LEFT JOIN 右侧的 Right join 中，您将获得与 LEFT JOIN 相同的输出。

Outer join: Show all records in Both tables No matter what. If records in Left table are not matching to right table based on Primary , Forieign key , use NULL value as result of join .

外连接：显示两个表中的所有记录No matter what。如果左表中的记录与基于主键、外键的右表不匹配，则使用 NULL 值作为连接的结果。

Example :

例子：

Example

Lets assume now for 2 tables

现在让我们假设有 2 个表

1.employees , 2.phone_numbers_employees

employees : id , name 

phone_numbers_employees : id , phone_num , emp_id

Here , employees table is Master table , phone_numbers_employees is child table(it contains emp_idas foreign key which connects employee.idso its child table.)

这里，employees 表是 Master 表，phone_numbers_employees 是子表（它包含emp_id作为连接employee.id它的子表的外键。）

Inner joins

内连接

Take the records of 2 tables ONLY IF Primary key of employees table(its id) matches Foreign key of Child table phone_numbers_employees(emp_id).

仅当员工表的主键（其 id）与子表 phone_numbers_employees(emp_id) 的外键匹配时，才取 2 个表的记录。

So query would be :

所以查询将是：

SELECT e.id , e.name , p.phone_num FROM employees AS e INNER JOIN phone_numbers_employees AS p ON e.id = p.emp_id;

Here take only matching rows on primary key = foreign key as explained above.Here non matching rows on primary key = foreign key are skipped as result of join.

这里只取主键 = 外键上的匹配行，如上所述。这里主键 = 外键上的非匹配行作为连接的结果被跳过。

Left joins:

左连接：

Left join retains all rows of the left table, regardless of whether there is a row that matches on the right table.

左连接保留左表的所有行，而不管右表是否有匹配的行。

SELECT e.id , e.name , p.phone_num FROM employees AS e LEFT JOIN phone_numbers_employees AS p ON e.id = p.emp_id;

Outer joins:

外连接：

SELECT e.id , e.name , p.phone_num FROM employees AS e OUTER JOIN phone_numbers_employees AS p ON e.id = p.emp_id;

Diagramatically it looks like :

从图表上看，它看起来像：

Diagram

Answer 9

回答by vijikumar

You use INNER JOINto return all rows from both tables where there is a match. i.e. In the resulting table all the rows and columns will have values.

您用于INNER JOIN返回两个表中存在匹配项的所有行。即在结果表中，所有行和列都有值。

In OUTER JOINthe resulting table may have empty columns. Outer join may be either LEFTor RIGHT.

在OUTER JOIN结果表中可能有空列。外连接可以是LEFT或RIGHT。

LEFT OUTER JOINreturns all the rows from the first table, even if there are no matches in the second table.

LEFT OUTER JOIN返回第一个表中的所有行，即使第二个表中没有匹配项。

RIGHT OUTER JOINreturns all the rows from the second table, even if there are no matches in the first table.

RIGHT OUTER JOIN返回第二个表中的所有行，即使第一个表中没有匹配项。

Answer 10

回答by Raghu K Nair

This is a good diagrammatic explanation for all kind of joins

这是对所有类型连接的很好的图解说明

source: http://ssiddique.info/understanding-sql-joins-in-easy-way.html

来源：http: //ssiddique.info/understanding-sql-joins-in-easy-way.html

SQL “内连接”和“外连接”有什么区别？

提问by Chris de Vries

回答by Mark Harrison

回答by Martin Smith

Explanation

解释

Some examples

一些例子

Animated Version

动画版

SELECT A.Colour, B.Colour FROM A INNER JOIN B ON A.Colour NOT IN ('Green','Blue')

SELECT A.Colour, B.Colour from A INNER JOIN B ON A.Colour NOT IN ('Green','Blue')

SELECT A.Colour, B.Colour FROM A LEFT OUTER JOIN B ON A.Colour = B.Colour

从 A.Colour = B.Colour 上的 A LEFT OUTER JOIN B 中选择 A.Colour, B.Colour

SELECT A.Colour, B.Colour FROM A LEFT OUTER JOIN B ON A.Colour = B.Colour WHERE B.Colour IS NULL

SELECT A.Colour, B.Colour FROM A LEFT OUTER JOIN B ON A.Colour = B.Colour WHERE B.Colour 为 NULL

SELECT A.Colour, B.Colour FROM A RIGHT OUTER JOIN B ON A.Colour = B.Colour

SELECT A.Colour, B.Colour from A RIGHT OUTER JOIN B on A.Colour = B.Colour

SELECT A.Colour, B.Colour FROM A FULL OUTER JOIN B ON A.Colour = B.Colour

从 A.Colour = B.Colour 上的完全外部连接 B 中选择 A.Colour、B.Colour

SELECT A.Colour, B.Colour FROM A FULL OUTER JOIN B ON 1 = 0

SELECT A.Colour, B.Colour from A FULL OUTER JOIN B ON 1 = 0

SELECT COALESCE(A.Colour, B.Colour) AS Colour FROM A FULL OUTER JOIN B ON 1 = 0

SELECT COALESCE(A.Colour, B.Colour) 作为颜色从一个完整的外部连接 B ON 1 = 0

SELECT A.Colour, B.Colour FROM A LEFT OUTER JOIN B ON A.Colour = B.Colour WHERE B.Colour = 'Green'

在 A.Colour = B.Colour WHERE B.Colour = 'Green' 上从 A LEFT OUTER JOIN B 中选择 A.Colour, B.Colour

SELECT A.Colour, B.Colour FROM A LEFT OUTER JOIN B ON A.Colour = B.Colour AND B.Colour = 'Green'

从 A.Colour = B.Colour AND B.Colour = 'Green' 上的 A LEFT OUTER JOIN B 中选择 A.Colour, B.Colour

SQL Fiddle

SQL小提琴

回答by ajitksharma

回答by Tushar Gupta - curioustushar

Inner Join

内部联接

Left Outer Join

左外连接

Full Outer Join

全外连接

References

参考

回答by vidyadhar

回答by 1800 INFORMATION

回答by Brian Boatright

回答by Pratik

回答by vijikumar

回答by Raghu K Nair

相关推荐

SQL 中 EXISTS 和 IN 的区别？

SQL 使用查询将数据插入临时表

SQL 插入 ... 值（ SELECT ... FROM ... ）

SQL Server：将行转置为列

相关推荐

最近更新

标签