Relationships and Joins

April 2021

Relationships

One-to-One Relationship

There is only one match for an id in each of the two tables. There are no duplicate id values in either table: only one row in the left table exists with an id of 1, and only one row in the right table has an id of 1. Eg. Each US state is managed by one governor; and each governor is in charge of one (the same) state.

One-to-Many Relationship

A key value in the first table will have multiple matching values in the second table’s joined column. Eg. Each US state contains multiple counties.

Many-to-Many Relationship

Multiple rows in the first table will have multiple matching rows in the second table. Eg. Students and classes: a student can register for many classes, and a class can include many students.

Table Joins

When you join tables in a query, the database connects rows in both tables where the columns you specified for the join have matching values. The query results then include columns from both tables if you requested them as part of the query.

Consider the two following tables:

Inner Join

We use JOIN when we want to return rows that have a match in the columns following the ON keyword. Only rows that match in both tables are kept.

SELECT *
-- JOIN and INNER JOIN are equivalent
FROM companies_left cl JOIN companies_right cr
ON cl.id = cr.id;

Produces:

NOTE: Watchout for this type of join as there may be unintended loss of data (eg. we may want to keep data from the left table even though it has no reference on the right table, for such a case, see LEFT JOIN).

Left Join and Right Join

In contrast to JOIN, the LEFT JOIN and RIGHT JOIN keywords each return all rows from one table (left table if LEFT JOIN; right table if RIGHT JOIN) and display blank rows from the other table if no matching values are found in the joined columns.

SELECT *
FROM companies_left cl 
LEFT OUTER JOIN companies_right cr 
ON (cl.id = cr.id);

Produces:

Full Outer Join

When you want to see all rows from both tables in a join, regardless of whether any match, use the FULL OUTER JOIN option. A full outer join is admittedly less useful and used less often than inner and left or right joins. Still, you can use it for a couple of tasks: to merge two data sources that partially overlap or to visualize the degree to which the tables share matching values.

SELECT *
FROM companies_left cl 
FULL OUTER JOIN companies_right cr 
ON (cl.id = cr.id);

Produces:

Cross Join

In a CROSS JOIN query, the result (also known as a Cartesian product) lines up each row in the left table with each row in the right table to present all possible combinations of rows. Because the join doesn’t need to find matches between key fields, there’s no need to provide the clause using the ON keyword.

SELECT *
FROM companies_left
CROSS JOIN companies_right;

Produces: