The UNION statement


The Union operator is a useful way to combine the result-set of multiple SELECT statements. In order to use the union operator, each SELECT statement must have >The same number of columns >Similar data types on each column >The columns in the same order

An example of the syntax of the UNION statement is as follows:

CREATE TABLE Customer ( name TEXT,customerID INTERGER, city TEXT, email TEXT);
CREATE TABLE Supplier ( name TEXT,supplierID INTERGER, city TEXT);

INSERT INTO Customer ( name, customerID, city,email) VALUES
    ("John", 1, "Huston", NULL), ("Eric", 2, "Paris", "[email protected]"), ("Jessica",3, "Kiev", "[email protected]"),
    ("Mike", 4, "Paris", "[email protected]"), ("Jeff", 5, NULL, "[email protected]"),("Ben", 6, NULL, "[email protected]");

INSERT INTO Supplier ( name ,supplierID , city ) VALUES
    ("Kevin", 1, "Madrid"), ("Nikolai", 2, "Kiev"), ("Joao",3, "Fortaleza"),
    ("Kim", 4, "Seul"), ("Emir", 5, "Dheli"),("Natasha", 6, "Kiev"),("Eric", 2, "Paris");

SELECT name, customerID, city
FROM Customer 
UNION
SELECT  name ,supplierID , city
FROM Supplier

Notice that ONLY the columns in the select statement must mach with others other columns in select statements within the UNION Statement. As you can see in the example shown above, the supplier's table lacks a fourth column, but the union still works due to it not selecting a fourth field in any select

The UNION statement only select the distinct values by default. To allow duplicate values, use UNION ALL:

CREATE TABLE Customer ( name TEXT,customerID INTERGER, city TEXT, email TEXT);
CREATE TABLE Supplier ( name TEXT,supplierID INTERGER, city TEXT);

INSERT INTO Customer ( name, customerID, city,email) VALUES
    ("John", 1, "Huston", NULL), ("Eric", 2, "Paris", "[email protected]"), ("Jessica",3, "Kiev", "[email protected]"),
    ("Mike", 4, "Paris", "[email protected]"), ("Jeff", 5, NULL, "[email protected]"),("Ben", 6, NULL, "[email protected]");

INSERT INTO Supplier ( name ,supplierID , city ) VALUES
    ("Kevin", 1, "Madrid"), ("Nikolai", 2, "Kiev"), ("Joao",3, "Fortaleza"),
    ("Kim", 4, "Seul"), ("Emir", 5, "Dheli"),("Natasha", 6, "Kiev"),("Eric", 2, "Paris");

SELECT name, customerID, city
FROM Customer 
UNION ALL
SELECT  name ,supplierID , city
FROM Supplier

The UNION statement is specially useful when each select is paired with a where clause, allowing multiple filters in the same query.

Exercise

Retrieve all the data of suppliers from Kiev and all the customers from Paris.


Copyright © learnsqlonline.org. Read our Terms of Use and Privacy Policy