MS SQL Basics: Joins

September 20, 2009 by Christoff Truter SQL  

There are three categories joins fall into, inner, outer and cross. Outer joins consist of three types, left, right and full. Lets have a quick look at the various joins. (On the right we've got two datasets we'll be using in the examples.)	Table a aID title 1 a1 2 a2 3 a3 4 a4 Table b bID title aID 1 b1 1 2 b2 2 3 b3 NULL 4 b4 NULL
Inner Join Return rows that match in both tables. (Default join) SELECT * FROM a INNER JOIN b ON a.aid = b.aid title aID a1 1 a2 2 aID bID title 1 1 b1 2 2 b2
Left Outer Join Return rows from the table on the left of the expression even if they dont match with the table on the right. SELECT * FROM a LEFT OUTER JOIN b ON a.aid = b.aid title aID a1 1 a2 2 a3 3 a4 4 aID bID title 1 1 b1 2 2 b2 NULL NULL NULL NULL NULL NULL
Right Outer Join Return rows from the table on the right of the expression even if they dont match with the table on the left. SELECT * FROM a RIGHT OUTER JOIN b ON a.aid = b.aid title aID a1 1 a2 2 NULL NULL NULL NULL aID bID title 1 1 b1 2 2 b2 3 3 b3 4 4 b4
Full Outer Join Return rows from both the left and right tables even if they don't match in either. (left + right join) SELECT * FROM a FULL OUTER JOIN b ON a.aid = b.aid title aID a1 1 a2 2 a3 3 a4 4 NULL NULL NULL NULL aID bID title 1 1 b1 2 2 b2 NULL NULL NULL NULL NULL NULL 3 3 b3 4 4 b4
Cross Join Join each row from the left with each row from the right. Known as a "carthesian product" SELECT * FROM a CROSS JOIN b or SELECT * FROM a, b title aID a1 1 a2 2 a3 3 a4 4 a1 1 a2 2 a3 3 a4 4 a1 1 a2 2 a3 3 a4 4 a1 1 a2 2 a3 3 a4 4 aID bID title 1 1 b1 1 1 b1 1 1 b1 1 1 b1 2 2 b2 2 2 b2 2 2 b2 2 2 b2 3 3 b3 3 3 b3 3 3 b3 3 3 b3 4 4 b4 4 4 b4 4 4 b4 4 4 b4