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

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?

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.

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 @6To3MonthsUsers that are not in @Last3MonthsUsers table.

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)
   

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

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.

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

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

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

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 

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/

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