Text copied!
SQL left join
LEFT JOIN (or LEFT OUTER JOIN) returns all the rows from the left table and the matching rows from the right table. If there is no match, the result will have null values for the right table columns.
The syntax of the SQL left join generally looks like this :
SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;
• 'table1' and 'table2' refer to the two tables being joined together.
• 'column_name(s)' refer to the selected columns from the tables being joined together.
• 'ON' clause is used to specify the conditions for joining two or more tables together.
As seen in above image, SQL left join produces a result set that includes each row from the left table (Table-A) and any matching rows from the right table (Table-B), if any.
Here's an example of how you might use the SQL left join :
Let's suppose we have two tables :
[a] Employees Table :
→ It contains columns [Employee_Id], [Employee_Name], [Gender] and [Department_Id].
[b] Departments Table :
→ It contains columns [Department_Id] and [Department_Name].
1. Let's use SQL left join to retrieve the employees names and their respective department names by joining the [Employees] and [Departments] tables together based on matching values in both the tables.
2. Please run the following SQL statement :
SELECT Emp.Employee_Name, Emp.Department_Id, Dpt.Department_Name
FROM [Employees] Emp
LEFT JOIN [Departments] Dpt
ON Emp.Department_Id = Dpt.Department_Id
3. Above statement selects the columns from both tables based on the matching values in the 'DepartmentId' column. It returns all the rows from the left table and the matching rows from the right table as the output.
• As you have seen, Only specific selected columns ( [Employee_Name], [Department_Id] from "Employees" table and [Department_Name] from "Departments" table ) are returned.
• It returned only the rows where the [Department_Id] column contained the values '1', '2', or '5'. This was done because '1' and '2' were the only values shared by both tables.
• The left table ('Employees') also included '5' but with no matching 'Department_Name' value based on [Department_Id] column, resulting in a 'NULL' value for that column.
That's it! You have successfully retrieved data using 'LEFT JOIN' statement.