Trying to find all columns in one table not in another -- left join not working

Question

I'm trying to find all of the columns that exist in one table that don't exist in another by using a self-join on Information_schema on COLUMN_NAME = COLUMN_NAME and it's inner joining itself. I can't seem to figure out what's wrong with my logic:

select c.Column_name, c2.Column_name 
from information_schema.columns c
left outer join Information_Schema.Columns c2
ON c.COLUMN_NAME = c2.column_name
Where c.TABLE_NAME = 'Table1'
and c2.TABLE_NAME = 'Table2'

I should be getting

Col1(a) | Col1(b)
Col2(a) | null
Col3(a) | Col3(b)
Col4(a) | Col4(b)
Col5(a) | null

but instead I'm getting

Col1(a) | Col1(b)
Col3(a) | Col3(b)
Col4(a) | Col4(b)

Why is this?

Answer 1

Filters on the right table of a left join should be only inside the ON clause. When specifying them in there where clause, your left join automatically turns into an inner join.

select c.Column_name, c2.Column_name 
from information_schema.columns c
left outer join Information_Schema.Columns c2
ON c.COLUMN_NAME = c2.column_name AND c2.TABLE_NAME = 'Table2'
Where c.TABLE_NAME = 'Table1'