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 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.
Select * from TableA as A Join TableB as B on A.ID = B.ID
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
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
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