In SQL, a LEFT JOIN is a type of outer join that returns all rows from the left table (also called the “first” or “left” table), and any matching rows from the right table (also called the “second” or “right” table). If there is no match, NULL values are returned for right table’s columns.
Here is an example of a LEFT JOIN in SQL:
SELECT *
FROM customers c LEFT JOIN orders o
ON c.customer_id = o.customer_id;
This query returns all rows from the customers table, and any matching rows from the orders table. If a customer does not have any orders, the orders columns will contain NULL values for that customer.
You can also use the LEFT JOIN syntax with multiple tables, using multiple ON clauses to specify the join conditions for each pair of tables. For example:
SELECT *
FROM customers c LEFT JOIN orders o
ON c.customer_id = o.customer_id
LEFT JOIN products p
ON o.product_id = p.product_id;
In SQL, a LEFT JOIN is a type of outer join that returns all rows from the left table (also called the “first” or “left” table), and any matching rows from the right table (also called the “second” or “right” table). If there is no match, NULL values are returned for right table’s columns.
Here is an example of a LEFT JOIN in SQL:
Copy codeSELECT *
FROM customers c LEFT JOIN orders o
ON c.customer_id = o.customer_id;
This query returns all rows from the customers table, and any matching rows from the orders table. If a customer does not have any orders, the orders columns will contain NULL values for that customer.
You can also use the LEFT JOIN syntax with multiple tables, using multiple ON clauses to specify the join conditions for each pair of tables. For example:
Copy codeSELECT *
FROM customers c LEFT JOIN orders o
ON c.customer_id = o.customer_id
LEFT JOIN products p
ON o.product_id = p.product_id;
This query returns all customers, their orders, and the products that were ordered. If a customer does not have any orders, or if an order does not have any products, the corresponding columns will contain NULL values.
LEFT JOIN Practical Examples
Here is an example of a LEFT JOIN in SQL:
SELECT * FROM customers LEFT JOIN orders ON customers.customer_id = orders.customer_id;
This example would select all rows from the customers table, and any matching rows from the orders table. If there is no match in the orders table, the NULL values will be displayed in the resulting table for the orders columns.
Here is another example using a LEFT JOIN with a WHERE clause:
SELECT c.customer_name, o.order_date
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id
WHERE o.order_date > '2022-01-01';
This example would select the customer name and order date for all customers who have placed an order after January 1, 2022. If a customer has not placed an order after that date, the order date will be NULL.
LEFT JOIN vs. INNER JOIN
Difference between LEFT JOIN and INNER JOIN
LEFT JOIN returns all rows from left table including non-matching rows. INNER JOIN returns only the matching rows between the tables involved in the JOIN. Read out article about LEFT JOIN vs INNER JOIN.