SQL: Union and Union All

Tutorials > Database Management Systems > MySQL > Tips And Tricks > 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.

Similar Tutorials:

• MySQL tables statistics (size, engine, no. of rows etc.)
• MySQL Performance Optimization Part I
• How to see table definition statements in MySQL
• Seeing indexes in a MySQL table
• How to find if MySQL supports partitioning or not

Support Geeks Worldwide:

Link to us:

You can support us by putting a link to our website on your blog or website (code is below).

<a href="http://www.geeksww.com/">
Geeks Worldwide - Tutorials about Software Installation,
Configuration, Administration, Monitoring, Tools, Tips &
Tricks
</a>

OR a simple one.

<a href="http://www.geeksww.com/">
Geeks Worldwide - IT related Tutorials
</a>

Feedbacks:

We appreciate feedbacks and suggestions about our tutorials and Geeks Worldwide from readers. Please contact us using the form here and let us know what you think about the tutorial and the website in general.

Bookmark Us:

We are working on new features for the website, please keep visiting or bookmark us using your favourite bookmarking service.

Subscribe to RSS:

You can subscribe to our RSS feed here.