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 the same tables 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 1 has emp id and column 2 has emp name and column 3 consists of 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 the left as well as the right side table.
Let’s have a look at 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 employees and their departments who have 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 network. You can also write to me for any queries. Keep reading for more such concepts.
Thanks and have a great day!