SQL 2005+

SQL Server Subqueries (2)

The thirtieth part of the SQL Server Programming Fundamentals tutorial examines subqueries. These can be nested queries allowing complex selections that are not possible using joins. They can also be used to update rows using the contents of other tables.

Standard Subqueries

Let's start by running a simple query containing a standard subquery. The query below retrieves a list of all contracts within the JoBS database. The third column in the results is generated by the subquery, which retrieves the total value of all known contracts. This query allows the value of each contract to be compared to the total value. Note the requirement for the subquery to be contained within parentheses (). In this case a name has also been provided for the subquery's column.

SELECT
    ContractNumber,
    ContractValue,
    (SELECT sum(ContractValue) FROM Contracts) AS AllContractsTotal
FROM
    Contracts

In the above statement the query contains a standard subquery. The result of the subquery need only be retrieved once because it is the same for every row in the result set.

The resultant value of a subquery can be used in much the same way as any other value. For example, if we wished to determine the percentage value that each contract contributes towards the company's income, we can use the subquery within a calculation, as in the next query. Again, the result of the subquery does not vary from row to row, even though the result of the calculation does.

SELECT
    ContractNumber,
    ContractValue,
    100 * ContractValue / (SELECT sum(ContractValue) FROM Contracts) AS Percentage
FROM
    Contracts

Subqueries can be used in places other than the column list for a query. In the next example the subquery is used in the WHERE clause. In this case only contract rows that contribute at least 3% of the total contract income are retrieved:

SELECT
    ContractNumber,
    ContractValue,
    100 * ContractValue / (SELECT sum(ContractValue) FROM Contracts) AS Percentage
FROM
    Contracts
WHERE
    ContractValue / (SELECT sum(ContractValue) FROM Contracts) >= 0.03

Correlated Subqueries

Correlated subqueries use information from the main query when retrieving or calculating their values. This leads to the subquery being executed multiple times, as the results can differ from row to row. This does lower the performance of the statement but can lead to very powerful queries that may be difficult or even impossible to implement using joins.

Try executing the following statement that includes two correlated subqueries:

SELECT
    EngineerId,
    EngineerName,
    (SELECT count(*) FROM Jobs WHERE EngineerId = E.EngineerId) AS Jobs,
    (SELECT sum(Duration) * E.HourlyRate FROM Jobs WHERE EngineerId = E.EngineerId)
        AS Cost
FROM
    Engineers E

In the above sample, the main part of the query obtains the full list of engineers, retrieving the ID and name for each row. Note that the Engineers table in the FROM clause has been given the alias, "E". The third column returned by the query is generated by a subquery. In this case the subquery retrieves the number of jobs that have been undertaken by the engineer. The link between the two queries is determined by the WHERE clause of the subquery, which compares the EngineerId from the Jobs table with the EngineerId of the parent query, using the "E" alias.

The fourth column in the query is also generated by a subquery. In this case, the total duration of all of the jobs undertaken by each engineer is multiplied by the engineer's hourly rate to determine a total cost. Again, the "E" alias is used to access the columns of the outer table.

As with standard subqueries, correlated subqueries may also be used within the WHERE clause of a query. The following example adds a WHERE clause that filters the results so that only engineers that have attended jobs are listed.

SELECT
    EngineerId,
    EngineerName,
    (SELECT count(*) FROM Jobs WHERE EngineerId = E.EngineerId) AS Jobs,
    (SELECT sum(Duration) * E.HourlyRate FROM Jobs WHERE EngineerId = E.EngineerId)
        AS Cost
FROM
    Engineers E
WHERE
    (SELECT count(*) FROM Jobs WHERE EngineerId = E.EngineerId) > 0