USE Northwind
GO
USE Northwind
GO

CREATE TABLE myTable99(Col1 int, Col2 varchar(10))
GO

INSERT INTO myTable99(Col1, Col2)
SELECT 1, 'Apple'  UNION ALL
SELECT 1, 'Banana' UNION ALL
SELECT 2, 'Cherry' UNION ALL
SELECT 3, 'Pear' UNION ALL
SELECT 2, 'Apple'
GO

    SELECT * FROM myTable99

    SELECT a.Col1 AS a_Col1, a.Col2 AS a_Col2, b.Col1 AS b_Col1, b.Col2 AS b_Col2 
      FROM myTable99 a LEFT JOIN myTable99 b ON a.Col1 = b.Col1
 

-- Produce the wrong results

    SELECT a.Col1 AS a_Col1, a.Col2 AS a_Col2, b.Col1 AS b_Col1, b.Col2 AS b_Col2 
      FROM myTable99 a 
 LEFT JOIN myTable99 b 
	ON a.Col1 = b.Col1
     WHERE a.Col2 = 'Apple' AND (b.Col2 = 'Banana' OR b.Col2 IS NULL)

    SELECT a.Col1 AS a_Col1, a.Col2 AS a_Col2, b.Col1 AS b_Col1, b.Col2 AS b_Col2 
      FROM myTable99 a 
 LEFT JOIN myTable99 b 
	ON a.Col1 = b.Col1
       AND a.Col2 = 'Apple' AND (b.Col2 = 'Banana' OR b.Col2 IS NULL)

    SELECT a.Col1 AS a_Col1, a.Col2 AS a_Col2, b.Col1 AS b_Col1, b.Col2 AS b_Col2 
      FROM myTable99 a 
 LEFT JOIN myTable99 b 
	ON a.Col1 = b.Col1
       AND a.Col2 = 'Apple' WHERE (b.Col2 = 'Banana' OR b.Col2 IS NULL)
       AND a.Col2 <> 'Banana'
GO

-- But this produces the correct results

    SELECT a.Col1 AS a_Col1, a.Col2 AS a_Col2, b.Col1 AS b_Col1, b.Col2 AS b_Col2 
      FROM (SELECT * FROM myTable99 WHERE Col2 = 'Apple')  AS a
 LEFT JOIN (SELECT * FROM myTable99 WHERE Col2 = 'Banana') AS b
	ON a.Col1 = b.Col1
GO
    SELECT * INTO myTable00 FROM myTable99 WHERE Col2 = 'Apple'
    SELECT * INTO myTable01 FROM myTable99 WHERE Col2 = 'Banana'
GO

    SELECT a.Col1 AS a_Col1, a.Col2 AS a_Col2, b.Col1 AS b_Col1, b.Col2 AS b_Col2 
      FROM myTable00 a 
 LEFT JOIN myTable01 b 
	ON a.Col1 = b.Col1
GO

DROP TABLE myTable00
DROP TABLE myTable01
DROP TABLE myTable99
GO

    SELECT a.Col1 AS a_Col1, a.Col2 AS a_Col2, b.Col1 AS b_Col1, b.Col2 AS b_Col2 
      FROM myTable99 a 
 LEFT JOIN myTable99 b 
	ON a.Col1 = b.Col1 
       AND b.Col2 = 'Banana'
     WHERE a.Col2 = 'Apple'