SQL Basics - Combining Queries Using UNION

Many times you have two queries that you want to make, but you want the results all in one table. You can do it with the OR function in the WHERE clause, but you can also use unions. Why not just use OR? Well, if you are retrieving the data from two different tables containing similar data, such as sales from two different years. You can't use joins, because there are no connecting columns. What do you do now? Use Unions. It's very simple. You take your 2 SQL queries, or 10 SQL queries and put the word UNION in between. Such as.:

SELECT prod_name, vend_id
FROM Products
WHERE vend_id='DLL01'
UNION
SELECT prod_name, vend_id
FROM Products
WHERE vend_id='BRS01'

As stated above, you can connect two different tables, even if they don't contain the same columns. For example:

SELECT prod_name, vend_id FROM Products WHERE vend_id='DLL01'

UNION

SELECT cust_name, cust_address FROM Customers

ORDER BY 1

Now why you want to do what I just did is beyond me, but at least you see it works.

BEWARE OF THE RULES INVOLVED:

1. All the queries must contain the same number of columns for the output, or you'll set an error message.

2. The columns that are part of the UNION must be of similar datatype, such as both test, both number, both numeric, etc. (this is what is exploited in SQL injection to get nice error messages.)

The UNION operator automatically filters out duplicate entries. How about if you had one table containing orders from 2007 and one from 2008 and you filtered by name. The duplicate order would be filtered out, which is not acceptable. For this we use UNION ALL. It is used the same way as UNION, and it returns all entries including duplicates.

See you next time.