CPSC340A
Database Systems

Review Inner Join

• Recall, use inner join to merge two tables into one

  SELECT * FROM Table1 INNER JOIN Table2 USING (id);

• This creates a table with all of the rows in Table1 that have a matching row in Table2

• What happens to the rows in Table1 that do not match a row in Table2?
• And what happens to the rows in Table2 that do not match a row in Table1?
• They are excluded from the join
• Sometimes you want to get the rows that are excluded

Outer Join

• Outer joins - join two tables, like an inner join
• But an outer join, includes rows that don’t match the ON condition
• There are two types of outer join, left and right

• The left outer join, is identical to the inner join, but it includes all rows in left table

  SELECT * FROM Table1 LEFT OUTER JOIN Table2 USING (id);

• The left table is the table on the left of the LEFT OUTER JOIN
• Note that the rows that are not in the inner join have NULL for columns from the right table

• Not surprisingly there is a right outer join

• It is identical to the inner join, but it includes all rows in the right table

  SELECT * FROM Table1 RIGHT OUTER JOIN Table2 USING (id);

• Again, rows that are not in the inner join have NULL for columns from the left table
• You can switch the order of the operands to get the same result as a right outer join using a left outer join
• You can also leave out the OUTER and just do a LEFT JOIN OR A RIGHT JOIN but I recommend leaving them for readability

• What if you want the relative complement, the rows in Table1 that do not match a row in Table2

• There is no built in syntax for this

• But it is easy to get with a filter for NULL on an outer join

  SELECT * FROM Table1 LEFT OUTER JOIN Table2 USING (id) WHERE value IS NULL;

• Note that outer joins can be chained just like inner joins

  SELECT * FROM Table1 INNER JOIN Table2 USING (id) RIGHT OUTER JOIN USING (id);