Database Tutorial

Two Table Joins

Learning Outcomes

• Learn how to perform a CROSS JOIN of two tables.
• Learn how to perform a filtered CROSS JOIN of two tables.
• Learn how to perform a INNER JOIN of two tables.
• Learn how to perform a NATURAL JOIN of two tables.
• Learn how to perform a LEFT, RIGHT, and FULL OUTER JOIN of two tables.
• Learn how to perform a self join between two copies of the same table.
• Learn how to perform a non-equijoin between two tables.

Lesson Materials

You can joins rows between two tables by several techniques. Those techniques are covered below.

CROSS JOIN

CROSS JOIN matches every row in one table against every row in another table.

INNER JOIN

INNER JOIN matches every row in one table against every row in another table where the value in one column of a table matches the value in one column the other table. It is also possible that the unique key may include more than one column. In the latter case, the match between tables uses a set of columns from each table.

NATURAL JOIN

NATURAL JOIN matches every row in one table against every row in another table where the value in one column of a table matches the value in one column the other table; or it matches every row in one table against every row in another table where the values in one set of columns in a table matches the values in another set of columns in another table. A NATURAL JOIN differs from an INNER JOIN because it implicitly discovers the column or set of columns to match.

LEFT JOIN

LEFT JOIN matches every row in one table against every row in another table where the value in one column of a table matches the value in one column the other table, and all rows in the left table not found in the right table. Like an LEFT JOIN, the unique key may include more than one column. The match between tables uses a set of columns from each table when the primary and foreign key values exist in a set of columns instead of a single column.

RIGHT JOIN

RIGHT JOIN matches every row in one table against every row in another table where the value in one column of a table matches the value in one column the other table, and all rows in the right table not found in the left table. Like an RIGHT JOIN, the unique key may include more than one column. The match between tables uses a set of columns from each table when the primary and foreign key values exist in a set of columns instead of a single column.

FULL JOIN

FULL JOIN matches every row in one table against every row in another table where the value in one column of a table matches the value in one column the other table, and the symmetric difference, which consists of:

• All rows in the left table not found in the right table.
• All rows in the right table not found in the left table.

Like a LEFT JOIN and RIGHT JOIN, the unique key may include more than one column. The match between tables uses a set of columns from each table when the primary and foreign key values exist in a set of columns instead of a single column.

Self Join

A self join occurs between a column or a set of columns in two copies of the same table. A self join works with INNER, LEFT, RIGHT, and FULL OUTER JOINS.

Non-equijoin

A non-equijoin join is a filtered CROSS JOIN that matches every row in one table against every row in another table and then filters the return set of rows based on rules in the WHERE clause.