Default Join Type

I'm always ask what the default join in SQL Server is, when no join type is specified. My immediate reaction is Google it. The problem is trying to find it among all the article that will show up as search result.

Now that I found it, I decided to put it here...

Here is the actual article (MSDN BOL) where I found it. And just in case Microsoft decides to remove it, here's an excerpt:

INNER
Specifies that all matching pairs of rows are returned. Discards unmatched rows from both tables. This is the default if no join type is specified.

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

RIGHT [ OUTER ]
Specifies that all rows from the right table not meeting the specified condition are included in the result set in addition to all rows returned by the inner join. Output columns from the right table are set to NULL.

FULL [ OUTER ]
If a row from either the left or right table does not match the selection criteria, specifies the row be included in the result set, and output columns that correspond to the other table be set to NULL. This is in addition to all rows usually returned by the inner join.

~~ CK