SQL Server JOIN basics

Yesterday I took an interview of an EXPERIENCED candidate. I was quite shocked to see the information he has on JOINs. Thus I thought about blogging about the basics of JOINs.
The scenario I gave him was -
CREATE TABLE #JOIN1
(
    ID INT,
    [TEXT] VARCHAR(100)
)
CREATE TABLE #JOIN2
(
    ID INT,
    [TEXT] VARCHAR(100)
)
INSERT INTO #JOIN1 VALUES(1,'TEXT JOIN 1 - 1')
INSERT INTO #JOIN1 VALUES(2,'TEXT JOIN 1 - 2')
INSERT INTO #JOIN1 VALUES(3,'TEXT JOIN 1 - 3')
INSERT INTO #JOIN1 VALUES(1,'TEXT JOIN 1 - 4')
INSERT INTO #JOIN1 VALUES(NULL,'TEXT JOIN 1 - 5')
INSERT INTO #JOIN2 VALUES(1,'TEXT JOIN 2 - 1')
INSERT INTO #JOIN2 VALUES(2,'TEXT JOIN 2 - 2')
INSERT INTO #JOIN2 VALUES(4,'TEXT JOIN 2 - 3')
INSERT INTO #JOIN2 VALUES(NULL,'TEXT JOIN 2 - 4')
INSERT INTO #JOIN2 VALUES(5,'TEXT JOIN 2 - 5')
Note: There is no primary key column.
Left Outer Join
MSDN defines a left outer join as - The Left Outer Join logical operator returns each row that satisfies the join of the first (top) input with the second (bottom) input. It also returns any rows from the first input that had no matching rows in the second input. The nonmatching rows in the second input are returned as null values. If no join predicate exists in the Argument column, each row is a matching row.
This is quite state forward for the guys with some experience but not that match for someone who is just trying his hands on JOINs.
Query -
SELECT * FROM #JOIN1 F
LEFT JOIN #JOIN2 S ON F.ID = S.ID

ID table #JOIN1	Text Table #JOIN1	ID table #JOIN1	Text Table #JOIN1
1	TEXT JOIN 1 - 1	1	TEXT JOIN 2 - 1
2	TEXT JOIN 1 - 2	2	TEXT JOIN 2 - 2
3	TEXT JOIN 1 - 3	NULL	NULL
1	TEXT JOIN 1 - 4	1	TEXT JOIN 2 - 1
NULL	TEXT JOIN 1 - 5	NULL	NULL

Id 1 is present in #JOIN1 and in #Join2 thus we have a matching record.
Id 2 is present in #JOIN1 and in #Join2 thus we have a matching record.
Id 3 is present in #JOIN1 and NOT in #Join2 thus we do not have a matching record. Thus we will get NULL for the row from #JOIN2 in the record set.
Id 1 is present in #JOIN1 and in #Join2 thus we have a matching record. (Duplicate ID)
Id NULL is present in #JOIN1 and in #Join2 BUT NULL is not equal to NULL and thus no matching record.
What you may I assume from above discussion
Correct Assumptions
If the join condition evaluates to be true then there will be a record from both tables.
All the records will come from LEFT table in the result set.
NULL is not equal to NULL and thus there can never be a match.
If the LEFT table has duplicates (ID – 1 in above case) they will be evaluated independently from each other. (That is why we had two separate records from in above set for ID – 1)
Incorrect Assumptions
The LEFT table will define the number of records in the result set. The truth is that it is the Joining condition which defines the number of records in the result set.
To prove this  insert one more record in #JOIN 2.
INSERT INTO #JOIN2 VALUES(2,'TEXT JOIN 2 - 6')

ID table #JOIN1	Text Table #JOIN1	ID table #JOIN1	Text Table #JOIN1
1	TEXT JOIN 1 - 1	1	TEXT JOIN 2 - 1
2	TEXT JOIN 1 - 2	2	TEXT JOIN 2 - 2
2	TEXT JOIN 1 - 2	2	TEXT JOIN 2 - 6
3	TEXT JOIN 1 - 3	NULL	NULL
1	TEXT JOIN 1 - 4	1	TEXT JOIN 2 - 1
NULL	TEXT JOIN 1 - 5	NULL	NULL

