SQL 2005+

SQL Server Subqueries

by Richard Carr, published at http://www.blackwasp.co.uk/SQLSubqueries.aspx

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.

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

Using a Subquery to Check if a Row Exists

Some Transact-SQL (T-SQL) commands are designed specifically to work with subqueries. One commonly used function, named "EXISTS", allows the creation of a predicate that ideal for use in a WHERE clause. The function returns true if the subquery passed to it as a parameter returns one or more rows. If the subquery does not generate any results, the function returns false.

In the previous sample, the subquery in the WHERE clause returned the number of jobs for each engineer. If this number was greater than zero, the row was included. It would be more appropriate to use EXISTS for such a query. Below is a similar query that returns a list of engineers that have jobs.

SELECT
    EngineerId,
    EngineerName
FROM
    Engineers E
WHERE
    EXISTS(SELECT * FROM Jobs WHERE EngineerId = E.EngineerId)

The EXISTS command can be used in reverse by prefixing a NOT clause. The next query returns the engineers who have not yet been assigned any jobs:

SELECT
    EngineerId,
    EngineerName
FROM
    Engineers E
WHERE
    NOT EXISTS(SELECT * FROM Jobs WHERE EngineerId = E.EngineerId)

Using IN

A second T-SQL command that uses a subquery is "IN". The subquery provided must return rows with only one column. You can then use the clause to determine whether the value to the left of the IN appears in any of the rows in the subquery to the right.

Consider the following query. The subquery here returns a list of EngineerId values that is filtered so that only IDs linked to the "GAS" skill are included. The main query lists the engineers with IDs that appear in the results of the subquery. Overall, the query returns all of the engineers that have the "GAS" skill. NB: This query could, and indeed probably should, be created using an inner join.

SELECT
    EngineerId,
    EngineerName
FROM
    Engineers E
WHERE
    E.EngineerId IN (SELECT EngineerId FROM EngineerSkills WHERE SkillCode = 'GAS')

You can also retrieve all of the engineers that do not have the "GAS" skill by using NOT IN.

SELECT
    EngineerId,
    EngineerName
FROM
    Engineers E
WHERE
    E.EngineerId NOT IN
    (SELECT EngineerId FROM EngineerSkills WHERE SkillCode = 'GAS')

Using a Subquery to Set Values During an Update

Subqueries are most commonly used within SELECT statements. However, they can be used elsewhere. In the final example below, two subqueries are used within an UPDATE to determine the new values for the rows being modified. In this case, the hourly rate and overtime rate for all engineers are being modified. The new rates are based upon the number of recognised skills for each engineer.

UPDATE Engineers
SET
    HourlyRate = 10 + 5 *
    (SELECT count(*) FROM EngineerSkills WHERE EngineerId = Engineers.EngineerId),
    OvertimeRate = 15 + 7.5 *
    (SELECT count(*) FROM EngineerSkills WHERE EngineerId = Engineers.EngineerId)