MySQL UNION

MySQL Tutorial

MySQL-Home MySQL-Environment setup MySQL- Workbench MySQL-Basic syntax MySQL-Operator MySQL-Data type MySQL-Comments MySQL-Create DB MySQL-Drop DB MySQL-Select DB MySQL-Create Table MySQL-Drop table MySQL-Truncate MySQL-Primary Key MySQL-Foreign Key MySQL-Null MySQL-Increment MySQL-Having MySQL-Top MySQL-Insert Statement MySQL-Select Statement MySQL-Alter Statement MySQL-Where MySQL-And & Or MySQL-Default values MySQL-Exists MySQL-Order by MySQL-View MySQL-Update Statement MySQL-Delete Statement MySQL-Like MySQL-Sort MySQL-Limit MySQL-Min MySQL-Max MySQL-Group MySQL-In MySQL-Between MySQL-Union MySQL-Count MySQL-Average MySQL-Sum MySQL-Date & Time MySQL-Import MySQL-Export MySQL-Index MySQL-Temporary MySQL-Join MySQL-Full Join MySQL-Inner Join MySQL-Left Join MySQL-Right Join MySQL-Store Procedure MySQL-Injection MySQL-PHP connection

« Previous Next »

The UNION operator is used to combine the results of two or more SELECT queries into a single result set.

These are basic rules for combining the result sets of two SELECT queries by using UNION:

Each SELECT statement within UNION must have the same number of columns.

The columns must also have similar data types.

The columns in each SELECT statement must also be in the same order


SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL:


SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

Let's put these statements into real use.

We've a table named Students and Teachers in our database that contains the following records:

TECH_NO	NAME	SUBJECT	ROLL_NO
20	Kayla	PHP	6
21	Albert	Java	2
21	Albert	Java	5
22	Helen	Node.js	3
23	Anne	Python	2
24	Jaime	C++	1

The following MySQL statement returns the SUBJECTS (only distinct values) from both the "Students" and the "Teachers" table:


SELECT SUBJECT FROM Students
UNION
SELECT SUBJECT FROM Teachers
ORDER BY SUBJECT;

Now, after executing the above MySQL statement,

If some Students or teachers have the same SUBJECT, each SUBJECT will only be listed once, because UNION selects only distinct values.

Use UNION ALL to also select duplicate values!

The following MySQL statement returns the SUBJECTS (all values) from both the "Students" and the "Teachers" table:


SELECT SUBJECT FROM Students
UNION ALL
SELECT SUBJECT FROM Teachers
ORDER BY SUBJECT;

Now, after executing the above MySQL statement,

« Previous Next »

MySQL - UNION