SQL JOINs Made Simple: INNER vs LEFT vs RIGHT

SQL has different JOIN methods, but for the sake of simplicity we are gonna cover only INNER JOIN, LEFT JOIN and RIGHT JOIN. the other join types have similar syntax but different purpose. JOIN is a way to combine related rows from different tables into one table that’s the main purpose of all of the different SQL Join methods.

In the following examples, we will use two tables to demonstrate how different SQL JOINs work.

-- users table:
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL
);

-- orders table:
CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INTEGER,
  total INTEGER
);

-- insert some random users:
INSERT INTO users (name) VALUES
('Alice'),
('Bob'),
('Charlie'),
('Dana');

-- insert some random orders:
INSERT INTO orders (user_id, total) VALUES
(1, 120),
(1, 60),
(2, 200),
(NULL, 50);

INNER JOIN

INNER JOIN is a way of join for combining rows from two tables that match a column value, let’s say we want to get all users who have placed at least one order.

SELECT users.name, users.id, orders.total
FROM users INNER JOIN orders
ON users.id=orders.user_id;

This query selects columns from both tables and returns only rows where users.id matches orders.user_id. Users without orders and orders without users are excluded.

Left table vs Right table

In SQL joins, table position matters.

• The table that appears immediately after FROM is called the left table.
• The table that appears after LEFT JOIN or RIGHT JOIN (before ON) is called the right table.

This distinction is irrelevant for INNER JOIN because it only returns matching rows. It becomes critical for LEFT JOIN and RIGHT JOIN because one side is preserved even when no match exists.

• LEFT JOIN preserves all rows from the left table.
• RIGHT JOIN preserves all rows from the right table.

Join behavior is determined by table order, not table names.

LEFT JOIN

LEFT JOIN is a way of join for combining rows from two tables that match a column value and also the rows that doesn’t match from the left table. let’s say we want to get all users who have placed at least one order including those with no orders

SELECT users.name, users.id, orders.total
FROM users LEFT JOIN orders
ON users.id=orders.user_id

This query keeps all rows from the users table and includes order data when a matching user_id exists.

RIGHT JOIN

RIGHT JOIN is a way of join for combining rows from two tables that match a column value and also the rows that doesn’t match from the right table. let’s say we want to get all orders including those that belong to no user.

SELECT users.name, users.id, orders.total
FROM users RIGHT JOIN orders
ON users.id=orders.user_id

This query keeps all rows from the orders table, including orders where user_id is NULL.

Visual illustration