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:

1. In this instance, the number of columns and the order of the columns in each SELECT query within the UNION statement must match.
2. 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.

1. doctors
2. inpatient
3. 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);

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);

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);

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;

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;

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;

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";

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 ");\

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");

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 ");

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");

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");

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");

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;