3.6

Subqueries

  • Definition: A subquery is a query within another SQL query (outer query). It is used in the SELECT statement's WHERE clause to return data to restrict the selected results.

  • Placement: Subqueries can be placed inside the WHERE clause.

Correlated Subqueries

  • Definition: A correlated subquery is a subquery that references a column from the outer query, meaning it relies on the outer query's data.

  • Functionality: Each row processed by the outer query can affect the rows selected by the subquery.

  • Aliases: Use of AS keyword allows creating aliases for improving query clarity (e.g., SELECT NAME AS n FROM COUNTRY AS C).

Example of Correlated Subquery

  • SQL Statement:

    SELECT Name, CountryCode, Population
FROM City C
WHERE Population >
(SELECT AVG(Population) FROM City WHERE CountryCode = C.CountryCode);

  • Explanation: The outer query selects from the City table, using an alias; the subquery calculates the average population for each country's cities.

  • Process:

    1. The outer query processes each city.

    2. The subquery calculates the average population for cities in the same country.

    3. The outer query checks if the city's population exceeds that average to determine selection.

EXISTS and NOT EXISTS Operators

  • EXISTS Operator: Returns TRUE if the subquery returns any rows.

  • NOT EXISTS Operator: Returns TRUE if the subquery returns no rows.

  • Example: To find cities in countries where a language is spoken by over 97% of the population:

    SELECT Name, CountryCode
FROM City C
WHERE EXISTS
(SELECT * FROM CountryLanguage
WHERE CountryCode = C.CountryCode AND Percentage > 97);

    • Only cities from Brazil are selected as Argentina has no languages with over 97% speakers.

Using Family and Employee Tables

  • Query 1: To return employees with a spouse:

    SELECT Name
FROM Employee E
WHERE EXISTS
(SELECT * FROM Family WHERE Id = E.Id AND Relationship = 'Spouse');

  • Query 2: To return employees without a spouse:

    SELECT Name
FROM Employee E
WHERE NOT EXISTS
(SELECT * FROM Family WHERE Id = E.Id AND Relationship = 'Spouse');

  • Output: Only Sam Snead is returned since he has no spouse.

Flattening Subqueries

  • Definition: Flattening a subquery involves converting it into a join, which can improve performance and readability.

  • Steps to Flatten:

    1. Retain the outer query's SELECT and ORDER BY clauses.

    2. Add INNER JOIN clauses for each subquery table.

    3. Shift any subquery conditions into the WHERE clause of the main query.

    4. Remove any duplicate rows if necessary.

Final Example of Query Conversion

  • Subquery Example:

    SELECT Name FROM Country WHERE Code IN
(SELECT CountryCode FROM City WHERE Population > 1000000);

  • Flattened Join Example:

    SELECT DISTINCT Name
FROM Country INNER JOIN City ON Code = CountryCode
WHERE Population > 1000000;

  • Outcome: Both queries yield the same results, with the join query using DISTINCT to eliminate duplicates.