Left Join (Left Outer Join) Sql
In SQL database management systems, a LEFT JOIN, also known as a LEFT OUTER JOIN, is a popular type of join method for combining two different tables in an SQL database. It returns all table column rows from the first table in the left table join process and the corresponding exact matching table row values from the second table in the right table join process. If no proper match is found in the database tables, NULL values will be defined for the columns of the right table in the final result.
In other words, a LEFT JOIN method in database tables ensures that all records from the first (left) table are returned, regardless of whether a matching record exists in the second (right) table. If a match is found in both tables, the related data information from the second (right) table is added. If no match is found, NULL values are added and displayed in the columns of the right table.
Syntax of LEFT JOIN in SQL database.
SELECT columns
FROM tableX
LEFT JOIN tableY
ON tableX.column_name = tableY.column_name;
Elements of LEFT JOIN in SQL database.
- tableX – In the SQL database table, the left table is the one from which all table row values are returned.
- tableY – In the SQL database table, the right table is the one from which matching table row values are returned.
- column_name – This is the table column used for exact matching between the two database tables in the left join process.
How LEFT JOIN works in SQL database tables.
The left table in the SQL database always previews all its table rows as a result set.
If a proper exact matching row exists in the right database table, the value information from the right database table will be added.
If no matching row exists in the right database table, NULL values will be added for the columns of the right table in the database result set.
Basic LEFT JOIN example in SQL database tables.
Required database table information.
- Employee – The Employee table contains the total details of employee.
- employee_id, name, department_id
- Department – The Department table contains all the details of departments.
- department_id, department_name
Sample data for the employee database table.
Employee table.
employee_id emp_name department_id
101 Siddhi deora S101
201 Harry deora H204
301 Bhavshi deora B302
307 Anil Jain NULL
Sample data for the department database table.
department_id department_name
K403 Marketing
M908 Development
N888 Design
Employee and department table query.
SELECT employee.emp_name, department.department_name
FROM employee
LEFT JOIN department
ON employee.department_id = department.department_id;
Explanation of Employee and department table LEFT JOIN method.
- Here, the database table query extracts the employee’s name and their department_name from the department table.
- The LEFT JOIN in the database table ensures that all employee data from the employee table is returned. It even displays employee records that have not yet been assigned a department, such as the employee Anil Jain.
Result of Employee and department table.
emp_name department_name
Siddhi deora Marketing
Harry deora Development
Bhavshi deora Design
Anil Jain NULL
How LEFT JOIN works in SQL database tables.
Here, the departments of Siddhi deora, Harry deora, and Bhavshi deora match properly in the database table, which is why their department names are added here.
Anil Jain does not have a department defined; the department_id has a NULL value, which is why his department name is displayed as NULL.
Example of LEFT JOIN with no matching data in the right table.
- So, let’s modify the data in the SQL database table in such a way that there are no matching rows for some records in the right database table.
Updated database table query.
Employee Table/same data.
employe_id emp_name department_id
101 Siddhi deora S101
201 Harry deora H204
301 Bhavshi deora B302
301 Anil Jain NULL
Department database table/modified table data.
department_id department_name
K403 Marketing
M908 Development
N888 Design
Statement of Employee and department table query.
SELECT employe.emp_name, department.department_name
FROM employe
LEFT JOIN department
ON employe.department_id = department.department_id;
Explanation of the employee and department table query.
- Here, Siddhi Deora has a matching department, Marketing, but Harry Deora and Bhavshi Deora do not have matching department IDs in the department table. Because the department_id is defined as NULL for Anil Jain, no table match is found for him either.
- In this LEFT JOIN process, it will return NULL values for the department_name for Harry Deora, Bhavshi Deora, and Anil Jain, as there is no proper match for them in the second department table.
Result of the employee and department table match query.
emp_name Department Name
Siddhi Deora Marketing
Harry Deora NULL
Bhavshi Deora NULL
Anil Jain NULL
How LEFT JOIN works in SQL database management.
Here, all records from the employee table are included in the result, even if there is no proper match in the department table.
When no match is found in the department table, it displays a NULL value in the department_name column.
Example of LEFT JOIN with aliases in SQL database management.
- Using table aliases in SQL database management can make database queries more detailed and advanced, especially when database users are working with multiple tables simultaneously.
Employee and department table query with aliases.
SELECT e.emp_name, d.department_name
FROM employee AS e
LEFT JOIN department AS d
ON e.department_id = d.department_id;
Explanation of the query with aliases database table.
- Here, e and d are defined as aliases for the employee and department tables in the database table.
- This table query is more compact, but its output previews the same result as before.
LEFT JOIN example with multiple tables in SQL database management.
Here, database users can also apply LEFT JOIN with two or more database tables.
So, let’s add a third table, the pay table, to store the employee’s salary information.
All database table information.
- Employee – Here, the Employee table contains table column information such as employe_id, emp_name, department_id, etc.
- Department – Here, the Department table contains table column information such as department_id, department_name, etc.
- Pay – Here, the Pay table contains table column information such as employe_id, salary, etc.
Employee table sample data output.
Employee table query.
employe_id emp_name department_id
101 Siddhi deora S101
201 Harry deora H204
301 Bhavshi deora B302
307 Anil Jain NULL
Pay table query.
employe_id salary
10 34909
30 65000
40 71987
Employee, department, and pay table query.
SELECT e.emp_name, d.department_name, p.pay
FROM employe AS e
LEFT JOIN department AS d
ON e.department_id = d.department_id
LEFT JOIN pay AS p
ON e.employe_id = p.employee_id;
Explanation of the Employee, department, and pay table query.
- This database table query displays the employee’s name, department name, and pay/salary information.
- The LEFT JOIN in these three tables ensures that all employees from the Employee table are included, and if an employee does not have department or salary information, those table columns will be defined and used as NULL.
Result of the Employee, department, and pay table query.
emp_name department_name salary
Siddhi deora Marketing 34909
Harry deora Development 65000
Bhavshi deora Design 71987
Anil Jain NULL NULL
How LEFT JOIN works in SQL database management.
In this output, employees Siddhi Deora, Harry Deora, and Bhavshi Deora have matching departments and salaries, but Anil Jain does not have a record of his salary, which is why NULL information is displayed in the salary column.
Anil Jain also does not have any department record, so the text value NULL is displayed in the department_name column.
Main Differences Between INNER JOIN and LEFT JOIN
| Join Aspect | INNER JOIN features | LEFT JOIN/LEFT OUTER JOIN features |
| What the table join returns | Inner join is used to Only rows with matching values in both tables table X and table Y. | Left join used to All rows from the left table, and matching rows from the right table at a same time in both tables. |
| Non-matching join table rows | Inner join used to Excludes table rows without matches in both tables at a same time. | Left join adds table rows from the first left table, with NULL for columns in the second right table if with no match. |
| Usage cases of joins | Inner join Used when database user only wants table rows with matching table data. | Left join Use when database user wants all table records from the left first table, even if there are no matches in the second right table. |
Conclusion of Left Join/Left Outer Join in Database Management.
In SQL database management systems, LEFT JOIN or LEFT OUTER JOIN is used when the database user wants to ensure that all table column rows from the left (first) database table are included and previewed in the result, regardless of whether there is any matching row data in the right (second) database table. If there is no match in the database tables, NULL values will be displayed for the columns of the right (second) table in the result. This is the best option for situations where the database user wants to secure and manage all records from the left table, even if there is no related table data information present in the related table.
