SQL LEFT JOIN

The SQL LEFT JOIN combines two tables based on a common column. It then selects records having matching values in these columns and the remaining rows from the left table.

Example

-- left join Customers and Orders tables based on their shared customer_id columns
-- Customers is the left table
-- Orders is the right table

SELECT Customers.customer_id, Customers.first_name, Orders.item
FROM Customers
LEFT JOIN Orders
ON Customers.customer_id = Orders.customer_id;

Here, the code left joins the Customers and Orders tables based on customer_id, which is common to both tables.


SQL LEFT JOIN Syntax

SELECT columns_from_both_tables
FROM table1
LEFT JOIN table2
ON table1.column1 = table2.column2

Here,

  • table1 is the left table to be joined
  • table2 is the right table to be joined
  • column1 and column2 are the common columns in the two tables

Example: SQL LEFT Join

-- left join the Customers and Orders tables

SELECT Customers.customer_id, Customers.first_name, Orders.amount
FROM Customers
LEFT JOIN Orders
ON Customers.customer_id = Orders.customer;

Here's how this code works:

How to use LEFT JOIN in SQL
Example: SQL LEFT JOIN

Here, the SQL command combines data from the Customers and Orders tables.

The query selects the customer_id and first_name from Customers and the amount from Orders.

Hence, the result includes rows where customer_id from Customers matches customer from Orders.


More on SQL LEFT JOIN

JOIN With AS Alias

We can use AS aliases inside LEFT JOIN to make our query short and clean. For example,

-- use alias C for Categories table
-- use alias P for Products table

SELECT C.cat_name, P.prod_title
FROM Categories AS C
LEFT JOIN Products AS P
ON C.cat_id= P.cat_id;

Here, the command left joins the Categories and Products tables while assigning the aliases C and P to them, respectively.

NULL Values in LEFT JOIN

When performing a LEFT JOIN, it's common to encounter NULL values in the result set for rows in the right table that don't have a matching row in the left table.

To handle these NULL values, we can use the COALESCE() function. For example,

-- display order status for each customer, handling NULL values

SELECT c.customer_id, COALESCE(s.status, 'No Orders') AS order_status
FROM Customers c
LEFT JOIN Shippings s ON c.customer_id = s.customer
ORDER BY c.customer_id;

In this command, we're joining the Customers table with the Shippings table. The COALESCE() function ensures that if the status column is NULL, it displays No Orders.

This way, we always have an order status for each customer.

To learn more, visit SQL COALESCE() Function.

LEFT JOIN With WHERE Clause

We can use the LEFT JOIN statement with an optional WHERE clause. For example,

SELECT Customers.customer_id, Customers.first_name, Orders.amount
FROM Customers
LEFT JOIN Orders
ON Customers.customer_id = Orders.customer
WHERE Orders.amount >= 500;

Here, the SQL command joins the Customers and Orders tables and selects rows where the amount is greater than or equal to 500.

LEFT JOIN Multiple Tables

We can also use LEFT JOIN to combine more than two tables.

-- join Customers, Orders, and Shippings tables
SELECT C.customer_id, C.first_name, O.amount, S.status
FROM Customers C
LEFT JOIN Orders O
ON C.customer_id = O.customer_id
LEFT JOIN Shippings S
ON C.customer_id = S.customer;

This command will join three tables and return a row for each customer, including their order amounts and shipping status, if available.

Note: To learn more about joining multiple tables, visit SQL JOIN Multiple Tables.


Also Read

Did you find this article helpful?

Our premium learning platform, created with over a decade of experience and thousands of feedbacks.

Learn and improve your coding skills like never before.

Try Programiz PRO
  • Interactive Courses
  • Certificates
  • AI Help
  • 2000+ Challenges