SQL Union Clause
The SQL UNION clause merges the results of several select queries into a single result set. Unique values are produced using the UNION clause. Use the UNION ALL clause to produce duplicate data values.
Note:
- In this instance, the number of columns and the order of the columns in each SELECT query within the UNION statement must match.
- The data type of each column must also be consistent in all SELECT queries. For instance, if column 1 of select query 1 is varchar, then column 1 of select query 2 must also be varchar.
Syntax:
SELECT column1, column2, column3,….
FROM table1
WHERE condition
UNION
SELECT column1, column2, column3,…
FROM table2
WHERE condition
Examples of UNION operators:
Let's now create three tables.
- doctors
- inpatient
- outpatient
Use the DDL command "create" to create the table and the DML command "insert" to add values.
1. Creating “doctors” table:
create table doctors(doc_id int primary key, doc_name varchar(25), doc_dept varchar(25), doc_age int);
Inserting values into “doctors” table:
insert into doctors values(1001,"james","pediatrician ",45);
insert into doctors values(1002,"michael","pulmonologist",39);
insert into doctors values(1003,"william","dermatologist",42);
insert into doctors values(1004,"jack","cardiologist",48);
insert into doctors values(1005,"daniel","general surgeon",34);
![SQL Union Clause](https://static.tutorialandexample.com/sql/sql-union-clause1.png)
2. Creating an "inpatient" table:
create table inpatient(in_id int primary key, in_name varchar(25), in_age int, in_gender varchar(10), in_doc int, foreign key(in_doc) references doctors(doc_id));
Inserting values into the "inpatient" table:
insert into inpatient values(1101, "joseph",20, "M",1003);
insert into inpatient values(1102, "elizabeth",13, "F",1001);
insert into inpatient values(1103, "george",42, "M",1004);
insert into inpatient values(1104, "john",08, "M",1001);
insert into inpatient values(1105, "emily",38, "F",1002);
![SQL Union Clause](https://static.tutorialandexample.com/sql/sql-union-clause2.png)
3. Creating an "outpatient" table:
create table outpatient(out_id int primary key, out_name varchar(25), out_age int, out_gender varchar(10), out_doc int, foreign key(out_doc) references doctors(doc_id));
Inserting values into the "outpatient" table:
insert into outpatient values(1201, "robert",25, "M",1003);
insert into outpatient values(1202, "lily",12, "F",1001);
insert into outpatient values(1203, "martin",56, "M",1004);
insert into outpatient values(1204, "charles",65, "M",1002);
insert into outpatient values(1205, "emma",07, "F",1001);
![SQL Union Clause](https://static.tutorialandexample.com/sql/sql-union-clause3.png)
Here are some SQL queries that use the UNION operator:
1. The following SQL query returns the distinct doctor IDs from the tables "inpatient" and "outpatient".
select i.in_doc from inpatient i union select o.out_doc from outpatient o;
![SQL Union Clause](https://static.tutorialandexample.com/sql/sql-union-clause4.png)
2. The following SQL query returns the inpatient and outpatient names from the tables “inpatient” and “outpatient”.
select i.in_name as name
from inpatient i
union
select o.out_name
from outpatient o;
![SQL Union Clause](https://static.tutorialandexample.com/sql/sql-union-clause5.png)
3. The following SQL query returns the patient names and ages (both inpatient and outpatient) from the tables "inpatient" and "outpatient".
select i.in_name as name, i.in_age as age
from inpatient i
union
select o.out_name, out_age
from outpatient o;
![SQL Union Clause](https://static.tutorialandexample.com/sql/sql-union-clause6.png)
4. The following SQL query returns the female patient names and ages (both inpatients and outpatients) from the tables "inpatient" and "outpatient".
select i.in_name as name, i.in_age as age
from inpatient i
where in_gender="F"
union
select o.out_name, o.out_age
from outpatient o
where out_gender="F";
![SQL Union Clause](https://static.tutorialandexample.com/sql/sql-union-clause7.png)
5. The following SQL query returns the patient names ( both inpatients and outpatients) who are consulting a pediatrician from tables "inpatient" and "outpatient".
select i.in_name as name
from inpatient i
where i.in_doc in(select d.doc_id from doctors d where doc_dept="pediatrician ")
union
select o.out_name
from outpatient o
where o.out_doc in(select d.doc_id from doctors d where doc_dept="pediatrician ");\
![SQL Union Clause](https://static.tutorialandexample.com/sql/sql-union-clause8.png)
6. The following SQL query returns the inpatient names consulting pediatricians and dermatologists from the table "inpatient".
select i.in_name as name
from inpatient i
where i.in_doc in(select d.doc_id from doctors d where doc_dept="pediatrician ")
union
select i.in_name
from inpatient i
where i.in_doc in(select d.doc_id from doctors d where doc_dept="dermatologist");
![SQL Union Clause](https://static.tutorialandexample.com/sql/sql-union-clause9.png)
7. The following SQL query returns the patient names (both inpatients and outpatients) and ages of patients whose age is less than 20 years and who are consulting a pediatrician from tables "inpatient" and "outpatient".
select i.in_name, i.in_age
from inpatient i
where i.in_age<20 and i.in_doc in(select doc_id from doctors where doc_dept="pediatrician ")
union
select o.out_name,o.out_age
from outpatient o
where o.out_age<20 and o.out_doc in(select doc_id from doctors where doc_dept="pediatrician ");
![SQL Union Clause](https://static.tutorialandexample.com/sql/sql-union-clause10.png)
8. The following SQL query returns the names and ages of inpatients whose age is less than 20 years and who are consulting pediatricians and dermatologists from table "inpatient".
select i.in_name as name, i.in_age as age
from inpatient i
where i.in_age<20 and i.in_doc in(select d.doc_id from doctors d where doc_dept="pediatrician ")
union
select i.in_name, i.in_age
from inpatient i
where i.in_age<20 and i.in_doc in(select d.doc_id from doctors d where doc_dept="dermatologist");
![SQL Union Clause](https://static.tutorialandexample.com/sql/sql-union-clause11.png)
9. The following SQL query returns the male patient names and IDs (both inpatients and outpatients) who are consulting a cardiologist from tables "inpatient" and "outpatient".
select i.in_id, i.in_name
from inpatient i
where i.in_gender="M" and i.in_doc in(select d.doc_id from doctors d where doc_dept="cardiologist")
union
select o.out_id, o.out_name
from outpatient o
where o.out_gender="M" and o.out_doc in(select d.doc_id from doctors d where doc_dept="cardiologist");
![SQL Union Clause](https://static.tutorialandexample.com/sql/sql-union-clause12.png)
10. The following SQL query returns patient names (both inpatients and outpatients), patient ids, consulting doctor ids, and the age of patients whose age is greater than 30 years and who is consulting a cardiologist from tables "inpatient" and "outpatient".
select i.in_id as id, i.in_name as name, i.in_doc as doc, i.in_age as age
from inpatient i
where i.in_age>30 and i.in_doc in(select d.doc_id from doctors d where doc_dept="cardiologist")
union
select o.out_id, o.out_name, o.out_doc, o.out_age
from outpatient o
where o.out_age>30 and o.out_doc in(select d.doc_id from doctors d where doc_dept="cardiologist");
![SQL Union Clause](https://static.tutorialandexample.com/sql/sql-union-clause13.png)
Example of UNION ALL operator:
The following SQL query returns all doctor IDs from the tables "inpatient" and "outpatient".
select i.in_doc from inpatient i union all select o.out_doc from outpatient o;
![SQL Union Clause](https://static.tutorialandexample.com/sql/sql-union-clause14.png)