WHY USE DATA
SQL: Left Join, Right Join

SQL: Left Join, Right Join

Israel Ahanbare, FMVA, BIDA's photo
Israel Ahanbare, FMVA, BIDA
·

3 min read

Introduction:

In SQL, JOIN is a powerful tool that allows you to combine data from two or more tables into a single result set. There are several types of JOINs, including INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN. In this article, we will focus on how to use LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN with SQL code examples.

LEFT JOIN:

A LEFT JOIN returns all records from the left table (the first table in the JOIN clause) and the matching records from the right table (the second table in the JOIN clause). If there are no matching records in the right table, the result will contain NULL values.

Let's use the same example tables we used in the previous example, "employees" and "departments", but this time we want to retrieve all the employees and their corresponding department names, even if they don't have a department assigned.

Here's the SQL code using LEFT JOIN:

SELECT e.first_name, e.last_name, e.email, d.department_name
FROM employees e
LEFT JOIN departments d
ON e.department_id = d.department_id;

Explanation of the code:

  • SELECT is used to specify the columns to retrieve from the table. In this case, we want to retrieve the first_name, last_name, email, and department_name columns.

  • FROM is used to specify the table to retrieve the data from. In this case, we want to retrieve the data from both the employee's and departments' tables.

  • LEFT JOIN is used to join the two tables based on a common column. In this case, we want to join the employees and departments tables based on the department_id column, but this time we want to return all the records from the employee's table, even if there are no matches in the department's table.

  • ON is used to specify the condition for the join. In this case, we want to join the tables where the department_id in the employee's table matches the department_id in the department's table.

The result will contain all the employees, even if they don't have a department assigned, with their first name, last name, email, and department name displayed in separate columns. If an employee doesn't have a department assigned, the department name will be NULL.

RIGHT JOIN:

A RIGHT JOIN returns all records from the right table (the second table in the JOIN clause) and the matching records from the left table (the first table in the JOIN clause). If there are no matching records in the left table, the result will contain NULL values.

Let's use the same example tables we used in the previous example, "employees" and "departments", but this time we want to retrieve all the departments and their corresponding employees, even if they don't have any employees assigned.

Here's the SQL code using RIGHT JOIN:

SELECT e.first_name, e.last_name, e.email, d.department_name
FROM employees e
RIGHT JOIN departments d
ON e.department_id = d.department_id;

Explanation of the code:

  • SELECT is used to specify the columns to retrieve from the table. In this case, we want to retrieve the first_name, last_name, email, and department_name columns.

  • FROM is used to specify the table to retrieve the data from. In this case, we want to retrieve the data from both the employee's and departments' tables.

  • RIGHT JOIN is used to join the two tables based on a common column. In this case, we want to join the employees and departments tables based on the department_id column, but this time we want to return all the records from the department's table, even if there are no matches in the employee's table.

  • ON is used to specify the condition for the join.

SQL ServerSQLData Science