SQL: 4 Types of Joins

SQL Joins come in 4 major types. In T-SQL – Microsoft’s SQL language, unless otherwise specified, a Join defaults to Inner Join. The other three are Right Outer Join, Left Outer Join, and Full Outer Join.

Inner Join

Inner joins match up data that exists in both tables. In the example below, only A and C found in the result table since B,D don’t exist in TableB and E,F don’t exist in TableA.

Syntax

Select *
from TableA as A Join TableB as B
on A.ID = B.ID

innerjoin

Full Outer Join

A Full Outer Join puts all elements in both tables together. Where information is missing, Nulls will appear

Select *
from TableA as A Full Outer Join TableB as B
on A.ID = B.ID

fullouterjoin

Left Outer Join

Left Outer Join takes all data from the Left Table and joins up matching data from the Right table. Left and Right is determined based on the table’s position in the Where clause

Select *
from TableA as A Left Outer Join TableB as B
on A.ID = B.ID

leftouterjoin.jpg

Right Outer Join

Right Outer Join is the exact opposite of the Left Outer Join, with all elements from the Right Table being used and only matching elements from the Left Table.

Select *
from TableA as A Right Outer Join TableB as B
on A.ID = B.ID

rightouterjoin.jpg

2 thoughts on “SQL: 4 Types of Joins

Please Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s