SQL LEFT JOIN Keyword

Welcome to The Coding College, your ultimate resource for mastering SQL and programming concepts. In this guide, we’ll cover the SQL LEFT JOIN keyword, a powerful tool for combining data from multiple tables.

What Is SQL LEFT JOIN?

The SQL LEFT JOIN retrieves all records from the left table and the matching records from the right table. If there is no match in the right table, the result will contain NULL for the columns from the right table.

Syntax of LEFT JOIN

SELECT column_names  
FROM table1  
LEFT JOIN table2  
ON table1.column_name = table2.column_name;  

• table1: The left table (returns all rows).
• table2: The right table (returns matching rows or NULL if no match is found).
• ON: Specifies the condition for matching rows.

Example Tables

Table: employees

Table: departments

LEFT JOIN Example

Query

SELECT employees.name, employees.salary, departments.department_name  
FROM employees  
LEFT JOIN departments  
ON employees.department_id = departments.department_id;  

Result

Explanation

1. The query combines employees and departments based on the department_id.
2. Alice, Bob, and Charlie have matching department IDs, so their department names are included.
3. Diana has no match in the departments table, so NULL is returned for the department_name column.

Practical Use Cases for LEFT JOIN

1. Employee-Department Relationship:
   • List all employees, even those not assigned to a department.
2. Customer Orders:
   • Retrieve all customers, including those who haven’t placed an order.
3. Inventory Management:
   • Show all products, including those with no supplier.

Filtering with LEFT JOIN

To filter rows, use a WHERE clause. For example, to find employees without a department:

SELECT employees.name  
FROM employees  
LEFT JOIN departments  
ON employees.department_id = departments.department_id  
WHERE departments.department_name IS NULL;  

Result

LEFT JOIN vs. INNER JOIN

LEFT JOIN with Multiple Tables

You can extend LEFT JOIN to work with more than two tables.

Query Example

SELECT employees.name, departments.department_name, projects.project_name  
FROM employees  
LEFT JOIN departments  
ON employees.department_id = departments.department_id  
LEFT JOIN projects  
ON departments.department_id = projects.department_id;  

Result

Best Practices for LEFT JOIN

• Use Meaningful Aliases:
  • Simplify references in your query.

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

• Be Cautious with Filters:
  • When adding a WHERE clause, ensure it doesn’t unintentionally exclude rows.
• Optimize with Indexes:
  • Use indexes on the join columns for faster performance.

Real-World Application

Let’s say you’re managing an e-commerce database. The customers table stores customer details, while the orders table logs purchases. To list all customers, including those who haven’t made a purchase, use a LEFT JOIN:

SELECT customers.name, orders.order_id  
FROM customers  
LEFT JOIN orders  
ON customers.customer_id = orders.customer_id;  

Conclusion

The SQL LEFT JOIN keyword is a powerful tool for retrieving data even when relationships between tables aren’t complete. By mastering this keyword, you can ensure your queries are comprehensive and insightful.

For more SQL tutorials, visit The Coding College and keep learning with us!