SQL 如何从右表中找不到的左表返回行？

How to return rows from left table not found in right table?

提问by CloudJedi

I have two tables with similar column names and I need to return records from the left table which are not found in the right table? I have a primary key(column) which will help me to compare both tables. Which join is preferred?

我有两个列名相似的表，我需要从左表中返回右表中没有的记录？我有一个主键（列）可以帮助我比较两个表。哪个加入是首选？

采纳答案by Deepshikha

If you are asking for T-SQL then lets look at fundamentals first. There are three types of joins here each with its own set of logical processing phases as:

如果您要求使用 T-SQL，那么让我们先看看基础知识。这里有三种类型的连接，每种类型都有自己的一组逻辑处理阶段，如下所示：

1. A cross joinis simplest of all. It implements only one logical query processing phase, a Cartesian Product. This phase operates on the two tables provided as inputs to the join and produces a Cartesian product of the two. That is, each row from one input is matched with all rows from the other. So if you have m rows in one table and n rows in the other, you get m×n rows in the result.
2. Then are Inner joins: They apply two logical query processing phases: A Cartesian productbetween the two input tables as in a cross join, and then it filtersrows based on a predicate that you specify in ONclause (also known as Join condition).

3. Next comes the third type of joins, Outer Joins:

   In an outer join, you mark a table as a preservedtable by using the keywords LEFT OUTER JOIN, RIGHT OUTER JOIN, or FULL OUTER JOINbetween the table names. The OUTERkeyword is optional. The LEFTkeyword means that the rows of the left tableare preserved; the RIGHTkeyword means that the rows in the right tableare preserved; and the FULLkeyword means that the rows in boththe leftand righttables are preserved.

   The third logical query processing phase of an outer joinidentifies the rows from the preserved table that did not find matches in the other table based on the ONpredicate. This phase adds those rows to the result table produced by the first two phases of the join, and uses NULLmarks as placeholders for the attributes from the nonpreserved side of the join in those outer rows.

1. Across join是最简单的。它仅实现一个逻辑查询处理阶段，即Cartesian Product. 此阶段对作为连接输入提供的两个表进行操作，并生成两者的笛卡尔积。也就是说，一个输入中的每一行都与另一个输入中的所有行相匹配。因此，如果您在一个表中有 m 行，而在另一个表中有 n 行，则结果中将得到 m×n 行。
2. 然后是Inner joins：它们应用两个逻辑查询处理阶段：A Cartesian product在两个输入表之间，如交叉连接，然后它filters根据您在ON子句（也称为Join condition）中指定的谓词行。

3. 接下来是第三种类型的连接，Outer Joins：

   在outer join你标记表作为preserved使用关键字表格LEFT OUTER JOIN，RIGHT OUTER JOIN或FULL OUTER JOIN之间的表名。该OUTER关键字optional。的LEFT关键字意味着的行left table被保留; 的RIGHT关键字的装置，在各行right table被保留; 和FULL关键字的意思是，行both的left，并right表将被保留。

   an 的第三个逻辑查询处理阶段outer join根据ON谓词识别未在另一个表中找到匹配项的保留表中的行。此阶段将这些行添加到连接的前两个阶段生成的结果表中，并使用NULL标记作为来自这些外部行中连接的非保留侧的属性的占位符。

Now if we look at the question: To return records from the left table which are not found in the right table use Left outer joinand filter out the rows with NULLvalues for the attributes from the right side of the join.

现在，如果我们看一下这个问题：要从左表中返回在右表中找不到的记录，请使用Left outer join并过滤掉具有NULL连接右侧属性值的行。

回答by Shamseer K

Try This

尝试这个

SELECT f.*
FROM first_table f LEFT JOIN second_table s ON f.key=s.key
WHERE s.key is NULL

For more please read this article : Joins in Sql Server

有关更多信息，请阅读这篇文章：在 Sql Server 中加入

回答by viejoEngineer

I also like to use NOT EXISTS. When it comes to performance if index correctly it should perform the same as a LEFT JOIN or better. Plus its easier to read.

我也喜欢使用 NOT EXISTS。当涉及到性能时，如果索引正确，它的性能应该与 LEFT JOIN 相同或更好。加上它更容易阅读。

SELECT Column1
FROM TableA a
WHERE NOT EXISTS ( SELECT Column1
                   FROM Tableb b
                   WHERE a.Column1 = b.Column1
                 )

回答by AHiggins

I can't add anything but a code example to the other two answers: however, I find it can be useful to see it in action (the other answers, in my opinion, are better because they explain it).

除了代码示例之外，我无法向其他两个答案添加任何内容：但是，我发现查看它的实际效果会很有用（在我看来，其他答案更好，因为它们对其进行了解释）。

