SQL Server 中左连接和右连接的区别

Question

提问by Pankaj Agarwal

I know about joins in SQL Server.

我知道 SQL Server 中的连接。

For example. There are two tables Table1, Table2.

例如。有两个表Table1，Table2。

Their table structures are the following.

它们的表结构如下。

create table Table1 (id int, Name varchar (10))

create table Table2 (id int, Name varchar (10))

Table1 data as follows:

表1数据如下：

    Id     Name     
    -------------
    1      A        
    2      B

Table2 data as follows:

表2数据如下：

    Id     Name     
    -------------
    1      A        
    2      B 
    3      C

If I execute both below mentioned SQL statements, both outputs will be the same

如果我执行下面提到的两个 SQL 语句，两个输出将相同

select *
from Table1
  left join Table2 on Table1.id = Table2.id

select *
from Table2
  right join Table1 on Table1.id = Table2.id

Please explain the difference between left and right join in the above SQL statements.

请解释一下上述SQL语句中左连接和右连接的区别。

Answer 1

采纳答案by Péter T?r?k

Select * from Table1 left join Table2 ...

and

和

Select * from Table2 right join Table1 ...

are indeed completely interchangeable. Try however Table2 left join Table1(or its identical pair, Table1 right join Table2) to see a difference. This query should give you more rows, since Table2 contains a row with an id which is not present in Table1.

确实是完全可以互换的。但是，请尝试Table2 left join Table1（或其相同的一对，Table1 right join Table2）以查看差异。这个查询应该给你更多的行，因为 Table2 包含一个 id 在 Table1 中不存在的行。

Answer 2

回答by Daan Timmer

Codeproject has this image which explains the simple basics of SQL joins, taken from: http://www.codeproject.com/KB/database/Visual_SQL_Joins.aspx SQL joins explained

Codeproject 有这张图片解释了 SQL 连接的简单基础，取自：http: //www.codeproject.com/KB/database/Visual_SQL_Joins.aspx SQL 连接解释

Answer 3

回答by vbole

Table from which you are taking data is 'LEFT'.
Table you are joining is 'RIGHT'.
LEFT JOIN: Take all items from left table AND (only) matching items from right table.
RIGHT JOIN: Take all items from right table AND (only) matching items from left table.
So:

您从中获取数据的表是“左”。
您要加入的表是“正确的”。
LEFT JOIN：从左表中取出所有项目和（仅）从右表中匹配的项目。
RIGHT JOIN：从右表中取出所有项目和（仅）从左表中匹配的项目。
所以：

Select * from Table1 left join Table2 on Table1.id = Table2.id

gives:

给出：

Id     Name       
-------------  
1      A          
2      B

but:

但：

Select * from Table1 right join Table2 on Table1.id = Table2.id

gives:

给出：

Id     Name       
-------------  
1      A          
2      B   
3      C

you were right joining table with less rows on table with more rows
AND
again, left joining table with less rows on table with more rows
Try:

你是右连接表，表上的行较少，行数较多
，
再次，左连接表，表上的行较少，行数较多
尝试：

 If Table1.Rows.Count > Table2.Rows.Count Then  
    ' Left Join  
 Else  
    ' Right Join  
 End If

Answer 4

回答by Sushank Pokharel

(INNER) JOIN:Returns records that have matching values in both tables.

(INNER) JOIN：返回在两个表中具有匹配值的记录。

LEFT (OUTER) JOIN:Return all records from the left table, and the matched records from the right table.

LEFT (OUTER) JOIN：返回左表中的所有记录，以及右表中匹配的记录。

RIGHT (OUTER) JOIN:Return all records from the right table, and the matched records from the left table.

RIGHT (OUTER) JOIN：返回右表中的所有记录，以及左表中匹配的记录。

FULL (OUTER) JOIN:Return all records when there is a match in either left or right table

FULL (OUTER) JOIN：左表或右表匹配时返回所有记录

For example, lets suppose we have two table with following records:

例如，假设我们有两个包含以下记录的表：

Table A

表A

id   firstname   lastname
___________________________
1     Ram         Thapa
2     sam         Ktheitroadala
3     abc         xyz
6    sruthy       abc

Table B

表B

id2   place
_____________
1      Nepal
2      USA
3      Lumbini
5      Kathmandu

Inner Join

内部联接

Note: It give the intersection of two table.

注意：它给出了两个表的交集。

Syntax

句法

SELECT column_name FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;

Apply it in your sample table:

将其应用到您的示例表中：

SELECT TableA.firstName,TableA.lastName,TableB.Place FROM TableA INNER JOIN TableB ON TableA.id = TableB.id2;

Result will be:

结果将是：

firstName       lastName       Place
_____________________________________
  Ram         Thapa             Nepal
  sam         Ktheitroadala            USA
  abc         xyz              Lumbini

Left Join

左加入

Note : will give all selected rows in TableA, plus any common selected rows in TableB.

