Join Types

There are three different Join Types in SQL Server. Within the types of joins are different variations of the joins.

INNER JOIN
Only join records from two different tables where the join columns are equal. Restricts the result set to rows where a matching column exists in both tables.
[cc lang=”sql”]
SELECT a.*, b.*
FROM dbo.TableA a
INNER JOIN dbo.TableB b
ON a.ColumnID = b.ColumnID
[/cc]

OUTER JOIN
Retrieves additional records from one or both tables where the columns are not equal.
[cc lang=”sql”]
— Retrieve all records from TableA even where there is not a match in TableB
SELECT a.*, b.*
FROM dbo.TableA a
LEFT OUTER JOIN dbo.TableB b
ON a.ColumnID = b.ColumnID
[/cc]
[cc lang=”sql”]
— Retrieve all records from TableB AND TableA even where there is not a match
SELECT a.*, b.*
FROM dbo.TableA a
FULL OUTER JOIN dbo.TableB b
ON a.ColumnID = b.ColumnID
[/cc]
[cc lang=”sql”]
— Retrieve all records from TableB even where there is not a match in TableA
SELECT a.*, b.*
FROM dbo.TableA a
RIGHT OUTER JOIN dbo.TableB b
ON a.ColumnID = b.ColumnID
[/cc]

CROSS JOIN
Cross Join – join every combination of record in one table with every combination of record in another table.
[cc lang=”sql”][/cc]
— No Join column is necessary
SELECT a.*, b.*
FROM dbo.TableA a
CROSS JOIN dbo.TableB b
[cc lang=”sql”][/cc]