Full Outer Join In SQL.

Today’s SQL article is all about Full Outer Join.

Before moving ahead just want to say that in order to better understand the concept of full outer Join please read my last two SQL tutorial article on right and left outer join.

Here once again we will be using same table which we have used so far in this Outer Join series. These are the emp and the dept tables.

Let’s have a quick look of the structure and the data of these two tables. 

   Desc emp;

Our table emp has 3 columns emp id, emp name and emp salary. Here column emp id is a primary key.

   Desc dept;

Table dept also has 3 columns dept id, dept name and emp id. Here in this table column dept id is a primary key where column emp id is a foreign key reference from the emp table.

Now let’s have a look at the data of these two tables.

  SELECT * FROM emp;

Emp table has 5 rows where column one has emp id and column 2 has emp name and column 3 consist the salary of these employees.

Now data of dept table

  SELECT * FROM dept;

Table Dept also has 5 columns. Column one hold dept id where column two has dept name and column three emp id which is the foreign key has only 3 records corresponding to department name sales account and finance. 

We can interpret this data easily. For example the employee with employee id 1 is named Steve in our emp table and works in department of Sales. Similarly employee with employee id 2 is Nancy who works in Accounts department and employee with employee id 3 is Guru who works in finance. Also we have not assigned any employee id for department of IT and Marketing which signifies that no one works in these departments.

Let’s jump over to full outer join. 
Full outer join is kind of a combination of both right outer join and left outer join because it returns all the rows from left as well as the right side table.

Let’s have look of FULL OUTER JOIN syntax.

Syntax
  SELECT column names FROM 

  table1 FULL OUTER JOIN / FULL JOIN table 2

  ON(expression) or USING(column_name) 

  WHERE(expression) ORDER BY column names;

Syntax is pretty similar to our left or right join as you can see. We have our SELECT statement where you can specify the name of the columns from both the participating tables followed by FROM clause. And our JOIN clause which is full outer join. Here you can either write full outer join or just outer join since both are permissible and perform the same task. And then we have our Join condition ON and USING followed by WHERE and ORDER BY clause.

Query 1 : Full Outer Join With ON clause

SELECT   emp_name, dept_name   FROM   emp   FULL OUTER JOIN   dept   ON  (emp.emp_id  =  dept.emp_id);

Here in this query we are selecting emp name column from emp table and dept name column from dept table. In our full outer join clause we have emp table on left side and dept table on right side and then we have Our ON clause where we are comparing the values of emp id columns from the both the tables.

On executing this query the result will be.



If you will observe minutely then you can see that the result till row 5 is similar to that of right outer join, As all the records from right side table is here and only those records from left side table which satisfy the join condition are here in the result. Followed by all the remaining records from the tables thus the last row 6 and 7 contains the remaining emp names.

Here in this ON join clause we used columns which have same name and data type. Now let’s use columns which have different name and same data type for example column
dept id.

SELECT   emp_name, dept_name   FROM   emp   FULL OUTER JOIN   dept   ON  (emp.emp_id  =  dept.dept_id);

and the result of this query is

Query 2 : Full Outer Join With USING clause

We use USING join condition when

The column in join condition share the same name and same data type and are compared only using = comparison operator and no other comparison operator such as greater than, less than etc. 

As you can see, in the join condition (ON clause) of Query 1 we have used emp id column of both the tables. This column shares the same name and same data type hence we can easily replace this ON clause with USING clause. Let’s do it. 

SELECT   emp_name, dept_name   FROM   emp   FULL OUTER JOIN   dept   USING  (emp_id);

The result of this query is 


If you compare this result with the result of our first query then you will find them as exactly the same. 

Query 3 : Full Outer Join With WHERE clause

You can also limit the result by using WHERE clause. Say you want to see the name of only those employees and their departments who have a salary of less than 50000

For that we can modify our query 2 and add a where clause to it.

SELECT   emp_name, dept_name, emp_salary   FROM   emp   FULL OUTER JOIN   dept   USING  (emp_id) 
WHERE emp.emp_salary < 50000;

On executing this query you will get only those employee and their departments who has salary less than 50,000 

Query 4 : Full Outer Join With ORDER BY clause

Similarly you can sort the result using ORDER BY clause. Say you want to sort the result of query 3 in ascending order according to the employee name (emp_name column) For that just add the ORDER BY clause followed by the column name which is emp_name in our case. 

SELECT   emp_name, dept_name   FROM   emp   FULL OUTER JOIN   dept   USING  (emp_id)  
WHERE emp.emp_salary < 50000  ORDER BY emp_name;

If you execute this query then by default the result will be sorted in ascending order. 


If you write DESC right after the column, your result will be sorted in descending order. For Example 

SELECT   emp_name, dept_name   FROM   emp   FULL OUTER JOIN   dept   USING  (emp_id)  
WHERE emp.emp_salary < 50000   ORDER BY emp_name DESC;

That’s it on Full Outer Joins. If you found it useful then please share it with your friends on social networking. You can also write to me for any queries. Keep reading for more such concepts. 

Thanks and take care.