See the number of records and difference in text in #JOIN2 records in result set.
Right Outer Join
In case of RIGHT outer join the definition remains the same as the left outer join except that all the records from right table will be present in the result set.  NULL will be there in the result set for the records of left table where the join condition has failed.
SELECT * FROM #JOIN1 F
RIGHT JOIN #JOIN2 S ON F.ID = S.ID

ID table #JOIN1	Text Table #JOIN1	ID table #JOIN1	Text Table #JOIN1
1	TEXT JOIN 1 - 1	1	TEXT JOIN 2 - 1
1	TEXT JOIN 1 - 4	1	TEXT JOIN 2 - 1
2	TEXT JOIN 1 - 2	2	TEXT JOIN 2 - 2
NULL	NULL	4	TEXT JOIN 2 - 3
NULL	NULL	NULL	TEXT JOIN 2 - 4
NULL	NULL	5	TEXT JOIN 2 - 5
2	TEXT JOIN 1 - 2	2	TEXT JOIN 2 – 6

The explanation remains the same as in case of LEFT Outer Join and why not see the result set of this query -

ID table #JOIN1	Text Table #JOIN1	ID table #JOIN1	Text Table #JOIN1
1	TEXT JOIN 2 - 1	1	TEXT JOIN 1 - 1
1	TEXT JOIN 2 - 1	1	TEXT JOIN 1 - 4
2	TEXT JOIN 2 - 2	2	TEXT JOIN 1 - 2
4	TEXT JOIN 2 - 3	NULL	NULL
NULL	TEXT JOIN 2 - 4	NULL	NULL
5	TEXT JOIN 2 - 5	NULL	NULL
2	TEXT JOIN 2 - 6	2	TEXT JOIN 1 – 2

Are not the same .

INNER JOIN
Inner join is just like a where. It returns only those records for which join condition matches.
SELECT * FROM #JOIN1 F
INNER JOIN #JOIN2 S ON F.ID = S.ID

ID table #JOIN1	Text Table #JOIN1	ID table #JOIN1	Text Table #JOIN1
1	TEXT JOIN 1 - 1	1	TEXT JOIN 2 - 1
1	TEXT JOIN 1 - 4	1	TEXT JOIN 2 - 1
2	TEXT JOIN 1 - 2	2	TEXT JOIN 2 - 2
2	TEXT JOIN 1 - 2	2	TEXT JOIN 2 – 6

To make it clearer let us tweak the join condition a bit.

SELECT * FROM #JOIN1 F
INNER JOIN #JOIN2 S ON F.ID = S.ID AND F.ID IN (1,3)

ID table #JOIN1	Text Table #JOIN1	ID table #JOIN1	Text Table #JOIN1
1	TEXT JOIN 1 - 1	1	TEXT JOIN 2 - 1
1	TEXT JOIN 1 - 4	1	TEXT JOIN 2 – 1

Full Outer Join
Conceptually, a full outer join combines the effect of applying both left and right outer joins. Where records in the FULL OUTER JOINed tables do not match, the result set will have NULL values for every column of the table that lacks a matching row. For those records that do match, a single row will be produced in the result set (containing fields populated from both tables).
SELECT * FROM #JOIN1 F
FULL OUTER JOIN #JOIN2 S ON F.ID = S.ID

ID table #JOIN1	Text Table #JOIN1	ID table #JOIN1	Text Table #JOIN1
1	TEXT JOIN 1 - 1	1	TEXT JOIN 2 - 1
2	TEXT JOIN 1 - 2	2	TEXT JOIN 2 - 2
2	TEXT JOIN 1 - 2	2	TEXT JOIN 2 - 6
3	TEXT JOIN 1 - 3	NULL	NULL
1	TEXT JOIN 1 - 4	1	TEXT JOIN 2 - 1
NULL	TEXT JOIN 1 - 5	NULL	NULL
NULL	NULL	4	TEXT JOIN 2 - 3
NULL	NULL	NULL	TEXT JOIN 2 - 4
NULL	NULL	5	TEXT JOIN 2 – 5

In the next article of this series we will look into Cross Join, Self Join, Equi Join and usage in detail. We will also cover on the fly tables.

A book worth reading on SQL SERVER -