- 4 years ago
- Zaid Bin Khalid
- 2501 Views
-
4
In this session, we will learn how to fetch data from two tables by using SQL right join.
Right, Join Operations.
The RIGHT JOIN is the opposite of the LEFT JOIN. In other words, the RIGHT JOIN will return rows from the right table along with the rows from the left table in which the Join condition is met.
In other words, you can say that the SQL right join returns all the values from the rows of the right table. It also includes the matched values from the left table but if there is no matching in both tables, it returns NULL.
Referred outer join.
OUTER JOIN is the type of RIGHT JOIN that’s why it also called a referred outer join.
Other types of outer join.
Pictorial Representation of RIGHT JOIN.
The Following Venn diagram will show how the right join works in SQL.
Outer join is a join that Added rows in a result set even though there may not be a match between two tables in a row being joined.
Proposed Tables – Using Right Joins.
To understand this easily let us take an example with two tables table 1 is the table and table 2 is the department table.
+--------+--------------+------------+---------+
| 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 |
+--------+--------------+------------+---------+
Table: employees
+---------+------------------+
| dept_id | dept_name |
+---------+------------------+
| 1 | Administration |
| 2 | Customer Service |
| 3 | Finance |
| 4 | Human Resources |
| 5 | Sales |
+---------+------------------+
Table: departments
Example – Using Right Joins.
The below example will retrieve all the available departments as well as the id, name, hiring date of the employees who belong 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;
Output Result.
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 |
+--------+--------------+------------+------------------+
In the join query, the left table is the one that shows leftmost in the JOIN clause and the right table is the one that shows rightmost in the JOIN clause.
NOTE.
If there is a row in the right table which is no match in the left table. Then the result row contains NULL values for all columns coming from the left table.
- 4 years ago
- Zaid Bin Khalid
- 2501 Views
-
4