Databricks SQL Subqueries: Examples and Best Practices

If you write SQL in Databricks, you are eventually going to need a subquery. Whether you are trying to filter data based on another table, calculate a moving average, or just organize a massive query into readable chunks, subqueries are an essential tool for any data engineer.

In this guide, we will break down exactly how subqueries work in Databricks SQL, look at the specific types supported, and walk through practical examples you can use right away.

Page Content

Introduction

What is a Subquery in Databricks?

Types of subqueries in Databricks and Examples

Table Subquery

Scalar Subquery Expressions

Subquery in WHERE clause

Correlated Subquery

Nested subqueries in Databricks

Conclusion

Introduction

A subquery in Databricks SQL is a SELECT expression enclosed in parentheses, acting as a nested query block within a query statement. Similar to subqueries in other relational databases, a subquery in Databricks SQL can return zero, one, or multiple values to the main SELECT statement.

What is a Subquery in Databricks?

At its core, a subquery is just a query nested inside another query. You wrap a standard SELECT statement in parentheses and use it as a building block for your main query (often called the “outer” query).

You can drop these nested queries into several different Databricks statements, including:

SELECT
CREATE TABLE AS
INSERT INTO

Think of a subquery as a way to calculate a value or create a temporary dataset on the fly, which your main query can then use to filter, compare, or display data.

Think of a subquery as a way to calculate a value or create a temporary table on the fly

Types of Subqueries in Databricks (With Examples)

While Databricks SQL is powerful, it handles subqueries slightly differently than legacy systems like Oracle or Teradata. Here are the main types of subqueries you can use in Databricks, complete with examples.

Table SubQuery

A table subquery sits in the FROM clause. Instead of querying a physical table, you query the results of the subquery as if it were its own table.

Example: Let’s say you want to find the total spending of customers who have placed more than five orders.

SELECT 
    customer_id, 
    total_amount 
FROM (
    SELECT 
        customer_id, 
        SUM(order_amount) AS total_amount 
    FROM orders 
    GROUP BY customer_id 
    HAVING COUNT(order_id) > 5
) AS high_value_customers;

Scalar Subquery Expressions

A scalar subquery is designed to return exactly one row and one column a single value. If the subquery finds no data, it returns NULL. If it accidentally returns more than one row, Databricks will throw an error.

Example: Fetching the absolute maximum value from one table to display alongside data from another.

SELECT 
    employee_name, 
    salary,
    (SELECT MAX(salary) FROM employees) AS highest_company_salary 
FROM departments;

The scalar subquery returns a single row and one column. If the subquery returns more than one row, Databricks SQL will generate an error, ‘Scalar subquery must return only one column‘.

Subquery in WHERE Clause

This is arguably the most common way to use a subquery. You use it in the WHERE clause to filter the results of your main query based on data found in another table. You will usually pair this with operators like IN, NOT IN, EXISTS, or NOT EXISTS.

Example: Finding all employees who work in a specific location by looking up the department IDs.

SELECT employee_name 
FROM employees 
WHERE department_id IN (
    SELECT department_id 
    FROM departments 
    WHERE location = 'New York'
);

Correlated Subquery

A correlated subquery is a bit different: it actually references a column from the outer query. Because it relies on the outer query, the database has to evaluate the subquery row-by-row.

Example: Finding employees who make more than the average salary within their specific department.

SELECT e1.employee_name, e1.salary 
FROM employees e1
WHERE e1.salary > (
    SELECT AVG(salary) 
    FROM employees e2 
    WHERE e1.department_id = e2.department_id
);

Nested subqueries in Databricks

You don’t have to stop at one level. Databricks allows you to nest subqueries inside other subqueries. While this is incredibly powerful, be careful—nesting too deeply can make your code hard to read and slow to execute.

Best Practice: If you find yourself nesting three or four levels deep, consider using Common Table Expressions (CTEs) instead to keep your code clean and manageable.

Conclusion

Writing Databricks SQL subqueries is a fast track to writing more efficient and powerful data transformations. Whether you are using a simple scalar query to grab a single metric, or a correlated subquery for complex row-by-row comparisons, these tools give you the flexibility to handle almost any logic you throw at them.

Just remember to keep an eye on performance, and switch to CTEs if your subqueries start getting too crowded!