SQL Full Outer Join Using Union Clause

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 :

ID	SALARY	NAME	DEPT_ID
1	34000	ANURAG	UI DEVELOPERS
2	33000	HARSH	BACKEND DEVELOPERS
3	36000	SUMIT	BACKEND DEVELOPERS
4	36000	RUHI	UI DEVELOPERS
5	37000	KAE	UI 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 :

ID	Email	City
1	ANURAG@xyz.com	Noida
2	HARSH@xyz.com	Jaipur
3	SUMIT@xyz.com	Noida
4	RUHI@xyz.com	Jaipur
5	KAE@xyz.com	Noida

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 :

ID	SALARY	NAME	DEPT_ID	ID	Email	City
1	34000	ANURAG	UI DEVELOPERS	1	ANURAG@xyz.com	Noida
2	33000	HARSH	BACKEND DEVELOPERS	2	HARSH@xyz.com	Jaipur
3	36000	SUMIT	BACKEND DEVELOPERS	3	SUMIT@xyz.com	Noida
4	36000	RUHI	UI DEVELOPERS	4	RUHI@xyz.com	Jaipur
5	37000	KAE	UI DEVELOPERS	5	KAE@xyz.com	Noida

My Personal Notes arrow_drop_up

Save