DECLARE @testLeft TABLE (ID INT, SomeValue VARCHAR(1))
DECLARE @testRight TABLE (ID INT, SomeOtherValue VARCHAR(1))

INSERT INTO @testLeft (ID, SomeValue) VALUES (1, 'A')
INSERT INTO @testLeft (ID, SomeValue) VALUES (2, 'B')
INSERT INTO @testLeft (ID, SomeValue) VALUES (3, 'C')


INSERT INTO @testRight (ID, SomeOtherValue) VALUES (1, 'X')
INSERT INTO @testRight (ID, SomeOtherValue) VALUES (3, 'Z')

SELECT l.*
FROM 
    @testLeft l
     LEFT JOIN 
    @testRight r ON 
        l.ID = r.ID
WHERE r.ID IS NULL 

回答by Kritner

This page gives a decent breakdown of the different join types, as well as venn diagram visualizations to help... well... visualize the difference in the joins.

此页面提供了不同联接类型的详细分类，以及维恩图可视化，以帮助……嗯……可视化联接中的差异。

As the comments said this is a quite basic query from the sounds of it, so you should try to understand the differences between the joins and what they actually mean.

正如评论所说，这是一个非常基本的查询，因此您应该尝试了解连接之间的差异及其实际含义。

Check out http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/

查看http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/

You're looking for a query such as:

您正在寻找一个查询，例如：

DECLARE @table1 TABLE (test int)
DECLARE @table2 TABLE (test int)

INSERT INTO @table1
(
    test
)
SELECT 1
UNION ALL SELECT 2

INSERT INTO @table2
(
    test
)
SELECT 1
UNION ALL SELECT 3

-- Here's the important part
SELECT  a.*
FROM    @table1 a
LEFT    join @table2 b on a.test = b.test -- this will return all rows from a
WHERE   b.test IS null -- this then excludes that which exist in both a and b

-- Returned results:

2

回答by George Let

select * from left table where key field not in (select key field from right table)

select * from left table where key field not in (select key field from right table)

回答by Offir Pe'er

This is an example from real life work, I was asked to supply a list of users that bought from our site in the last 6 months but not in the last 3 months.

这是现实生活中的一个例子，我被要求提供过去 6 个月内从我们网站购买但不是最近 3 个月内购买的用户列表。

For me, the most understandable way I can think of is like so:

对我来说，我能想到的最容易理解的方式是这样的：

--Users that bought from us 6 months ago and between 3 months ago.
DECLARE @6To3MonthsUsers table (UserID int,OrderDate datetime)
INSERT @6To3MonthsUsers
    select u.ID,opd.OrderDate
        from OrdersPaid opd
        inner join Orders o
        on opd.OrderID = o.ID
        inner join Users u
        on o.BuyerID = u.ID
        where 1=1 
        and opd.OrderDate BETWEEN DATEADD(m,-6,GETDATE()) and DATEADD(m,-3,GETDATE())

--Users that bought from us in the last 3 months
DECLARE @Last3MonthsUsers table (UserID int,OrderDate datetime)
INSERT @Last3MonthsUsers
    select u.ID,opd.OrderDate
        from OrdersPaid opd
        inner join Orders o
        on opd.OrderID = o.ID
        inner join Users u
        on o.BuyerID = u.ID
        where 1=1 
        and opd.OrderDate BETWEEN DATEADD(m,-3,GETDATE()) and GETDATE()

Now, with these 2 tables in my hands I need to get only the users from the table @6To3MonthsUsersthat are not in @Last3MonthsUserstable.

现在，有了这 2 个表，我只需要从表@6To3MonthsUsers中获取不在@Last3MonthsUsers表中的用户。

There are 2 simple ways to achieve that:

有两种简单的方法可以实现：

1. Using Left Join:

   select distinct a.UserID
   from @6To3MonthsUsers a
   left join @Last3MonthsUsers b
   on a.UserID = b.UserID
   where b.UserID is null
   

2. Not in:

   select distinct a.UserID
   from @6To3MonthsUsers a
   where a.UserID not in (select b.UserID from @Last3MonthsUsers b)
   

1. 使用左连接：

   select distinct a.UserID
   from @6To3MonthsUsers a
   left join @Last3MonthsUsers b
   on a.UserID = b.UserID
   where b.UserID is null
   

2. 不在：

   select distinct a.UserID
   from @6To3MonthsUsers a
   where a.UserID not in (select b.UserID from @Last3MonthsUsers b)
   

Both ways will get me the same result, I personally prefer the second way because it's more readable.

两种方式都会得到相同的结果，我个人更喜欢第二种方式，因为它更具可读性。