Left Outer Join Clause with Search Condition

Putting a search condition on an 'Outer Join' clause will yield a different result compared to when it is on a 'Where' clause.

For example, using 'Left Outer Join' on 'Categories' and 'Products' tables

SELECT * FROM Categories C
LEFT JOIN Products P ON C.CategoryID = P.CategoryID AND C.CategoryName ='Seafood'

will yield a different result compared to this query

SELECT * FROM Categories C
LEFT JOIN Products P ON C.CategoryID = P.CategoryID
WHERE C.CategoryName ='Seafood'

The first one will yield a table with rows on the 'Products' table's side that do not match the condition CategoryName ='Seafood' to have 'NULL' values, while the matched rows will have their information.

While on the second query, the filter is applied further after the outer joined's result is formed.

The same thing also applies to this query:

SELECT * FROM Categories C
LEFT JOIN Products P ON C.CategoryID = P.CategoryID AND P.ProductName ='Ikura'

This will return a table with only rows on the 'Products' table's side that match ProductName ='Ikura' to have values. Other rows on the 'Products' table's side will return 'NULL' values.

However, if we use 'Inner Join' clause like this:

SELECT * FROM Categories C
JOIN Products P ON C.CategoryID = P.CategoryID AND C.CategoryName ='Seafood'

it will return the same result if the search condition was on the 'Where' clause.