SQL LEFT

What is a SQL LEFT JOIN?

LEFT JOIN returns every row from the left table and the matching rows (or NULLs) from the right table.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL LEFT: PostgreSQL, MySQL, MariaDB, SQL Server, Oracle, SQLite, Snowflake, BigQuery, and virtually all ANSI-compliant databases.

SQL LEFT Full Explanation

LEFT JOIN (often written as LEFT OUTER JOIN) is a relational operation that combines two tables. It keeps all rows from the table listed before the LEFT JOIN keyword (the left table). For each left-table row, the database searches the right table for rows that satisfy the join condition. When a match is found, columns from the right table are returned; when no match exists, the right-table columns are filled with NULLs. This makes LEFT JOIN ideal for retaining unmatched rows while still pulling any related data that does exist.Key points:- The result set size is always at least the number of rows in the left table.- Rows that do not satisfy the ON condition in the right table produce NULLs in right-table columns.- Adding filters in the WHERE clause that reference right-table columns can accidentally convert a LEFT JOIN to an INNER JOIN by removing NULL rows; use IS NULL checks instead.- LEFT JOIN obeys standard SQL evaluation order: FROM and JOINs first, then WHERE, then GROUP BY, HAVING, and SELECT.

SQL LEFT Syntax

SELECT <column_list>
FROM <left_table>
LEFT [OUTER] JOIN <right_table>
  ON <join_condition>;

SQL LEFT Parameters

- left_table (table) - The primary table whose rows are all preserved.
- right_table (table) - The secondary table from which matching rows are pulled.
- join_condition (expression) - Boolean expression that defines how rows relate, usually using equality on key columns.

Example Queries Using SQL LEFT

-- 1. Show every customer, plus their order id if one exists
SELECT c.customer_id,
       c.name,
       o.order_id
FROM   customers AS c
LEFT JOIN orders AS o
  ON c.customer_id = o.customer_id;

-- 2. Find customers who have never placed an order
SELECT c.customer_id,
       c.name
FROM   customers AS c
LEFT JOIN orders AS o
  ON c.customer_id = o.customer_id
WHERE  o.order_id IS NULL;

Expected Output Using SQL LEFT

Query 1 returns all customers
Customers with orders show order_id; others show NULL
Query 2 returns only customers with no matching order rows

Use Cases with SQL LEFT

Reporting on entities that may or may not have related records (customers without orders, products without reviews).
Creating dimensional models where the fact table may lack rows for some dimensions.
Building audit or completeness checks to identify missing relationships.
Retaining left-side data while enriching with optional right-side attributes.

Common Mistakes with SQL LEFT

Forgetting the ON clause, which causes a Cartesian product before WHERE filtering.
Placing conditions on right-table columns in WHERE instead of ON or using IS NULL, which removes the NULL rows and turns the join into an INNER JOIN.
Assuming LEFT JOIN improves performance over INNER JOIN; it can be slower because it keeps more rows.
Selecting ambiguous column names without table aliases, leading to errors.

First Introduced In

SQL-92

Frequently Asked Questions

How does LEFT JOIN differ from INNER JOIN?

An INNER JOIN shows only rows that meet the join condition in both tables. LEFT JOIN preserves every row from the left table and inserts NULLs for right-table columns when no match exists.

Can I LEFT JOIN more than two tables?

Yes. Write additional LEFT JOIN clauses, each with its own ON condition, to pull optional data from several related tables.

What happens if I omit the ON clause?

Without an ON clause, the database performs a Cartesian product of the two tables and then applies WHERE filters. This is almost never desired and can create huge result sets.

How do NULLs behave in LEFT JOIN filters?

Rows where right-table columns are NULL survive the join. To keep those rows while filtering, use WHERE right_table.column IS NULL instead of equality checks.