Execute the following T-SQL example scripts in SQL Server Management Studio Query Editor to demonstrate the application of the LEFT JOIN between two tables.

-- Simple left join - all left table rows included - QUICK SYNTAX

-- Missing right table rows have null values (translated to -1 by isnull)

USE AdventureWorks2008;

GO

SELECT p.Name                       AS ProductName,

      ISNULL(pr.ProductReviewID,-1) AS ReviewID

FROM Production.Product p

  LEFT JOIN Production.ProductReview pr

    ON p.ProductID = pr.ProductID

ORDER BY ProductName

/* ProductName          ReviewID

....

HL Mountain Handlebars  -1

HL Mountain Pedal       2

HL Mountain Pedal       3

HL Mountain Rear Wheel  -1  ....*/

------------

------------

-- LEFT JOIN predicate vs. WHERE clause predicate

-- They are not the same (they are the same for inner join)

------------

SELECT RowsCount=COUNT(*)

FROM Production.Product p

  LEFT JOIN Production.ProductReview pr

    ON p.ProductID = pr.ProductID

    AND Color = 'Red'

-- 504

SELECT RowsCount=COUNT(*)

FROM Production.Product p

  LEFT JOIN Production.ProductReview pr

    ON p.ProductID = pr.ProductID

WHERE Color = 'Red'

-- 38

------------

Related articles:

SQL SERVER – Better Performance – LEFT JOIN or NOT IN?

http://www.sqlusa.com/bestpractices2005/advancedsql/employeegeoreport/