Section 1.1: INNER JOIN

The INNER JOIN is the most frequently utilized join type. It connects two tables and retrieves only those records that have matching values in both. For instance, if you search for a user with user_id equal to 1, the INNER JOIN will return only the rows from the second table that contain that id.

Example of INNER JOIN:

CREATE TABLE users (

id SERIAL PRIMARY KEY,

name TEXT NOT NULL

);

CREATE TABLE orders (

id SERIAL PRIMARY KEY,

value NUMERIC NOT NULL,

user_id INT NOT NULL,

CONSTRAINT fk_user_id

FOREIGN KEY(user_id)

REFERENCES users(id)

ON DELETE CASCADE

);

INSERT INTO users (name) VALUES ('Joe'), ('John'), ('Olivia');

INSERT INTO orders (value, user_id) VALUES

(100, 1), (200, 1), (150, 2), (250, 2), (115, 3), (162, 3);

When joining tables, it's crucial to use the JOIN keyword and specify the tables involved. Although it's technically feasible to join on any columns, it’s advisable to utilize primary and foreign keys for clarity and consistency.

Section 1.2: LEFT JOIN

Initially, I perceived the LEFT JOIN as a method to fetch all records from the left table alongside any matching entries from the right table. Notably, even if there are no matches in the right table, the LEFT JOIN will still return data from the left table, filling in NULLs for unmatched records.

Example of LEFT JOIN:

INSERT INTO users (name) VALUES ('Charlotte');

INSERT INTO orders (value, user_id) VALUES

(100, 1), (200, 1), (150, 2), (250, 2), (115, 3), (162, 3);

In this case, the first six records will yield corresponding data from the right table. However, if there’s no match for a subsequent entry, the LEFT JOIN will still return data from the left table, albeit with NULL values for the right.

Section 1.3: RIGHT JOIN

A RIGHT JOIN functions similarly to a LEFT JOIN, but it prioritizes the right table. This join retrieves all records from the right table and attempts to find matching entries from the left.

Example of RIGHT JOIN:

If the orders table is missing a corresponding entry in the users table, the foreign key constraint will prevent the insertion of such records. However, if a RIGHT JOIN is executed, it will display data from the right table, potentially resulting in NULLs for unmatched records from the left.

Section 1.4: SELF JOIN

The concept of a self-join may seem peculiar at first. It allows for a table to be joined with itself, which can be useful for comparing data within the same table. No special keyword is needed; any join method can be applied here.

Example of SELF JOIN:

CREATE TABLE teams (

id SERIAL PRIMARY KEY,

name TEXT NOT NULL

);

INSERT INTO teams (name)

VALUES ('Arsenal'), ('Real Madrid'), ('Atletico Madrid'), ('Liverpool');

This structure enables us to identify matchups where each football team can alternate between home and away games.

Section 1.5: FULL OUTER JOIN

A FULL OUTER JOIN retrieves all records from both tables involved in the join, matching them when possible and returning NULLs where matches do not exist. This type of join effectively merges the capabilities of LEFT and RIGHT joins.

Example of FULL OUTER JOIN:

DROP TABLE IF EXISTS users CASCADE;

CREATE TABLE users (

id SERIAL PRIMARY KEY,

name TEXT NOT NULL

);

DROP TABLE IF EXISTS orders CASCADE;

CREATE TABLE orders (

id SERIAL PRIMARY KEY,

value NUMERIC NOT NULL,

user_id INT NOT NULL

);

INSERT INTO users (id, name)

VALUES (1, 'Joe'), (2, 'John'), (4, 'Olivia'), (6, 'Charlotte');

INSERT INTO orders (value, user_id) VALUES

(100, 1), (200, 1), (150, 2), (250, 2), (115, 3), (162, 3), (152, 4), (211, 5);

In this example, NULL values appear for names and values due to missing data in either the left or right table.

Section 1.6: CROSS JOIN

A CROSS JOIN produces a Cartesian product of the two tables, merging every row from the first table with every row from the second. This results in a full combination of all rows, which can lead to duplicates.

Example of CROSS JOIN:

CREATE TABLE teams (

id SERIAL PRIMARY KEY,

name TEXT NOT NULL

);

INSERT INTO teams (name)

VALUES ('Arsenal'), ('Real Madrid'), ('Atletico Madrid'), ('Liverpool');

Unlike a self-join, a CROSS JOIN generates all possible pairings of rows from both tables.

Section 1.7: NATURAL JOIN

The NATURAL JOIN is often considered the least useful, as it allows PostgreSQL to determine which columns to join. I generally avoid this type of join in my code to maintain full control over the join conditions.

SELECT select_list

FROM table1

NATURAL [INNER, LEFT, RIGHT] JOIN table2;

Thank you for exploring this guide on SQL joins! Your thoughts and feedback are always welcome, so feel free to share your insights.

Video Description: This course from 2019 focuses specifically on inner joins in PostgreSQL, providing a detailed overview with practical examples.

If you found this information helpful and want to engage with our community, consider following us! Your support is greatly appreciated.