SQL LEFT JOIN is a type of JOIN that returns all the rows from the left table and the matching rows from the right table, and if there are no matching rows in the right table, it returns NULL. In this tutorial, we will explain how to use SQL LEFT JOIN with examples.
Syntax: The basic syntax for SQL LEFT JOIN is as follows:
SELECT column1, column2, ... FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;
Example: Suppose we have two tables, Customers and Orders. Customers table contains customer information, and Orders table contains order information. We want to join these two tables and get all customers information, along with the corresponding order information, if available.
To join these two tables using SQL LEFT JOIN, we can use the following query:
SELECT Customers.CustomerName, Orders.OrderDate FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
This will return the following result:
In this example, the Customers table is on the left side of the JOIN statement, and the Orders table is on the right side of the JOIN statement. The ON keyword specifies the join condition, which is based on the CustomerID column in both tables. The result contains all rows from the Customers table and the matching rows from the Orders table. If there is no matching row in the Orders table, the OrderDate column will be NULL.
- SQL LEFT JOIN is useful when you want to get all the data from the left table, even if there is no matching data in the right table.
- If you want to get all the data from the right table, you can use SQL RIGHT JOIN.
- If you want to get all the data from both tables, you can use SQL FULL OUTER JOIN.
Advanced concepts related to SQL LEFT JOIN
You can use subqueries in the ON clause of the LEFT JOIN to filter out the records from the right table based on a condition. For example:
SELECT employees.employee_name, departments.department_name FROM employees LEFT JOIN( SELECT department_id, department_name FROM departments WHERE department_name LIKE 'Sales%' ) AS departments ON employees.department_id = departments.department_id;
In this example, a subquery is used in the ON clause to retrieve only the departments that have names starting with “Sales”. This filters out the records from the right table (departments) that don’t meet the condition.
Multiple LEFT JOINs
You can use multiple LEFT JOINs to join more than two tables. In this case, the order of the joins matters, and you need to use parentheses to specify the join order. For example:
SELECT customers.customer_name, orders.order_id, order_items.product_name FROM customers LEFT JOIN orders ON customers.customer_id = orders.customer_id LEFT JOIN order_items ON orders.order_id = order_items.order_id WHERE customers.country = 'USA';
In this example, three tables (customers, orders, and order_items) are joined using two LEFT JOINs. The parentheses are used to specify that the first join is between customers and orders, and the second join is between orders and order_items. The WHERE clause is used to filter the results based on the customer’s country.
You can use aliases to give different names to the tables or columns used in the LEFT JOIN. This is especially useful when you join multiple tables, and the column names are ambiguous. For example:
SELECT c.customer_name, o.order_date, i.product_name FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id LEFT JOIN order_items i ON o.order_id = i.order_id;
In this example, aliases are used to give short names to the tables (customers, orders, and order_items), making the query more readable. The aliases are specified using the AS keyword.