Multiple Table Queries in SQL Server – UNION, EXCEPT, INTERSECT, Subqueries, and Joins

There are several for creating queries that return data from multiple
tables. The one you choose depends on your data retrieval requirements and the
underlying structure of the database. In this three part article series, I will demonstrate how to combine data from multiple tables by using UNION, INTERSECT, and
EXCEPT. You’ll perform advanced queries by using joins and subqueries. You’ll
learn that SQL Server supports inner joins, right outer joins, left outer
joins, and full joins. You’ll use subqueries to break a query into logical
steps. You compare correlated and noncorrelated subqueries and saw when it is
appropriate to use each.

The UNION operator

Use the UNION operator to combine the results of two or more SELECT
statements to generate a single result set. The syntax for using UNION is as
follows:

The
statements combined must have the same number of columns and compatible data types.
The column names from the first statement are used as headings for the result
set. Include the ALL keyword if you do not want UNION to remove duplicates.
Only the last statement in the UNION operation can have an OPTION clause.

Have a look at the following Venn diagram to get a better
understanding of UNION
and UNION
ALL operators:

For example, the following query returns all products associated
with a purchase order and all products associated with a sales order:

This
returns the following result set:

To
keep duplicates, you would run:

This
returns the following:

The EXCEPT operator

You
use the EXCEPT operator to compare the results of two queries and return only
the distinct values from the first query that are not found in the second
query. The following is the basic syntax for using the EXCEPT operator:

As
with the UNION operator:


The queries must have the same number of columns in the SELECT list.


The columns must be listed in the same order.


The data types for the columns must be compatible.

Have a look at the following diagram:

For
example, let’s rewrite the preceding query to return only distinct ProductID,
UnitPrice, and OrderQty values of those products associated with the purchase
orders that do not have an associated sales order. Have a look at the following
code:

This
returns the following result set:

The INTERSECT operator

You
use the INTERSECT operator to compare the results of two queries and return
only the distinct values from the first query result set that are also found in
the second query result set. The general syntax for the INTERSECT statement is
as follows:

The
INTERSECT operator has the same SELECT list restrictions as the UNION and EXCEPT
operators. The following Venn diagram will help you understand the INTERSECT
operator:

For example, to find out which currency codes exist in both the Sales.Currency table
and the Sales.CurrencyRate
table, we need to retrieve the distinct list of currency codes,
for which we will run the following query:

This
returns the following result set:

Subqueries

A
subquery is a query that is nested inside a SELECT, INSERT, UPDATE, or DELETE
statement, or inside another subquery. You use subqueries to break a
query into logical steps. By using subqueries, you can often solve a complex
data retrieval or modification problem with a single statement. You use
subqueries in situations in which a query depends on the results of another query.

Subqueries
typically aren’t the most efficient way to retrieve data. Whenever possible, it
is suggested that you use joins rather than subqueries.

You
enclose subqueries within parentheses. Typically, you will use one of three
forms for your subqueries. The first form is to use a comparison operator in
the WHERE clause:

In
the second form, you use the IN keyword (or NOT IN) in the WHERE clause of the outside
query:

In
the third form, you use the EXISTS (or NOT EXISTS) keyword in the WHERE clause:

Subquery flow

SQL
Server supports noncorrelated and correlated subqueries.


In
a noncorrelated subquery, the inner query is independent and gets
evaluated first, then passes results to the outer query. A noncorrelated
(independent) subquery can be independently evaluated and relies only on its
own SELECT clause for instructions.


In
a correlated subquery, the outer query provides values for the dependent
inner subquery evaluation. SQL Server passes the subquery results back to the outer
query for evaluation. A correlated (dependent) subquery receives values from
the outer SELECT.

General subquery flow
is shown in the following diagram.

Subqueries that return a single value

You
can write a subquery that returns a single value by using the standard
comparison operators. SQL Server evaluates the nested subquery once. The syntax
for this form of subquery is:

SQL
Server compares the value returned from the subquery against the expression in the
WHERE clause. SQL Server generates an error if the statement returns more than one
value. Subqueries using comparison conditions often include an aggregate
function because an aggregate function returns a single value. An example of
this type of query is:

In
this example, the inner subquery locates the list price for specific product
and then passes this out to the outer query. The outer query uses the list
price to retrieve the product names for this list price. The outer statement is
a standard SELECT statement. All you have done is replaced part of the WHERE
expression with a subquery.




Array

No comments yet... Be the first to leave a reply!