Impara Subqueries and EXPLAIN | Advanced Query Plan Interpretation

Subqueries are queries nested within another SQL query, often used for filtering, calculating, or transforming data in a single statement. When you use EXPLAIN on queries that include subqueries, the output reveals how the database executes both the outer query and the inner subquery. Understanding how subqueries are represented in the EXPLAIN output is crucial for interpreting and optimizing complex queries.


              123456789
            
-- EXPLAIN output for a query using a subquery in the WHERE clause
EXPLAIN
SELECT title, published_year
FROM books
WHERE author_id IN (
    SELECT author_id
    FROM authors
    WHERE country = 'United Kingdom'
);

Subqueries can be classified as either correlated or uncorrelated, and this distinction has a direct impact on the query plan. An uncorrelated subquery is independent of the outer query—it can be executed once, and its result reused. In the previous example, the subquery inside the WHERE clause does not reference columns from the outer query, so it is uncorrelated. The EXPLAIN output shows the subquery as a separate step, often labeled as SUBQUERY in the select_type column.

A correlated subquery, on the other hand, references columns from the outer query. This means the subquery must be evaluated once for each row processed by the outer query, which can significantly affect performance. The EXPLAIN output for correlated subqueries typically shows a dependency between the subquery and the outer query, often resulting in the subquery being executed multiple times.


              12345678910
            
-- EXPLAIN output for a correlated subquery
EXPLAIN
SELECT title
FROM books b
WHERE EXISTS (
    SELECT 1
    FROM loans l
    WHERE l.book_id = b.book_id
      AND l.return_date IS NULL
);

1. What is the difference between a correlated and an uncorrelated subquery?

2. How can subqueries impact query performance?

Tutto è chiaro?

Grazie per i tuoi commenti!

Sezione 2. Capitolo 3

Chieda ad AI

Chieda pure quello che desidera o provi una delle domande suggerite per iniziare la nostra conversazione

Suggested prompts:

Can you explain the difference between correlated and uncorrelated subqueries with more examples?

How does the `EXPLAIN` output help in optimizing queries with subqueries?

What are some best practices for improving the performance of queries that use subqueries?

Scorri per mostrare il menu


              123456789
            
-- EXPLAIN output for a query using a subquery in the WHERE clause
EXPLAIN
SELECT title, published_year
FROM books
WHERE author_id IN (
    SELECT author_id
    FROM authors
    WHERE country = 'United Kingdom'
);


              12345678910
            
-- EXPLAIN output for a correlated subquery
EXPLAIN
SELECT title
FROM books b
WHERE EXISTS (
    SELECT 1
    FROM loans l
    WHERE l.book_id = b.book_id
      AND l.return_date IS NULL
);

1. What is the difference between a correlated and an uncorrelated subquery?

2. How can subqueries impact query performance?

Tutto è chiaro?

Grazie per i tuoi commenti!

Sezione 2. Capitolo 3