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 its own set of logical processing phases as:
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.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
).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.