Right Outer Join In SQL.
As the name suggests Right Outer Join is a form of Outer Join that returns each and
every record from the source table and returns only those values from the target table
that fulfil the Join condition.
*Note: The Source table is the one situated on the
right side of the Right Outer Join Clause whereas The
Target table is the one on the left side of this clause.
target_table RIGHT OUTER JOIN source_table
ON(source_table.column = target_table.column)
ORDER BY column_names;
To demonstrate the working of Right Outer Join,
I have created two tables by the name of emp and dept
and have also inserted some data in them.
Structure of emp table
Structure of dept table
Data In emp table
Data In dept table
If you have noticed that in dept table IT and marketing department don’t have any employees.
Query 1: Right Outer Join with ‘ON’ clause
Now when should we use ON Join Condition in SQL Joins you ask?
- When columns which are participating in ON join condition have Different name and Same Data type or
- When columns which are participating in ON join condition have SAME NAME and SAME Data-type.
Let’s write a very simple Right outer join query.
FROM dept RIGHT OUTER JOIN emp
ON (emp.EMP_ID = dept.EMP_ID);
In the above query we are selecting emp_name
from emp table
from dept table
. Emp table is the
as it’s on the right hand side of join clause
thus automatically making dept table as the target table.
Column emp_id is a primary key
in emp table and foreign key in dept
table thus column emp_id is the column which is establishing a relationship in
between these two tables. As column emp_id is common in both tables thus it’s a best
fit for Join condition (ON clause).
If you execute this query you will get all the rows of emp_name column of emp table as
emp is the source table and only those rows of dept_name column of dept table which satisfy
the join condition (condition in ON clause).
Query 2: Right Outer Join with WHERE clause
You can use WHERE clause with any type of JOIN and limit or Filter the result.
In case of JOINS, WHERE clause always comes after Join Condition (ON or USING)
Say you want to see employees name and department name whose salary is less than 50,000
FROM dept RIGHT OUTER JOIN emp
ON (emp.EMP_ID = dept.EMP_ID)
WHERE emp_salary < 50000;
Query 3: Right Outer Join with USING clause
Using clause can be used on the place of ON clause when
- We are performing Equi Join.
- We want to put join on that column of source and target table which are common i.e. have same name and data type.
Most of the joins you’ll perform will be equijoins, and if you always use the same name as
the primary key for your foreign keys, then:
SELECT emp_name,dept_name FROM dept RIGHT OUTER JOIN emp USING (emp_id);
That’s all on Right Outer Joins. You can also watch my tutorial for understanding its practical usage.
Kindly please share this article on your social networking and spread the knowledge.
Thanks & Have A Great Day!
SQL Script and Presentation used
You can DOWNLOAD SQL script and presentation used in the Video and in this article.
SQL Script used in Video and in this article
Do Not Drink & Drive Use My Uber Code "UberRebellionRider"
SignUp using my referral code and get first ride worth $20 FREE