SQL: Union and Union All

The concepts described here are generic and should work on all database management systems. However, the commands were tested on a Windows XP machine running MySQL 4.1.22, 5.0.84, and 5.1.39 (community editions).

UNION is used to combine the result from multiple SELECT statements into a single result set. By default, when it is applied to more than one queries, returns unique result sets only. UNION ALL on the other hand returns all rows in the result sets.

For example, we have two tables as follows:

student:
-----------------------------
|  id        |    name      |
-----------------------------
|  1         |    John      |
|  2         |    David     |
|  3         |    Daniel    |
-----------------------------

teacher:
-----------------------------
|  id        |    name      |
-----------------------------
|  1         |    Ryan      |
|  2         |    David     |
|  3         |    Leslie    |
-----------------------------

Please note that the second row (id = 2) in both tables is exactly the same. Also, you can use different data types for the columns (e.g. int in student and smallint in teacher). Lets run a few SQL queries using the data above.

(SELECT * FROM student)
UNION
(SELECT * FROM teacher);

The above SQL returns only 5 rows (by removing the duplicate rows with id=2). Now, if you want to display all rows (no matter if they are unique or not), run the following:

(SELECT * FROM student)
UNION ALL
(SELECT * FROM teacher);

The SQL query above returns all 6 rows from both tables.

Did this tutorial help a little? How about buy me a cup of coffee?

Please feel free to use the comments form below if you have any questions or need more explanation on anything. I recommend thoroughy testing on a production-like test system first before moving to production.

0 comments so far. Be the first one to leave a comment on this article.

Name
Email address
Website
Comments
	Notify me of follow-up comments via email.

Similar Tutorials:

• all-schemas: Tool to run multiple SQL queries on a MySQL database server
• Download all maatkit scripts through a single command
• MySQL table_cache, information_schema, and open files limit
• How to check MySQL version number?
• opensource-db-tools - A set of open source tools for MySQL

Tutorials in 'Database Management Systems > MySQL' (more):

• How to set default values for MySQL JSON columns
• How to delete rows in table when PK is referenced in another table
• Unique primary keys(PKs) for MySQL using Redis
• How to rename MySQL DB by moving tables
• How to set innodb_flush_log_at_timeout in MySQL 5.6?

Recent Tutorials:

• How to set default values for MySQL JSON columns
• How to delete rows in table when PK is referenced in another table
• Unique primary keys(PKs) for MySQL using Redis
• How to rename MySQL DB by moving tables
• How to compile and install Varnish-cache on Linux