T-SQL: Joins Explained

I dare say that if you write T-SQL, you'll have had to write a JOIN in your time. Although much of this is quite basic, it doesn't hurt to be reminded and I hope that the examples are clear enough to illustrate the different types of JOINs.

USE tempdb
GO
-- setup tables
CREATE TABLE dbo.tblA (i1 INT, c1 CHAR(1), i2 INT)
GO
CREATE TABLE dbo.tblB (i1 INT, c1 CHAR(1), i2 INT)
GO

-- insert some dummy data
INSERT INTO dbo.tblA
SELECT 1, 'A', 6
UNION
SELECT 2, 'A', 5
UNION
SELECT 3, 'A', 4
UNION
SELECT 4, 'A', 3
UNION
SELECT 5, 'A', 2
UNION
SELECT 6, 'A', 1
GO

INSERT INTO dbo.tblB
SELECT 1, 'B', 6
UNION
SELECT 2, 'B', 3
UNION
SELECT 3, 'B', 4
UNION
SELECT 7, 'B', 1
GO

-- INNER JOIN (Just matches)
-- so total rows will be number of matched records
SELECT *
FROM dbo.tblA a
   INNER JOIN dbo.tblB b
       ON a.i1 = b.i1

-- LEFT JOIN (All records from left table, NULLs in the right table if no match)
-- so total rows will be number of records in the left table (tblA)
SELECT *
FROM dbo.tblA a
   LEFT OUTER JOIN dbo.tblB b
       ON a.i1 = b.i1

-- RIGHT JOIN (All records from the right table, NULLs in the left table if no match)
-- so total rows will be number of records in the right table (tblB)
SELECT *
FROM dbo.tblA a
   RIGHT OUTER JOIN dbo.tblB b
       ON a.i1 = b.i1

-- FULL JOIN (all records from both tables with matches or not)
-- so total rows will be number of rows in left table + number of rows in right table - number of exact matches)
SELECT *
FROM dbo.tblA a
   FULL OUTER JOIN dbo.tblB b
       ON a.i1 = b.i1

-- CROSS JOIN (cartesian product of both tables)
-- total rows of left table * right table
SELECT *
FROM dbo.tblA
   CROSS JOIN dbo.tblB

-- "de Facto" INNER JOINs (using an OUTER join with a WHERE clause)
SELECT *
FROM dbo.tblA a
   LEFT OUTER JOIN dbo.tblB b
       ON a.i1 = b.i1
WHERE b.i1 = 1

-- "de Facto" INNER JOINs (using an OUTER join with a WHERE clause)
-- here the outer join is negated by the fact that you have included
-- filtering criteria which removes the NULL values
SELECT *
FROM dbo.tblA a
   LEFT OUTER JOIN dbo.tblB b
       ON a.i1 = b.i1
WHERE a.i2 = b.i2

-- its called a de factor INNER JOIN as its the same as writing:
SELECT *
FROM dbo.tblA a
   INNER JOIN dbo.tblB b
       ON a.i1 = b.i1
           AND a.i2 = b.i2

-- the correct way or writing this query as a LEFT JOIN would be
SELECT *
FROM dbo.tblA a
   LEFT OUTER JOIN dbo.tblB b
       ON a.i1 = b.i1
           AND a.i2 = b.i2

-- tidy up
DROP TABLE dbo.tblA
DROP TABLE dbo.tblB


Some resources:

INNER JOIN - http://msdn.microsoft.com/en-us/library/ms190014.aspx
OUTER JOIN - http://msdn.microsoft.com/en-us/library/ms187518.aspx
CROSS JOIN - http://msdn.microsoft.com/en-us/library/ms190690.aspx