SQL LEFT JOIN Made Easy: Learn the Basics in Minutes

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.

How to use Sql left Join?

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.

Customers Table:

Orders Table:

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.

Notes:

• 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

Here are some advanced concepts related to SQL LEFT JOIN:

Subqueries

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.

Aliases

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.