注意：将给出 TableA 中的所有选定行，以及 TableB 中任何常见的选定行。

SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;

Apply it in your sample table

将其应用到您的示例表中

SELECT TableA.firstName,TableA.lastName,TableB.Place FROM TableA LEFT JOIN TableB ON TableA.id = TableB.id2;

Result will be:

结果将是：

firstName   lastName    Place
______________________________
 Ram         Thapa      Nepal
 sam         Ktheitroadala    USA
 abc         xyz        Lumbini
sruthy       abc        Null

Right Join

右加入

Note:will give all selected rows in TableB, plus any common selected rows in TableA.

注意：将给出 TableB 中的所有选定行，以及 TableA 中任何常见的选定行。

Syntax:

句法：

SELECT column_name(s) FROM table1 RIGHT JOIN table2 ON table1.column_name = table2.column_name;

Apply it in your samole table:

将其应用到您的 samole 表中：

SELECT TableA.firstName,TableA.lastName,TableB.Place FROM TableA RIGHT JOIN TableB ON TableA.id = TableB.id2;

Result will bw:

结果将是：

firstName   lastName     Place
______________________________
Ram         Thapa         Nepal
sam         Ktheitroadala       USA
abc         xyz           Lumbini
Null        Null          Kathmandu

Full Join

完全加入

Note : It is same as union operation, it will return all selected values from both tables.

注意：它与联合操作相同，它将从两个表中返回所有选定的值。

Syntax:

句法：

SELECT column_name(s) FROM table1 FULL OUTER JOIN table2 ON table1.column_name = table2.column_name;

