MySQL

In MySQL, the LEFT JOIN is used to retrieve all records from the left (or first) table and the matching records from the right (or second) table. If there is no match between the two tables, the result will still include all rows from the left table, and NULL values will be returned for columns from the right table where no match is found. This is useful when you want to include all data from one table, even if there is no corresponding data in the related table.

Here’s a detailed guide on using the LEFT JOIN in MySQL, along with examples for new students:

1. Basic Syntax of LEFT JOIN:

   • The LEFT JOIN returns all rows from the left table and the matching rows from the right table. If there is no match, NULL is returned for columns from the right table.
   • Syntax:

   SELECT e.first_name, e.last_name, d.department_name 
   FROM employees e 
   LEFT JOIN departments d 
   ON e.department_id = d.department_id;

   • This query returns all employees and their corresponding department names. If an employee does not belong to any department, NULL will be returned for the department name.

2. Using LEFT JOIN with WHERE Clause:

   • You can combine LEFT JOIN with a WHERE clause to filter the results further.
   • Example:

   SELECT e.first_name, e.last_name, d.department_name 
   FROM employees e 
   LEFT JOIN departments d 
   ON e.department_id = d.department_id 
   WHERE d.department_name = 'HR' OR d.department_name IS NULL;

   • This query returns all employees who either work in the HR department or have no assigned department.

3. Handling NULL Values with LEFT JOIN:

   • Since LEFT JOIN includes rows without matches, you can use the IS NULL condition to find rows where no match was found in the right table.
   • Example:

   SELECT e.first_name, e.last_name 
   FROM employees e 
   LEFT JOIN departments d 
   ON e.department_id = d.department_id 
   WHERE d.department_id IS NULL;

   • This query retrieves all employees who do not belong to any department.

4. Using LEFT JOIN with Multiple Tables:

   • You can perform LEFT JOIN on more than two tables to retrieve data from multiple related tables.
   • Example:

   SELECT e.first_name, e.last_name, d.department_name, b.branch_name 
   FROM employees e 
   LEFT JOIN departments d 
   ON e.department_id = d.department_id 
   LEFT JOIN branches b 
   ON d.branch_id = b.branch_id;

   • This query retrieves employees, their department names, and the corresponding branch names if they exist. If an employee does not have a department or if the department does not have a branch, NULL will be returned for those fields.

5. Using LEFT JOIN with Aggregate Functions:

   • You can use aggregate functions like COUNT(), SUM(), or AVG() with LEFT JOIN to perform calculations while including all rows from the left table.
   • Example:

   SELECT d.department_name, COUNT(e.employee_id) AS employee_count 
   FROM departments d 
   LEFT JOIN employees e 
   ON d.department_id = e.department_id 
   GROUP BY d.department_name;

   • This query returns all departments along with the count of employees in each department. Departments with no employees will still be included with a count of 0.

6. Combining LEFT JOIN with ORDER BY:

   • You can combine LEFT JOIN with ORDER BY to sort the results based on columns from either the left or right table.
   • Example:

   SELECT e.first_name, e.last_name, d.department_name 
   FROM employees e 
   LEFT JOIN departments d 
   ON e.department_id = d.department_id 
   ORDER BY d.department_name ASC;

   • This query returns all employees, sorting the results by department name in ascending order.

7. Performance Considerations:

   • LEFT JOIN can impact query performance, especially on large datasets, as it includes rows without matches. Indexing the columns used in the ON clause can improve performance.
   • Example:

   SELECT e.first_name, e.last_name, d.department_name 
   FROM employees e 
   LEFT JOIN departments d 
   ON e.department_id = d.department_id 
   WHERE e.salary > 50000;

   • Indexing department_id in both tables can help improve the performance of this query.

8. Combining LEFT JOIN with INNER JOIN:

   • You can combine LEFT JOIN with INNER JOIN in complex queries to retrieve data from multiple tables with different join types.
   • Example:

   SELECT e.first_name, e.last_name, d.department_name, b.branch_name 
   FROM employees e 
   LEFT JOIN departments d 
   ON e.department_id = d.department_id 
   INNER JOIN branches b 
   ON d.branch_id = b.branch_id;

   • This query retrieves employees, their department names, and the corresponding branch names, ensuring that employees without departments are included, but only branches that have departments are shown.

The LEFT JOIN is a powerful tool in MySQL when you need to retrieve all records from one table, regardless of whether there are matching records in another table. This makes it useful for including data that may be incomplete or missing relationships between tables.

To gain complete access, login with gmail or outlook, no need of signup, click here

SELECT prd_product.product_id, prd_product.product_code, prd_product.product_name 
    act_order_detail.order_id
FROM prd_product 
LEFT JOIN act_order_detail ON act_order_detail.product_id = prd_product.product_id
ORDER BY prd_product.product_id, act_order_detail.order_id;

SELECT a.order_number
    a.order_date
FROM act_order a
LEFT JOIN act_payment b on b.order_id = a.order_id
WHERE b.order_id IS NULL;

SELECT org_client.client_id, org_client.first_name as client_name
    sum(act_order.sub_total) as lifetime_order_amount
FROM org_client
LEFT JOIN act_order on act_order.client_id = org_client.client_id
GROUP BY org_client.client_id, org_client.first_name
ORDER BY org_client.client_id;

EXAMPLE 1 - SQL LEFT JOIN

Here is a clear example of a LEFT JOIN. Retrieve information of all products along with their respective order details, including products that have not been associated with any orders.

EXAMPLE 1 - Tansy Academy Data Model

In this task, you will create a query involving two tables, named Products and Order details, marked are the columns necessary for the query.

EXAMPLE 1 - LEFT JOIN query

To achieve this, you need to execute a LEFT JOIN between the product table and the order detail table, utilizing the primary key and foreign key column, wherein the product ID column serves as the joining column. In this scenario, the product table functions as the left table, and the order detail table is considered the right table. The left table, which contains the essential primary business information, focuses on the product list as the primary requirement. At a secondary level, we seek order IDs, so we treat the order details as the secondary table, positioned on the right side of the join

SELECT prd_product.product_id, prd_product.product_code, prd_product.product_name, act_order_detail.order_id FROM prd_product LEFT JOIN act_order_detail ON act_order_detail.product_id = prd_product.product_id ORDER BY prd_product.product_id, act_order_detail.order_id;

EXAMPLE 1 - Query Data Mapping

In this visual representation, a yellow background denotes a correspondence between the primary key and foreign key. Meanwhile, a light yellow background with red font on the left side signifies products lacking a corresponding row in the order details table. These products will be included in the final result, but with NULL values for order IDs. It's important to recognize that a LEFT JOIN incorporates all rows from the left table, which, in this case, is the product table.

EXAMPLE 1 - Final OUTput

In this view, it's evident that certain product records from the left table have multiple corresponding rows on the right side. Consequently, the product information will be associated with each order entry from the right side.