WikiGalaxy
SQL Left Joins
Understanding Left Joins:
A SQL LEFT JOIN returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL from the right side if there is no match.
SELECT employees.name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.id;
Example Explanation:
This query fetches all employees' names. If an employee does not belong to any department, the department name will be NULL.
Console Output:
John Doe | Sales
Jane Smith | NULL
Using Aliases in Left Joins
Point Heading:
Aliases can help simplify queries by providing short names for tables.
SELECT e.name, d.department_name
FROM employees AS e
LEFT JOIN departments AS d ON e.department_id = d.id;
Example Explanation:
Here, 'e' and 'd' serve as aliases for the 'employees' and 'departments' tables, making the query more readable.
Console Output:
Alice Brown | HR
Bob Johnson | NULL
Left Join with Multiple Tables
Point Heading:
LEFT JOIN can be used with multiple tables to retrieve comprehensive data sets.
SELECT e.name, d.department_name, p.project_name
FROM employees AS e
LEFT JOIN departments AS d ON e.department_id = d.id
LEFT JOIN projects AS p ON e.project_id = p.id;
Example Explanation:
This query returns employee names along with their department and project names, if available.
Console Output:
Charlie Davis | Marketing | Project X
Diana Prince | NULL | NULL
Filtering Results with WHERE Clause
Point Heading:
Using the WHERE clause with LEFT JOIN allows for filtering specific data.
SELECT e.name, d.department_name
FROM employees AS e
LEFT JOIN departments AS d ON e.department_id = d.id
WHERE d.department_name IS NOT NULL;
Example Explanation:
This query filters out employees who are not assigned to any department.
Console Output:
Eve Adams | Finance
Left Join with Aggregate Functions
Point Heading:
Aggregate functions can be used with LEFT JOIN to perform calculations on the joined data.
SELECT d.department_name, COUNT(e.id) AS number_of_employees
FROM departments AS d
LEFT JOIN employees AS e ON e.department_id = d.id
GROUP BY d.department_name;
Example Explanation:
This query counts the number of employees in each department, including departments with no employees.
Console Output:
IT | 3
Logistics | 0
Joining Tables with Different Column Names
Point Heading:
LEFT JOIN can be applied to tables with different column names by specifying the correct columns to join on.
SELECT e.name, p.project_name
FROM employees AS e
LEFT JOIN projects AS p ON e.current_project = p.project_code;
Example Explanation:
This query joins the employees and projects tables using different column names to show which project each employee is currently working on.
Console Output:
Frank Castle | Project Z
Grace Hopper | NULL
Left Join with Self Join
Point Heading:
A self join using LEFT JOIN can compare rows within the same table, showing unmatched records as NULL.
SELECT a.name AS Employee, b.name AS Manager
FROM employees AS a
LEFT JOIN employees AS b ON a.manager_id = b.id;
Example Explanation:
This query lists employees along with their managers, showing NULL for employees without managers.
Console Output:
Hank Pym | Scott Lang
Wanda Maximoff | NULL
Combining Left Join with ORDER BY
Point Heading:
The ORDER BY clause can be used with LEFT JOIN to sort the results in a specified order.
SELECT e.name, d.department_name
FROM employees AS e
LEFT JOIN departments AS d ON e.department_id = d.id
ORDER BY e.name ASC;
Example Explanation:
This query sorts employee names in ascending order while displaying their department names, if available.
Console Output:
Isaac Newton | Science
Nikola Tesla | NULL