Apply it in your samp[le table:

将其应用到您的示例 [le 表：

SELECT TableA.firstName,TableA.lastName,TableB.Place FROM TableA FULL JOIN TableB ON TableA.id = TableB.id2;

Result will be:

结果将是：

firstName   lastName    Place
______________________________
 Ram         Thapa      Nepal
 sam         Ktheitroadala    USA
 abc         xyz        Lumbini
sruthy       abc        Null
 Null         Null      Kathmandu

Some facts

一些事实

For INNER joins the order doesn't matter

对于 INNER 加入，顺序无关紧要

For (LEFT, RIGHT or FULL) OUTER joins,the order matter

对于（LEFT、RIGHT 或 FULL）外部连接，顺序很重要

Find More at w3schools

在w3schools找到更多

Answer 5

回答by Moraes

select fields 
from tableA --left
left join tableB --right
on tableA.key = tableB.key

The table in the fromin this example tableA, is on the left side of relation.

在该表from在这个例子中tableA，是对关系的左侧。

tableA <- tableB
[left]------[right]

So if you want to take all rows from the left table (tableA), even if there are no matches in the right table (tableB), you'll use the "left join".

因此，如果您想从左表 ( tableA) 中获取所有行，即使右表 ( tableB)中没有匹配项，您也将使用“左连接”。

And if you want to take all rows from the right table (tableB), even if there are no matches in the left table (tableA), you will use the right join.

如果您想从右表 ( tableB) 中获取所有行，即使左表 ( tableA)中没有匹配项，您也将使用right join.

Thus, the following query is equivalent to that used above.

因此，以下查询等效于上面使用的查询。

select fields
from tableB 
right join tableA on tableB.key = tableA.key

Answer 6

回答by onedaywhen

You seem to be asking, "If I can rewrite a RIGHT OUTER JOINusing LEFT OUTER JOINsyntax then why have a RIGHT OUTER JOINsyntax at all?" I think the answer to this question is, because the designers of the language didn't want to place such a restriction on users (and I think they would have been criticized if they did), which would force users to change the order of tables in the FROMclause in some circumstances when merely changing the join type.

您似乎在问，“如果我可以重写RIGHT OUTER JOINusingLEFT OUTER JOIN语法，那为什么还要有RIGHT OUTER JOIN语法呢？” 我认为这个问题的答案是，因为语言的设计者不想对用户施加这样的限制（我认为如果他们这样做了他们会受到批评），这会迫使用户改变表格的顺序在FROM某些情况下在子句中仅仅改变连接类型时。

Answer 7

回答by JNK

Your two statements are equivalent.

你的两个陈述是等价的。

Most people only use LEFT JOINsince it seems more intuitive, and it's universal syntax - I don't think all RDBMS support RIGHT JOIN.

大多数人只使用LEFT JOIN它，因为它看起来更直观，而且它是通用语法 - 我认为并非所有 RDBMS 都支持RIGHT JOIN.

Answer 8

回答by vinsinraw

I feel we may require ANDcondition in whereclause of last figure of Outer Excluding JOINso that we get the desired result of A Union B Minus A Interaction B. I feel query needs to be updated to

我觉得我们可能需要AND在where最后一个数字的子句中使用条件，Outer Excluding JOIN以便我们得到所需的结果A Union B Minus A Interaction B。我觉得查询需要更新为

SELECT <select_list>
FROM Table_A A
FULL OUTER JOIN Table_B B
ON A.Key = B.Key
WHERE A.Key IS NULL AND B.Key IS NULL

If we use OR, then we will get all the results of A Union B

如果我们使用OR，那么我们将得到所有的结果A Union B

Answer 9

回答by Nisarg Shah

select * from Table1 left join Table2 on Table1.id = Table2.id

In the first query Left joincompares left-sidedtable table1to right-sidedtable table2.

在第一个查询中，Left join将左侧表table1与右侧表table2 进行比较。

In Which all the properties of table1will be shown, whereas in table2only those properties will be shown in which condition get true.

其中将显示table1 的所有属性，而在table2 中仅显示那些条件为真的属性。

select * from Table2 right join Table1 on Table1.id = Table2.id

In the first query Right joincompares right-sidedtable table1to left-sidedtable table2.

在第一个查询中，右连接将右侧表table1与左侧表table2 进行比较。

In Which all the properties of table1will be shown, whereas in table2only those properties will be shown in which condition get true.

其中将显示table1 的所有属性，而在table2 中仅显示那些条件为真的属性。

Both queries will give the same result because the order of table declaration in query are differentlike you are declaring table1and table2in left and rightrespectively in first left joinquery, and also declaring table1and table2in right and leftrespectively in second right joinquery.

这两个查询会产生相同的结果，因为在查询表声明的顺序是不同的像你声明表1和表2中的左，右分别在第一左连接的查询，同时也宣告表1和表2中的左，右分别在第二右连接询问。

This is the reason why you are getting the same result in both queries. So if you want different result then execute this two queries respectively,

这就是您在两个查询中获得相同结果的原因。因此，如果您想要不同的结果，则分别执行这两个查询，

select * from Table1 left join Table2 on Table1.id = Table2.id

select * from Table1 right join Table2 on Table1.id = Table2.id

Answer 10

回答by Nisarg Shah

Select * from Table1 t1 Left Join Table2 t2 on t1.id=t2.idBy definition: Left Join selects all columns mentioned with the "select" keyword from Table 1 and the columns from Table 2 which matches the criteria after the "on" keyword.

Select * from Table1 t1 Left Join Table2 t2 on t1.id=t2.id根据定义：Left Join 从表 1 中选择所有用“select”关键字提及的列和表 2 中与“on”关键字后面的条件匹配的列。

Similarly,By definition: Right Join selects all columns mentioned with the "select" keyword from Table 2 and the columns from Table 1 which matches the criteria after the "on" keyword.

类似地，根据定义：Right Join 从表 2 中选择所有用“select”关键字提及的列和表 1 中与“on”关键字后面的条件匹配的列。

Referring to your question, id's in both the tables are compared with all the columns needed to be thrown in the output. So, ids 1 and 2 are common in the both the tables and as a result in the result you will have four columns with idand namecolumns from firstand secondtables in order.

参考您的问题，将两个表中的 id 与需要在输出中抛出的所有列进行比较。因此，id 1 和 2 在这两个表中是通用的，因此在结果中，您将有四列，其中id和name列依次来自第一个和第二个表。

*select * from Table1 left join Table2 on Table1.id = Table2.id

The above expression,it takes all the records (rows) from table 1 and columns, with matching id'sfrom table 1 and table 2, from table 2.

上面的表达式，它从表 1 中获取所有记录（行）和来自表 2 的表 1 和表 2 中匹配id 的列。

select * from Table2 right join Table1 on Table1.id = Table2.id**

Similarly from the above expression,it takes all the records (rows) from table 1 and columns, with matching id'sfrom table 1 and table 2, from table 2. (remember, this is a right join so all the columns from table2 and not from table1 will be considered).

与上面的表达式类似，它从表 1 和列中获取所有记录（行），并从表 1 和表 2 中获取匹配的id。将考虑来自表 1）。

SQL Server 中左连接和右连接的区别

提问by Pankaj Agarwal

采纳答案by Péter T?r?k

回答by Daan Timmer

回答by vbole

回答by Sushank Pokharel

回答by Moraes

回答by onedaywhen

回答by JNK

回答by vinsinraw

回答by Nisarg Shah

回答by Nisarg Shah

相关推荐

最近更新

标签

SQL Server 中左连接和右连接的区别

提问by Pankaj Agarwal

采纳答案by Péter T?r?k

回答by Daan Timmer

回答by vbole

回答by Sushank Pokharel

回答by Moraes

回答by onedaywhen

回答by JNK

回答by vinsinraw

回答by Nisarg Shah

回答by Nisarg Shah

相关推荐

SQL 如何检查字符串是否是唯一标识符？

SQL 只返回具有空字段的行？

SQL 甲骨文：收到 ORA-06550 和 PLS-00905

SQL Server - 在 Where 和 Select 中使用 Exists 子句

相关推荐

最近更新

标签