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.
]]>