div class="row">

What is Right Outer Join In SQL.

By Manish Sharma

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.

Syntax :
  target_table RIGHT OUTER JOIN source_table
  ON(source_table.column = target_table.column)
  WHERE condition
  ORDER BY column_names;

right outer join rebellionrider

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 right outer join rebellionrider

Structure of dept table right outer join rebellionrider

Data In emp table right outer join rebellionrider

Data In dept table right outer join rebellionrider

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?
  1. When columns which are participating in ON join condition have Different name and Same Data type or
  2. 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.

  emp_name, dept_name
ON (emp.EMP_ID = dept.EMP_ID);

In the above query we are selecting emp_name from emp table and dept_name from dept table. Emp table is the source table 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

SELECT   emp_name, dept_name
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
  1. We are performing Equi Join.
  2. 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

  • Manish Sharma Oracle Rebellion Rider
  • Manish Sharma Oracle certified SQL expert
  • Manish Sharma oracle certified associate
  • Manish Sharma oracle certified professional
  • View Manish Sharma's profile on LinkedIn
  •          View Manish Sharma's profile on LinkedIn