Day 75 – SQL RIGHT JOIN Keyword
In SQL, a RIGHT JOIN is a type of join that returns all the records from the right table (the second table specified in the join clause) and matching records from the left table (the first table specified in the join clause). If there are no matching records in the left table, NULL values are returned for those columns.
The basic syntax for a RIGHT JOIN is as follows:
SELECT column1, column2, ...
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;
Here, column1, column2, etc. are the names of the columns to be returned in the result set, table1 is the name of the first table, table2 is the name of the second table, and column_name is the name of the column(s) that are used to join the two tables.
For example, consider the following two tables, “employees” and “departments”:
employees:
+----+----------+-------+
| ID | Name | DeptID|
+----+----------+-------+
| 1 | John | 1 |
| 2 | Mary | 2 |
| 3 | David | 3 |
| 4 | Sarah | 2 |
| 5 | Michael | NULL |
+----+----------+-------+
departments:
+--------+--------------+
| DeptID | Department |
+--------+--------------+
| 1 | HR |
| 2 | IT |
| 3 | Finance |
+--------+--------------+
To return all employees and their departments, including employees with no department (i.e., NULL department ID), we can use a RIGHT JOIN:
SELECT e.Name, d.Department
FROM employees e
RIGHT JOIN departments d
ON e.DeptID = d.DeptID;
This will return the following result set:
+---------+--------------+
| Name | Department |
+---------+--------------+
| John | HR |
| Mary | IT |
| David | Finance |
| Sarah | IT |
| Michael | NULL |
+---------+--------------+
Note that the “Michael” row is included in the result set, even though there is no matching department record for that employee.