When we write a DML query that is using JOINed tables as data source, first we need to understand the way SQL Server is manipulating the data.

The clauses in the DML query statement are ordered as follows:

1. SELECT
2. FROM… JOIN
3. WHERE
4. GROUP BY
5. HAVING
6. ORDER BY

Behind the scene SQL Server is executing the DML query statement in different order:

• Step 1: FROM… JOIN – build the virtual recordset (VR) from one or multiple data sources (DS)
• Step 2: WHERE – filter the VR
• Step 3: GROUP BY… HAVING – group, aggregate and filter the grouped VR
• Step 4: SELECT – build the resulting VR and create aliases
• Step 5: ORDER BY – order the resulting VR and use the aliases, created in the SELECT statement

These two tables will help your intuition to draw the full picture how the tables are JOINed in Step 1:

As explained in Step 1, SQL Server creates VR, by JOINing the data sources. We JOIN the tables on column Col1. The resulting VR is:

Let’s write a simple query:

SELECT
	L.*
	, R.*
FROM
	LeftDataSource AS L
	JOIN RightDataSource AS R
		ON L.Col1 = R.Col1;
GO

The different types of JOIN, create different VR:

We’ll modify the query above, by changing the JOIN type to demonstrate the difference between the different types of JOIN.

JOIN (INNER JOIN)

INNER JOIN is inner, because it creates a VR that contains only the rows that meet the join criteria(s) – the rows that match on the linked columns (Col1).

This query:

SELECT
	L.*
	, R.*
FROM
	LeftDataSource AS L
	JOIN RightDataSource AS R
ON L.Col1 = R.Col1;
GO

will create VR that contains only the rows where the value in Col1 matches:

LEFT JOIN (LEFT OUTER JOIN)

OUTER JOIN is outer, because it creates VR, that contains more rows than the rows that meet the JOIN condition(s) in the ON clause.

By using the tables above, this query:

SELECT
	L.*
	, R.*
FROM
	LeftDataSource AS L
	LEFT JOIN RightDataSource AS R
		ON L.Col1 = R.Col1;
GO

The VR fills the rows in the right table that don’t have a match on the JOIN condition(s) with NULL.

RIGHT JOIN (RIGHT OUTER JOIN)

The same logic as LEFT JOIN, but reversed – the VR contains all the rows from the right data source and the matching rows from the left data source.

SELECT
	L.*
	, R.*
FROM
	LeftDataSource AS L
	RIGHT JOIN RightDataSource AS R
		ON L.Col1 = R.Col1;
GO

FULL JOIN (FULL OUTER JOIN)

Creates a VR with all the rows from both data sources. The not matching values in the left and the right source are represented with NULL. It is a combined LEFT JOIN and RIGHT JOIN.

SELECT
	L.*
	, R.*
FROM
	LeftDataSource AS L
	FULL JOIN RightDataSource AS R
		ON L.Col1 = R.Col1;
GO

LEFT (RIGHT) JOIN… WHERE

Let’s combine (LEFT, RIGHT) JOIN and WHERE clauses to select the rows from the left data source, that don’t have a match in the right data source (LEFT JOIN WHERE RightRecordset IS NULL):

SELECT
	L.*
	, R.*
FROM
	LeftDataSource AS L
	LEFT JOIN RightDataSource AS R
		ON L.Col1 = R.Col1
WHERE R.Col1 IS NULL;
GO

The same logic can be used with RIGHT JOIN to select the rows from the right data source, that don’t have a match in the left data source (RIGHT JOIN WHERE LeftRecordset IS NULL):

SELECT
	L.*
	, R.*
FROM
	LeftDataSource AS L
	RIGHT JOIN RightDataSource AS R
		ON L.Col1 = R.Col1
WHERE L.Col1 IS NULL;
GO

We can also select the rows from both data sources, that don’t have a match (FULL JOIN WHERE LeftRecordset OR RightRecordset IS NULL). This logic is FULL JOIN except INNER JOIN.

SELECT
	L.*
	, R.*
FROM
	LeftDataSource AS L
	FULL JOIN RightDataSource AS R
		ON L.Col1 = R.Col1
WHERE
	L.Col1 IS NULL
	OR R.Col1 IS NULL;
GO

CROSS JOIN

Creates a VR, containing all the combinations of the rows in both data sources, (a.k.a. Cartesian Product).

SELECT
	L.*
	, R.*
FROM
	LeftDataSource AS L
	CROSS JOIN RightDataSource AS R;
GO

The above lesson and many more are collected in my book Learn SQL Server Intuitively. Transact-SQL: The Solid Basics. You can find it here.

Keep it simple :-)