What is the point of outer in a join?

I have made a script which harvests mac addresses from switch ports and puts them in a database. I insert it into an import table to be copied over later to the real table. All fine. Now I am writing the query to find which mac addresses are new. So, I need the rows in the import table which are not in the destination table. A left outer join makes sense:

select *
from SwitchportMac_import i
left outer join SwitchportMac sm 
on sm.switch = i.switch and sm.port = i.port and sm.mac = i.mac

This returns also rows in i which match in sm. Sure, I can add a where sm.id is null but I am flabbergasted.

So either:

• I am an idiot and did something obviously wrong

• I am an idiot and have been explaining and understanding joins wrong for 20 years

If it's the first I will dump the DDL and figure it out but I'm scared it's the second one. Does the outer keyword exist just to confuse me?

LEFT JOIN and LEFT OUTER JOIN are synonymous so apologies but it sounds like option 2 :-)

The "Outer" join (which preserves unmatched rows) is as opposed to "Inner" (which doesn't).

There are three flavours of outer join. Left, Right, Full (dependent on which unmatched rows are to be preserved).

As SQL doesn't have direct join syntax for any other types of join (such as left/right anti semi join) there is no ambiguity in allowing the OUTER keyword be optional in the grammar.

The LEFT ANTI SEMI JOIN sounds like what you expected LEFT OUTER to do. You can implement that in a variety of ways. Usually NOT EXISTS is preferred but you can use LEFT OUTER JOIN in conjunction with IS NULL to keep only the non matched ones.

This is all confirmed in the "Join type" section of the documentation


LEFT [ OUTER ] Specifies that all rows from the left table not
meeting the join condition are included in the result set, and output
columns from the other table are set to NULL in addition to all rows
returned by the inner join.

The square brackets around the [OUTER] just mean that this is optional but nonetheless the same definition applies.

StackExchange Database Administrators Q#266662, answer score: 11