Left Join in SQL Server

Left Join in SQL Server

Introduction:
The Left Join is one of the types of joins in SQL Server that allows you to combine rows from two or more tables based on a related column. It retrieves all the rows from the left table and matching rows from the right table. If there are no matching rows in the right table, NULL values are returned for the columns from the right table.

Syntax:

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

Explanation:

• SELECT column_list: Specifies the columns you want to retrieve from the tables. You can use * to select all columns or provide a list of specific column names.

• table1: The left table in the join operation, from which all rows will be retrieved.

• LEFT JOIN: Specifies the type of join you want to perform. In this case, it's a Left Join.

• table2: The right table in the join operation, from which matching rows will be retrieved.

• ON table1.column_name = table2.column_name: The condition that specifies how the two tables are related. It defines the columns used for matching rows between the left and right tables.

Example:
Consider two tables, employees and departments, with the following data:

Table: employees

emp_id	emp_name	department_id
1	John	101
2	Jane	102
3	Mike	101
4	Sarah	103

Table: departments

department_id	department_name
101	HR
102	Finance
103	Marketing

Query:

sql
SELECT emp_name, department_name
FROM employees
LEFT JOIN departments
ON employees.department_id = departments.department_id;

Result:

emp_name	department_name
John	HR
Jane	Finance
Mike	HR
Sarah	Marketing

Explanation:
In the above example, we used a Left Join to combine the employees and departments tables based on the department_id column. All rows from the employees table were retrieved, and matching department names from the departments table were included. Since there's no matching department for Sarah in the departments table, the department_name column contains NULL for her record.