SQL BASIC
SQL JOINS
SQL ADVANCED
SQL REFERENCE
Advertisements

SQL RIGHT JOIN Operation

In this tutorial you will learn how to fetch data from two tables using SQL right join.

Using Right Joins

The RIGHT JOIN is the exact opposite of the LEFT JOIN. It returns all rows from the right table along with the rows from the left table for which the join condition is met.

Right join is a type of outer join that's why it is also referred as right outer join. Other variations of outer join are left join and full join. The following Venn diagram illustrates how right join works.

SQL Right Join Illustration

Note: An outer join is a join that includes rows in a result set even though there may not be a match between rows in the two tables being joined.

To understand this clearly, let's look at the following employees and departments tables.

+--------+--------------+------------+---------+
| emp_id | emp_name     | hire_date  | dept_id |
+--------+--------------+------------+---------+
|      1 | Ethan Hunt   | 2001-05-01 |       4 |
|      2 | Tony Montana | 2002-07-15 |       1 |
|      3 | Sarah Connor | 2005-10-18 |       5 |
|      4 | Rick Deckard | 2007-01-03 |       3 |
|      5 | Martin Blank | 2008-06-24 |    NULL |
+--------+--------------+------------+---------+
 
+---------+------------------+
| dept_id | dept_name        |
+---------+------------------+
|       1 | Administration   |
|       2 | Customer Service |
|       3 | Finance          |
|       4 | Human Resources  |
|       5 | Sales            |
+---------+------------------+
Table: employees Table: departments

Now, let's say you want to retrieve the names of all departments as well as the details of employees who're working in that department. But, in real situation there may be some department in which currently no employee is working. Well, let's find out.

The following statement retrieves all the available departments as well as the id, name, hiring date of the employees who belongs to that department by joining the employees and departments tables together using the common dept_id field.

SELECT t1.emp_id, t1.emp_name, t1.hire_date, t2.dept_name
FROM employees AS t1 RIGHT JOIN departments AS t2
ON t1.dept_id = t2.dept_id ORDER BY dept_name;

Tip: In a join query, the left table is the one that appears leftmost in the JOIN clause, and the right table is the one that appears rightmost.

After executing the above command, you'll get the output something like this:

+--------+--------------+------------+------------------+
| emp_id | emp_name     | hire_date  | dept_name        |
+--------+--------------+------------+------------------+
|      2 | Tony Montana | 2002-07-15 | Administration   |
|   NULL | NULL         | NULL       | Customer Service |
|      4 | Rick Deckard | 2007-01-03 | Finance          |
|      1 | Ethan Hunt   | 2001-05-01 | Human Resources  |
|      3 | Sarah Connor | 2005-10-18 | Sales            |
+--------+--------------+------------+------------------+

The right join includes all the rows from the departments table in the result set, whether or not there is a match on the dept_id column in the employees table, as you can clearly see the department "Customer Service" is included even if there is no employee in this department.

Note: If there is a row in the right table but no match in the left table, then the associated result row contains NULL values for all columns coming from the left table.

Advertisements
Bootstrap UI Design Templates Property Marvels - A Leading Real Estate Portal for Premium Properties