SQL FULL OUTER JOIN

FULL JOIN returns all records from the left table and right table, if the records do not match then shows NULL value.
FULL JOIN is used between two tables.
FULL JOIN is also called FULL OUTER JOIN.

For SELECT Statement

Syntax:-


SELECT table_name1.column_name(s), table_name2.column_name(s)
FROM table_name1
FULL OUTER JOIN
table_name2
ON table_name1.column_name=table2.column_name

Example:- Suppose we have two table employees and emp_salary.

employees Table:- this table has the employee’s details.


+----+------------+-----------+----------------+-----------+
| id | first_name | last_name | email          | country   |
+----+------------+-----------+----------------+-----------+
|  1 | John       | Tailor    | john@abc.com   | USA       |
|  2 | Rom        | Tailor    | rom@abc.com    | USA       |
|  3 | Andrew     | Symonds   | andrew@abc.com | Australia |
|  4 | Miacle     | Tailor    | miacle@abc.com | Australia |
|  5 | Sachin     | Tendulkar | sachin@abc.com | India     |
|  6 | Virat      | Kohli     | virat@abc.com  | India     |
|  7 | rohit      | NULL      | rohit@abc.com  | India     |

emp_salary table:- this table employee’s salary details.

  
| id | emp_id | salary |
+----+--------+--------+
|  1 |      5 | 200000 |
|  2 |      6 | 180000 |
|  3 |      8 | 300000 |
|  4 |      9 | 400000 |
+----+--------+--------+

Find all employee’s details which has a salary or not.

Query:-


SELECT employees.id,employees.first_name,employees.last_name,
employees.email,employees.country,emp_salary.salary 
FROM employees
FULL OUTER JOIN
emp_salary
On employees.id=emp_salary.emp_id

Output:-


+----+------------+-----------+----------------+-----------+---------
| id | first_name | last_name | email          | country   | salary |
+----+------------+-----------+----------------+-----------+--------+
|  5 | Sachin     | Tendulkar | sachin@abc.com | India     | 200000 |
|  6 | Virat      | Kohli     | virat@abc.com  | India     | 180000 |
|  1 | John       | Tailor    | john@abc.com   | USA       |   NULL |
|  2 | Rom        | Tailor    | rom@abc.com    | USA       |   NULL |
|  3 | Andrew     | Symonds   | andrew@abc.com | Australia |   NULL |
|  4 | Miacle     | Tailor    | miacle@abc.com | Australia |   NULL |
|  7 | rohit      | NULL      | rohit@abc.com  | India     |   NULL |
|NULL| NULL       | NULL      | NULL           | NULL      | 300000 |
|NULL| NULL       | NULL      | NULL           | NULL      | 400000 |
+----+------------+-----------+----------------+-----------+--------+

SQL FULL OUTER JOIN – Interview Questions

Q 1: What is FULL OUTER JOIN?

Ans: Returns all records from both tables.

Q 2: What if no match exists?

Ans: NULL values are returned.

Q 3: Is FULL OUTER JOIN supported in MySQL?

Ans: No, it must be simulated.

Q 4: Can WHERE be used with FULL JOIN?

Ans: Yes

Q 5: When is FULL JOIN useful?

Ans: For complete data comparison.