1. For Readability - Refrain from using search arguments in the ON clause, and use the WHERE clause instead
2. When Outer Join is Converted to Inner Join - Adding references to the table in the right side of a JOIN to the WHERE clause will convert the OUTER JOIN to an INNER JOIN. I'm trying out Example Code to reproduce the scenario. This Shows the behavior when Left Join in Query is Converted to Inner Join during Execution.
Step 1 - Create Tables
CREATE TABLE Table1(Col1 INT IDENTITY(1,1) PRIMARY KEY, Col2 VARCHAR(40), Col3 VARCHAR(50))
CREATE TABLE Table2(Col1 INT IDENTITY(1,1) PRIMARY KEY, Col2 VARCHAR(40), Col3 VARCHAR(50))
Step 2 - Populate Data
DECLARE @J INT
SET @J = 1WHILE 1 = 1
BEGIN
IF @J < 1000
INSERT INTO Table1(Col2, Col3)
VALUES((CONVERT(VARCHAR(20),@J)+'VALUE'), (CONVERT(VARCHAR(20),@J)+'VALUE'))
INSERT INTO Table2(Col2, Col3)
VALUES((CONVERT(VARCHAR(20),@J)+'VALUE'), (CONVERT(VARCHAR(20),@J)+'VALUE'))
SET @J = @J + 1
IF @J > 1000
BREAK;
END
Step 3 - Run Queries
SELECT
N1.Col1, N2.Col2FROM Table1 N1
LEFT JOIN Table2 N2
ON N1.Col1 = N2.Col1
WHERE N2.Col1 = 100
SELECT
N1.Col1, N2.Col2FROM Table1 N1
LEFT JOIN Table2 N2
ON N1.Col1 = N2.Col1
WHERE N1.Col1 = 120
Step 4 - Execution Plan
Happy Learning!!!
