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?
If you are asking for T-SQL then lets look at fundamentals first. There are three types of joins here each with it's own set of logical processing phases as:
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.
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
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
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
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.
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.