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

I know about joins in SQL Server.

我知道SQL Server中的join。

For example. there is two tables Table1, Table2.

为例。表1和表2。

There table structure are 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:

表二数据如下:

    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 above sql statements.

请在上面的sql语句中解释左连接和右连接的区别。

10 个解决方案

#1

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.

确实是完全可以互换。但是，尝试使用表2左连接表1(或其同对表1右连接表2)来查看差异。这个查询应该提供更多的行，因为表2包含一个id在表1中不存在的行。

#2

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

Codeproject的这张图片解释了SQL join的基本原理，图片来自http://www.codeproject.com/KB/database/Visual_SQL_Joins.aspx

#3

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:

您正在从中获取数据的表是“左”。你要加入的桌子是“正确的”。左连接:从左表获取所有项，(仅)从右表获取匹配项。右连接:从右表获取所有项，(仅)从左表获取匹配项。所以:

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  

#4

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

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

这个例子表格中的表格，在关系的左边。

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

因此，如果要从左表(表a)中获取所有行，即使右表(表b)中没有匹配项，也要使用“左连接”。

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)中没有匹配项，也要使用右连接。

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

因此，下面的查询相当于上面使用的查询。

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

#5

You seem to be asking, "If I can rewrite a RIGHT OUTER JOIN using LEFT OUTER JOIN syntax then why have a RIGHT OUTER JOIN syntax 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 FROM clause in some circumstances when merely changing the join type.

您似乎在问，“如果我可以使用左外连接语法重写右外连接，那么为什么还要使用右外连接语法呢?”我认为这个问题的答案是,因为语言的设计者不希望这样一个限制用户(我认为他们会被批评,如果他们所做的那样),这将迫使用户改变FROM子句中的表的顺序在某些情况下当仅仅改变连接类型。

#6

Your two statements are equivalent.

你的两个表述是等价的。

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

大多数人只使用左连接，因为它看起来更直观，而且它是通用的语法——我不认为所有RDBMS支持都是正确的。

#7

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

(内)连接:返回两个表中具有匹配值的记录。

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

左(外部)连接:返回来自左表的所有记录，以及来自右表的匹配记录。

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

右(外部)连接:返回来自右表的所有记录，以及来自左表的匹配记录。

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

完整(外部)连接:当左表或右表中有匹配时，返回所有记录

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

例如，假设我们有两个表，记录如下:

Table A

表一个

id   firstname   lastname
___________________________
1     Ram         Thapa
2     sam         Koirala
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         Koirala            USA
  abc         xyz              Lumbini

Left Join

左连接

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

注意:将在TableA中给出所有选择的行，以及表b中任何常见的选定行。

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         Koirala    USA
 abc         xyz        Lumbini
sruthy       abc        Null

Right Join

正确的连接

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

注意:将给出表b中所有选定的行，以及表a中所有常见的选定行。

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:

结果将bw:

firstName   lastName     Place
______________________________
Ram         Thapa         Nepal
sam         Koirala       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.

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

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 table:

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         Koirala    USA
 abc         xyz        Lumbini
sruthy       abc        Null
 Null         Null      Kathmandu

Some facts

一些事实

For INNER joins the order doesn't matter

对于内部连接，顺序无关紧要

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

对于(左、右或全)外连接，顺序很重要。

Find More at w3schools

找到更多的在w3schools

#8

I feel we may require AND condition in where clause of last figure of Outer Excluding JOIN so that we get the desired result of A Union B Minus A Interaction B. I feel query needs to be updated to

我觉得我们可能需要和条件在where子句中去掉了JOIN，这样我们就可以得到一个Union B减去一个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

如果我们使用或者，我们会得到所有的结果。

#9

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

从表1左侧的join表2中选择*。id = Table2.id

In the first query Left join compares left-sided table table1 to right-sided table table2.

在第一个查询中，左连接比较左表表表1和右表表表2。

In Which all the properties of table1 will be shown, whereas in table2 only those properties will be shown in which condition get true.

其中表1的所有属性都将被显示，而在表2中，只有这些属性将被显示，其中条件为true。

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

从表2右连接表1中选择*。id = Table2.id

In the first query Right join compares right-sided table table1 to left-sided table table2.

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

In Which all the properties of table1 will be shown, whereas in table2 only those properties will be shown in which condition get true.

其中表1的所有属性都将被显示，而在表2中，只有这些属性将被显示，其中条件为true。

Both queries will give the same result because the order of table declaration in query are different like you are declaring table1 and table2 in left and right respectively in first left join query, and also declaring table1 and table2 in right and left respectively in second right join query.

这两个查询都会得到相同的结果，因为查询中的表声明顺序不同，就像您在第一个左连接查询中分别声明表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

从表1左侧的join表2中选择*。id = Table2.id

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

从表1右连接表2中选择*。id = Table2.id

#10

Select * from Table1 t1 Left Join Table2 t2 on t1.id=t2.id By 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.

从表1 t1中选择*，在t1.id=t2上连接表2 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 id and name columns from first and second tables in order.

根据您的问题，两个表中的id与要在输出中抛出的所有列进行了比较。因此，id 1和id 2在这两个表中都是常见的，因此，您将有四个列，其中id和name列按顺序从第一个表和第二个表开始。

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

*从表1左侧的join表2中选择*。id = Table2.id

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

上面的表达式，它从表1和列获取所有记录(行)，从表2获取与表1和表2匹配的id。

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

从表2右连接表1中选择*。id = Table2.id * *

Similarly from the above expression,it takes all the records (rows) from table 1 and columns, with matching id's from 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匹配，从表2。(记住，这是一个右连接，因此将考虑表2中的所有列，而不是表1中的所有列)。