Types of Subqueries in SQL

By /

Subqueries are a powerful feature in SQL that allow you to nest one query inside another. Understanding the different types of subqueries is essential for writing efficient and readable code, especially in healthcare analytics.

1. Types of Subqueries by Result Set

  • Scalar Subquery
    Returns one column and one row (a single value). Commonly used in WHERE, SELECT, or HAVING clauses.

Healthcare Example:

SELECT MemberID, RAF_Score
FROM Risk_Adjustment_Members
WHERE RAF_Score > 
    (SELECT AVG(RAF_Score) 
     FROM Risk_Adjustment_Members 
     WHERE Year = 2025);

Finds members whose Risk Adjustment Factor (RAF) score is above the yearly average.

  • Row Subquery
    Returns one row with multiple columns.
  • Table Subquery
    Returns multiple rows and multiple columns. Usually placed in the FROM clause.

2. Types of Subqueries by Execution

  • Nested (Non-correlated) Subquery
    The inner query runs first and independently. Its result is passed to the outer query.
  • Correlated Subquery
    The inner query references a value from the outer query. It runs once for each row of the outer query.

Healthcare Example (Correlated):

SELECT ProviderName,
       (SELECT COUNT(*) 
        FROM Claims C 
        WHERE C.ProviderID = P.ProviderID 
          AND C.Status = 'Denied') AS Denied_Claims
FROM Providers P;

Shows how many claims each provider had denied.

3. Special Predicate Keywords

These keywords are frequently used with subqueries:

  • IN – Checks if a value exists in the list returned by the subquery.
  • EXISTS – Checks whether the subquery returns any rows (often more efficient).
  • ALL / SOME / ANY – Used for comparisons with all or any values.

Healthcare Example with EXISTS:

SELECT MemberID, FullName
FROM Members M
WHERE EXISTS 
    (SELECT 1 
     FROM Risk_Adjustment_Conditions R 
     WHERE R.MemberID = M.MemberID 
       AND R.HCC_Code IN ('HCC001', 'HCC002'));

Finds members who have at least one of the specified high-impact HCC conditions.

Key Takeaways

  • Scalar subqueries return a single value — ideal for comparisons.
  • Correlated subqueries are powerful but can be slower on large tables.
  • Use EXISTS instead of IN when checking for existence in large healthcare datasets.
  • Well-written subqueries are very useful for risk scoring, claims validation, and provider performance analysis.
Scroll to Top