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 :
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, '[email protected]', 'Noida');
INSERT INTO employee VALUES (2, '[email protected]', 'Jaipur');
INSERT INTO employee VALUES (3, '[email protected]', 'Noida');
INSERT INTO employee VALUES (4, '[email protected]', 'Jaipur');
INSERT INTO employee VALUES (5, '[email protected]', 'Noida');
Step-8: Verifying the inserted data :
To select the data inside the tables as follows.
SELECT * FROM employee;
Output :
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 :
Similar Reads
SQL Full Outer Join Using Where Clause A FULL OUTER JOIN is a type of SQL Join that returns all the rows from both tables (says Table 1 or Table 2), regardless of whether they have matching values. In short, we can say it is used to retrieve all rows when there is a match in either left or right table.It combines the results of the LEFT
4 min read
SQL Full Outer Join Using Where Clause A FULL OUTER JOIN is a type of SQL Join that returns all the rows from both tables (says Table 1 or Table 2), regardless of whether they have matching values. In short, we can say it is used to retrieve all rows when there is a match in either left or right table.It combines the results of the LEFT
4 min read
SQL Full Outer Join Using Left and Right Outer Join and Union Clause An SQL join statement is used to combine rows or information from two or more than two tables on the basis of a common attribute or field. There are basically four types of JOINS in SQL. In this article, we will discuss FULL OUTER JOIN using LEFT OUTER Join, RIGHT OUTER JOIN, and UNION clause. Consi
3 min read
UNION vs UNION ALL in SQL SQL UNION and UNION ALL operators are used to concatenate results of multiple SELECT statements. However, they are different from each other. One key difference between UNION and UNION ALL in SQL is that the UNION command removes duplicates from the final results set, whereas the UNION ALL command a
6 min read
FULL OUTER JOIN in SQLite In the area of data querying and manipulation, the ability to combine information from different sources is important. SQLite, a popular embedded database engine, offers a range of join operations to fast process. One such operation FULL OUTER JOIN is particularly powerful as it allows us to merge d
4 min read
Union and Union All in MS SQL Server In MS SQL Server, the UNION and UNION ALL operators are used to combine the result sets of two or more SELECT statements into a single result set. This allows you to retrieve data from multiple tables or views and combine it into a single dataset. The primary difference between UNION and UNION ALL i
2 min read