UNION: Selecting from multiple tables in one statement

By Bill Graziano on 8 August 2000 | Tags: SELECT


Rick writes "I am trying to design an ASP page that allows the user to input one or more parameters to search on. When they click on submit, I need to build a SQL statement that reflects their desired choices. The information they can search on, is spread across multiple tables." We'll cover the UNION statement which allows you to join multiple select statements together.

Rick needs to pass one SQL statement to the server that will allow him to search multiple tables and combine the results into one record set for his ASP page. He could call the server multiple times, once for each query and then combine the results on the ASP page programatically. That is way too much work for a programmer though. Especially one as lazy as me :)

An easier way to this is with the UNION statement. It takes multiple select statements and combines them into one result set.

Let's look at an example using the PUBS database. Let's suppose you want wanted to build an address book application that would search the AUTHORS table and the EMPLOYEE table based on last name. Your initial query would look something like this:

SELECT au_lname FROM authors
UNION ALL
SELECT lname FROM Employee
ORDER BY au_lname


This will return all the records in the AUTHORS table and then all the records in the EMPLOLYEE table in one result set. The UNION will remove duplicates from the result set by default. You can use the UNION ALL to keep the duplicates in the result set.

The ORDER BY clause applies to the entire result. You can only have one ORDER BY clause in a UNION query and it must be part of the last SELECT statement. The first SELECT statement defines the column names. I usually try to alias all my columns to the same name for easier reading. It's probably also not a good idea to use SELECT * in a UNION query as tables structures change. Now let's make this a little more complicated:

SELECT id=convert(varchar(11), au_id),
last_name = au_lname,
table_name = 'authors'
FROM authors
Where au_lname = 'smith'
UNION ALL
SELECT id=convert(varchar(11), emp_id ),
last_name = lname,
table_name = 'employee'
FROM Employee
where lname = 'Smith'
ORDER BY last_name


First we added a column I'm calling ID. We converted both AU_ID and EMP_ID to a consistent data type. SQL Server will convert them automatically if possible but I prefer to do it myself. I also added a column to identify where the record came from. With the ID and record type (table name) you should be able to build a pointer (href) on your ASP page. I also had to include the WHERE clause in each SQL statement. You can simplify that using a view but we'll leave that as an exercise for you readers.

You can string as many SELECT statements together as you'd like. The can search on different fields in each statement. Keep in mind that SQL Server will have to execute all those statements so you'll want to keep it brief. You can also use a GROUP BY in the individual SELECT statements. That's it for the UNION statement. Happy Selecting!


Related Articles

Joining to the Next Sequential Row (2 April 2008)

Writing Outer Joins in T-SQL (11 February 2008)

How to Use GROUP BY with Distinct Aggregates and Derived tables (31 July 2007)

How to Use GROUP BY in SQL Server (30 July 2007)

SQL Server 2005: Using OVER() with Aggregate Functions (21 May 2007)

Server Side Paging using SQL Server 2005 (4 January 2007)

Using XQuery, New Large DataTypes, and More (9 May 2006)

Counting Parents and Children with Count Distinct (10 January 2006)

Other Recent Forum Posts

Bulk alter SQL column data value in MS-SQL2019 in trans-sql (4h)

Bulk alter SQL column data value in MS-SQL2019 in a Trans-Sql (4h)

Filter query base on date field (4d)

Pull results from a stored procedure (4d)

Help With Query To Count Number of Pets & Vaccinations (5d)

Connect sql off network (5d)

Moving Registered Servers to a New Instance (9d)

CU 31 for 2017 Will Not Install (9d)

- Advertisement -