|
| |
MySQL Union query |
Many time we require results from different
tables or different queries. We try one after the other and list the result
accordingly. There is a better way to do this by using UNION command or query.
We can list records of two different tables by combining two sql queries to one.
What is the advantage ?
We can as well
write the query two times and display one after the other. The point here is we
can pick up unique records also. Say we have two tables one is storing name of
the football players and other is storing players of baseball team.
We want to display a list of players who are member of one of the team at
least. Here we can display the name of the players by using UNION command. If
John is member of football and baseball team then his name will appear
once only. This is as if we have applied distinct sql command to a field in a
single table. If we want to display all the records then we can use ALL along
with the UNION command.
Here are two tables with three records each. Please
note that there is one common record . Name Alex is there in both the tables.
| Baseball |
Football |
| name |
age |
| Reid |
16 |
| Alex |
21 |
| Greek |
19 |
|
| name |
age |
| John |
20 |
| Alex |
21 |
| Robort |
23 |
|
|
|
SELECT name,age FROM football
UNION
SELECT name,age FROM baseball
|
OUT put is > |
| name |
age |
| John |
20 |
| Alex |
21 |
| Robort |
23 |
| Reid |
16 |
| Greek |
19 |
|
You can see from the above output that only unique records are displayed so the
record Name Alex is displayed once. If the requirement is to display all the
records then we have to add ALL command to the UNION query
SELECT name,age FROM football
UNION ALL
SELECT name,age FROM baseball
|
OUT put is > |
| name |
age |
| John |
20 |
| Alex |
21 |
| Robort |
23 |
| Reid |
16 |
| Alex |
21 |
| Greek |
19 |
|
Now in the above result records from both the tables are displayed.
| |
|
| HOME |
| SQL Tutorial List |
| SQL (Home) |
| SQL Commands |
|
|
|
|
|
| Subscribe |
|
Submit your email address and receive
article and product notifications. Your email is safe with us.
|
|
|
|
