Skip to content
Related Articles

Related Articles

SQL Full Outer Join Using Union Clause
  • Last Updated : 13 Apr, 2021

In this article, we will discuss the overview of SQL, and our main focus will be on how to perform Full Outer Join Using Union Clause in SQL. Let’s discuss it one by one.

Overview :
To manage a relational database, SQL is a Structured Query Language to perform operations like creating, maintaining database tables, retrieving information from the database, etc. Here we are going to see how to create SQL Full Outer Join Using Union Clause. If the database doesn’t allow FULL JOIN (MySQL doesn’t), you may combine LEFT and RIGHT JOINS using the UNION clause.

Steps to implement SQL Full Outer Join Using Union Clause :
Here, we will first create a database named “geeks” then we will create two tables “department” and “employee” in that database. 

Step-1: Creating a database :
To create a database using the following SQL query as follows.

CREATE geeks;

Step-2: Using the database :
To use this database using the following SQL query as follows.



USE geeks;

Step-3: Creating a table :
Create a table “department” in the ‘geeks’ database using SQL query as follows.

CREATE TABLE department
(
   ID int,
   SALARY int,
   NAME Varchar(20),
   DEPT_ID Varchar(255)
);

Step-4: Inserting data :
Adding values into the table using SQL query as follows.

INSERT INTO department VALUES (1, 34000, 'ANURAG', 'UI DEVELOPERS');
INSERT INTO department VALUES (2, 33000, 'HARSH', 'BACKEND DEVELOPERS');
INSERT INTO department VALUES (3, 36000, 'SUMIT', 'BACKEND DEVELOPERS');
INSERT INTO department VALUES (4, 36000, 'RUHI', 'UI DEVELOPERS');
INSERT INTO department VALUES (5, 37000, 'KAE', 'UI DEVELOPERS');

Step-5: Verifying inserted data :
To select the data inside the tables as follows.

SELECT * FROM department;

Output :

IDSALARY NAMEDEPT_ID
134000ANURAGUI DEVELOPERS
233000HARSHBACKEND DEVELOPERS
336000SUMITBACKEND DEVELOPERS
436000RUHIUI DEVELOPERS
537000KAEUI DEVELOPERS

Step-6: Creating a table :
To create table “employee” in the ‘geeksdb’ database as follows.

Create Table employee(
 ID int,
 Email Varchar(255),
 City Varchar(20) 
);

Step-7: Inserting the values :
Add values into the table “employee” as follows.

INSERT INTO employee VALUES (1, 'ANURAG@xyz.com', 'Noida');
INSERT INTO employee VALUES (2, 'HARSH@xyz.com', 'Jaipur');
INSERT INTO employee VALUES (3, 'SUMIT@xyz.com', 'Noida');
INSERT INTO employee VALUES (4, 'RUHI@xyz.com', 'Jaipur');
INSERT INTO employee VALUES (5, 'KAE@xyz.com', 'Noida');

Step-8: Verifying the inserted data :
To select the data inside the tables as follows.

SELECT * FROM employee;

Output :

IDEmailCity
1ANURAG@xyz.comNoida
2HARSH@xyz.comJaipur
3SUMIT@xyz.comNoida
4RUHI@xyz.comJaipur
5KAE@xyz.comNoida

Step-9: Performing Full Outer Join Using Union Clause :
To create SQL Full Outer Join Using Union Clause. If the database doesn’t allow FULL JOIN (MySQL doesn’t), you may combine LEFT and RIGHT JOINS using the UNION clause.
Syntax –

SELECT  *
  FROM table1
  LEFT JOIN table2
  ON table1.columname = table2.columname
UNION 
  SELECT *
  FROM table1
  RIGHT JOIN table2
   ON table1.columname = table2.columname

Example –

SELECT  *
 FROM department
 LEFT JOIN employee
On department.ID = employee.ID
UNION
 SELECT *
 FROM department
 RIGHT JOIN employee
 On department.ID = employee.ID

Output :

IDSALARYNAMEDEPT_IDIDEmailCity
134000ANURAG UI DEVELOPERS1ANURAG@xyz.comNoida
233000HARSHBACKEND DEVELOPERS2HARSH@xyz.comJaipur
336000SUMITBACKEND DEVELOPERS3SUMIT@xyz.comNoida
436000RUHI UI DEVELOPERS4RUHI@xyz.comJaipur
537000KAE UI DEVELOPERS5 KAE@xyz.com Noida
My Personal Notes arrow_drop_up
Recommended Articles
Page :