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.column2Here,
- 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:
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
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.
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.
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.
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