SQL - Joins - Left

In SQL, a left join is used to combine rows from two tables based on a matching column from the left table and a matching column from the right table. The left table is the first table listed in the join clause, and it contains all the rows from that table, regardless of whether there is a match in the right table. The right table may or may not contain matching rows, and if it does not, the result set will include NULL values for those columns.



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

In this syntax, table1 is the left table, and table2 is the right table. The ON clause specifies the column from each table that should be used to match the rows. The column_list is a comma-separated list of the columns that should be included in the result set.


Suppose we have two tables, employees and departments.

We can use a left join to retrieve all the employees and their corresponding departments, including those who do not have a department assigned to them. Here's the SQL query:

SELECT e.emp_name, d.dept_name
FROM employees e
LEFT JOIN departments d
ON e.dept_id = d.dept_id;

In the above example, the left join returns all the rows from the employees table, including the row for employee Bob who does not have a department assigned to him. The matching rows from the departments table are also included in the result set, and NULL values are returned for the dept_name column for employee Bob.