Using MySQL UNION Operator

Many operators exist in MySQL to retrieve data from multiple tables based on the requirements. One of the useful MySQL operators is UNION. It is used to combine records from two or more tables by writing a single query. Each select statement used with the UNION operator must contain the same numbers of fields and the data type of each field will be the same also. It retrieves all common and uncommon fields values of all tables mentioned in the query by removing duplicate entries.

Syntax:

SELECT field1, field2, ... fieldn
FROM table1
[WHERE clause]

UNION [DISTINCT]

SELECT field1, field2, ... fieldn
FROM table2
[WHERE cluase];

Here, the WHERE clause and DISTINCT modifier are optional. If you want to run a select query based on any condition then run the WHERE clause. It is mentioned before that duplicate records are removed automatically when running the query with a UNION operator. So using the DISTINCT modifier is useless.

Prerequisite:

You have to create the necessary database and tables with some records to know the use of the UNION operator. At first, connect with the database server using mysql client and run the following SQL statement to create a database named ‘company’.

CREATE DATABASE company;

Select the current database by executing the following statement.

USE company;

Run the following SQL statement to create a table named ‘products’ of five fields (id, name, model_no, brand, and price). Here, ‘id‘ is the primary key.

CREATE TABLE products (
id INT(5) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
model_no VARCHAR(50) NOT NULL,
brand VARCHAR(50) NOT NULL,
price int(5)) ENGINE = INNODB;

Run the following SQL statement to create a table named ‘suppliers’ of four fields (id, name, address, pro_id). Here, ‘id’ is a primary key and pro_id is a foreign key.

CREATE TABLE suppliers (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
address VARCHAR(50) NOT NULL,
pro_id INT(5) UNSIGNED NOT NULL,
FOREIGN KEY (pro_id) REFERENCES products(id) ON DELETE CASCADE)
ENGINE = INNODB;

Run the following SQL statement to insert four records into the products table.

INSERT INTO products values
(NULL,'Samsung 42” TV', 'TV-78453' , 'Samsung', 500),
(NULL,'LG Fridge', 'FR-9023','LG', 600)
(NULL,'Sony 32” TV','TV-4523W' , 'Sony', 300),
(NULL,'Walton Washing Machine','WM-78KL', 'Walton', 255);

Run the following SQL statement to insert six records into the suppliers table.

INSERT INTO suppliers values
(NULL,'Rahman Enterprise', 'Dhanmondi', 1),
(NULL,'ABC Electronics', 'Mirpur', 2),
(NULL,'Nabila Enterprise', 'Mogbazar', 2),
(NULL,'Naher plaza', 'Eskaton', 3),
(NULL,'Walton Plaza', 'Eskaton', 4)
(NULL,'Walton Plaza', 'Dhanmondi', 4);

***Note: It is assumed that the reader is familiar with the SQL statements for creating a database and table or inserting data into tables. So the screenshots of the above statements are omitted.

Run the following SQL statement to see current records of the products table.

SELECT * FROM products;

Run the following SQL statement to see current records of the suppliers table.

SELECT * FROM suppliers;

Here, the supplier name ‘Walton Plaza‘ exists in two records. When these two tables are combined with the UNION operator then a duplicate value will be generated but it will be removed automatically by default and you will not require to use a DISTINCT modifier.

Use of a Simple UNION operator

The following query will retrieve the data of pro_id and name fields from suppliers table, and id and name fields from products table.

SELECT pro_id as `Product ID`, name as `Product Name or Supplier Name`
FROM suppliers

UNION

SELECT id as `Product ID`, name as `Product Name or Supplier Name`
FROM products;

Here, products table contains 4 records and suppliers table contains 6 records with one duplicate record (‘Walton Plaza’). The above query returns 9 records after removing the duplicate entry. The following image shows the output of the query where ‘Walton Plaza’ appears for one time.

Use of UNION with single WHERE clause

The following example shows the use of the UNION operator between two select queries where the second query contains a WHERE condition to search those records from suppliers table that contains the word, ‘Walton’ in the name field.

SELECT id as `Product ID`, name as `Product Name or Supplier Name`
FROM products

UNION

SELECT pro_id as `Product ID`, name as `Product Name or Supplier Name`
FROM suppliers
WHERE suppliers.name like '%Walton%';

Here, The first select query will return 4 records from products table and the second select statement will return 2 records from suppliers table because, the word, ‘Walton’ appears two times in the ‘name’ field. The total 5 records will be returned after removing the duplicate from the result set.

Use of UNION with multiple WHERE clause

The following example shows the use of a UNION operator between two select queries where both queries contain where condition. The first select query contains a WHERE condition that will search those records from products which price values are less than 600. The second select query contains the same WHERE condition as the previous example.

SELECT id as `Product ID`, name as `Product Name or Supplier Name`
FROM products
WHERE price < 600

UNION

SELECT pro_id as `Product ID`, name as `Product Name or Supplier Name`
FROM suppliers
WHERE suppliers.name like '%Walton%';

Here, 4 records will be returned as output after removing the duplicates.

Use of UNION ALL with multiple WHERE clause

It is shown in the previous examples that all duplicate records are removed by UNION operators by default. But if you want to retrieve all records without removing duplicates then you have to use UNION ALL operator. The use of UNION ALL operator is shown in the following SQL statement.

SELECT id as `Product ID`, name as `Product Name or Supplier Name`
FROM products
WHERE price < 600

UNION ALL

SELECT pro_id as `Product ID`, name as `Product Name or Supplier Name`
FROM suppliers
WHERE suppliers.name like '%Walton%';

The following image shows that the returned result set contains the duplicate records after running the above statement. Here, ‘Walton Plaza’ appears two times.

Conclusion:

The uses of UNION operators in the SQL statement are explained in this tutorial by using simple examples. I hope, the readers will be able to use this operator properly after reading this article.

from Linux Hint https://ift.tt/3a1h40r