What are JOINs in SQL?

Traditional JOINs

The most common form of JOIN operations you will encounter are INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, and CROSS JOIN. To demonstrate these JOIN types, we will use the following customers and orders (modified) tables:

SELECT
    *
FROM
    customers
INNER JOIN orders
    ON customers.customer_id = orders.customer_id;

SELECT
    *
FROM
    customers
LEFT JOIN orders
    ON customers.customer_id = orders.customer_id;

SELECT
    *
FROM
    customers
RIGHT JOIN orders
    ON customers.customer_id = orders.customer_id;

SELECT
    *
FROM
    customers
FULL JOIN orders
    ON customers.customer_id = orders.customer_id;

  (SELECT *
   FROM customers
   LEFT JOIN orders ON customers.customer_id = orders.customer_id)
UNION ALL
  (SELECT *
   FROM customers
   RIGHT JOIN orders ON customers.customer_id = orders.customer_id
   WHERE customers.customer_id IS NULL );

t1.r1 + t2.r1
t1.r1 + t2.r2
t1.r1 + t2.r3
t1.r2 + t2.r1
t1.r2 + t2.r2
t1.r2 + t2.r3
t1.r3 + t2.r1
t1.r3 + t2.r2
t1.r3 + t2.r3

LATERAL JOINs

Lateral JOINs offer a different syntax to combining tables compared to traditional JOINs. In a LATERAL JOIN, the second table is presented as a subquery, and the JOIN criteria is defined within the WHERE clause of the subquery.

According to the PostgreSQL docs:

“The LATERAL key word can precede a sub-SELECT FROM item. This allows the sub-SELECT to refer to columns of FROM items that appear before it in the FROM list. (Without LATERAL, each sub-SELECT is evaluated independently and so cannot cross-reference any other FROM item.)”

In other words, a LATERAL JOIN is like a foreach loop, where PostgreSQL iterates over each row in a result set and uses each row to evaluate the subquery.

Here is the associated query for the customers and orders table:

SELECT *
FROM customers
LEFT JOIN LATERAL
  (SELECT *
   FROM orders
   WHERE orders.customer_id = customers.customer_id ) AS ALIAS ON TRUE;

Here is the result table after using a LATERAL JOIN:

In this case, the result would be the same as a LEFT JOIN and FULL JOIN.

Note: LATERAL JOINs are supported by Postgres but only supported in MySQL versions >8

One-to-one (1-1)

In a one-to-one relation, each record in one table is associated with exactly one record in another table, and vice versa. Here is an example SQL schema demonstrating a one-to-one relation between customers and profiles tables:

CREATE TABLE customers (
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    email TEXT NOT NULL
);

CREATE TABLE profiles (
    name TEXT NOT NULL,
    gender TEXT NOT NULL,
    age TEXT NOT NULL,
    customer_email TEXT NOT NULL,
    CONSTRAINT fk_profiles_customer_email FOREIGN KEY (customer_email) REFERENCES customers (email) ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE UNIQUE INDEX customers_email_key ON customers(email);

CREATE UNIQUE INDEX profiles_customer_email_key ON profiles(customer_email);

In this example, we use the unique field (email) from the customers table as a foreign key (customer_email) in the profiles table. This is possible because each email uniquely identifies a customer. This represents a one-to-one relation because each customer can have only one profile, and a profile can belong to only one customer.

In one-to-one relations, JOINs are straightforward. You can use a traditional INNER JOIN to combine the customers with their profiles. Here is an example query:

SELECT
    *
FROM
    customers
INNER JOIN profile
    ON customers.email = profiles.customer_email;

When performing an INNER JOIN between the two tables, we would get a result set where each row contains columns from both tables, matched based on the relationship criteria. Here is an example result set:

When you join the customers and profiles tables using the INNER JOIN and the specified join condition, each row in the result set represents a customer along with their corresponding profile. In other words, we include the id and email columns from the customers table, along with the name, gender, and age of those customers from the profiles table.

One-to-many (1-n)

In a one-to-many relationship, each record in one table can be associated with multiple records in another table. Here is an example SQL schema demonstrating a one-to-many relation between users and posts tables:

CREATE TABLE users (
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    email TEXT NOT NULL
);

CREATE TABLE posts (
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    author_id INTEGER NOT NULL,
    CONSTRAINT fk_posts_author_id FOREIGN KEY (author_id) REFERENCES users (id) ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE UNIQUE INDEX users_email_key ON users(email);

This means that one user can have multiple posts, but each post is linked to only one user.

When dealing with one-to-many relations, traditional JOINs can lead to duplicated data. Consider the following query:

SELECT
    user.id, user.email, post.id AS post_id, post.title AS post_title
FROM
    user
INNER JOIN post
    ON user.id = post.authorId;

Here is an example result set based on the query above:

As you can see in the result set, Jenny's email and id get duplicated over multiple rows for each of her posts. As we incorporate more nested relations, result sets may become bloated with redundant data. This makes the query less efficient (because it requires more bandwidth for the transmission of the results) and can make it harder to interpret the data.

To address this duplication, we can pair LATERAL JOINs with JSON aggregation. JSON aggregation helps avoid duplication by consolidating multiple related records into a single field as a JSON array, while LATERAL JOINs help improve readability and can optimize performance.

Here is an example of what the result set would look like using LATERAL JOINs with JSON aggregation:

As you can see, LATERAL JOINs combined with JSON aggregation leads to a cleaner result set with no unnecessary duplication. Each of Jenny’s posts got consolidated into a JSON array, transforming three rows from the previous result set into one.

Note: A many-to-many (m-n) is not discussed in this context because it describes two 1-n relations and hence would be supported with the same type of JOIN