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?

Solution 1:

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

Solution 2:

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:

1. A cross join is 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 product between the two input tables as in a cross join, and then it filters rows based on a predicate that you specify in ON clause (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 preserved table by using the keywords LEFT OUTER JOIN, RIGHT OUTER JOIN, or FULL OUTER JOIN between the table names. The OUTER keyword is optional. The LEFT keyword means that the rows of the left table are preserved; the RIGHT keyword means that the rows in the right table are preserved; and the FULL keyword means that the rows in both the left and right tables are preserved.

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

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 join and filter out the rows with NULL values for the attributes from the right side of the join.

Solution 3:

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
                 )

Solution 4:

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