SQL Subqueries: Scalar, IN, EXISTS and Correlated Subqueries
You need the forum users who have never posted a message. The query SELECT username FROM users WHERE id NOT IN (SELECT author_id FROM messages); runs without an error and returns Empty set, even though Dave has never written anything. The culprit is a single message with no author: its messages.author_id is NULL, and that one value makes NOT IN reject every row.
A subquery (a nested query, or inner query) is a SELECT statement written in parentheses inside another SQL statement. The outer query uses the subquery’s result as a single value, as a list of values for IN, as a check that rows exist (EXISTS) or as a temporary table in FROM. A subquery is either independent or correlated — that is, it refers to columns of the outer query.
What a Subquery Is and Where You Can Use It
A subquery can go almost anywhere an SQL statement expects a value, a list of values or a table: in the SELECT column list, in FROM, in WHERE and HAVING conditions, and inside INSERT, UPDATE and DELETE. What is allowed in each place depends on the shape of the subquery’s result.
| Kind of subquery | What it returns | Where it goes | Example |
|---|---|---|---|
| Scalar | One row with one column | The SELECT list, WHERE/HAVING with =, >, <, SET in UPDATE | WHERE views > (SELECT AVG(views) FROM topics) |
| Column | One column, any number of rows | IN, NOT IN, ANY, ALL | WHERE id IN (SELECT author_id FROM topics) |
| Row | One or more rows of several columns | Tuple comparison: (a, b) = (...), (a, b) IN (...) | WHERE (topic_id, id) IN (SELECT topic_id, MAX(id) FROM messages GROUP BY topic_id) |
| Table | Any set of rows and columns | FROM (derived table), EXISTS, INSERT ... SELECT | FROM (SELECT ...) AS t |
Apart from the shape of the result, subqueries are either independent or correlated. An independent subquery can run on its own: SELECT AVG(views) FROM topics returns the same number however many times you call it. A correlated subquery refers to the current row of the outer query (WHERE m2.topic_id = m.topic_id) and cannot run outside it — see Correlated Subqueries.
Inserting a query result into a table (INSERT ... SELECT) also uses a nested SELECT, but it is covered in INSERT ... SELECT: Inserting Query Results. From Java, queries with subqueries go through JDBC like any other SELECT, and JPQL allows subqueries in WHERE and HAVING.
Practice Data
All examples run in MySQL 8 on the forum practice database. Recreate it with the script from The Forum Practice Database, then run the block below: it adds the created_at and views columns to the threads table (as in the DML lesson), a user with no threads and no messages, a third thread and a message without an author.
ALTER TABLE topics
ADD COLUMN created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
ADD COLUMN views INT NOT NULL DEFAULT 0;
INSERT INTO users (username, email) VALUES ('Dave', '[email protected]');
INSERT INTO topics (title, author_id) VALUES ('Ice fishing', 1);
INSERT INTO messages (topic_id, author_id, body) VALUES
(3, 1, 'Is the ice thick enough yet?'),
(3, 3, 'Not yet, it is still thin.'),
(2, NULL, 'Selling a hybrid, almost new.');
UPDATE topics SET views = 120 WHERE id = 1;
UPDATE topics SET views = 45 WHERE id = 2;
UPDATE topics SET views = 30 WHERE id = 3; Message 7 without an author is a normal situation for this schema: messages.author_id allows NULL because deleting a user sets the author of their messages to NULL through a foreign key with ON DELETE SET NULL (see ON DELETE and ON UPDATE). The tables after the script:
SELECT id, title, author_id, views FROM topics;
SELECT id, topic_id, author_id FROM messages; +----+-------------+-----------+-------+
| id | title | author_id | views |
+----+-------------+-----------+-------+
| 1 | Fishing | 1 | 120 |
| 2 | Cycling | 2 | 45 |
| 3 | Ice fishing | 1 | 30 |
+----+-------------+-----------+-------+
+----+----------+-----------+
| id | topic_id | author_id |
+----+----------+-----------+
| 1 | 1 | 1 |
| 2 | 1 | 3 |
| 3 | 2 | 2 |
| 4 | 2 | 3 |
| 5 | 3 | 1 |
| 6 | 3 | 3 |
| 7 | 2 | NULL |
+----+----------+-----------+ Users: 1 Alice, 2 Bob, 3 Carol, 4 Dave. Thread 2 has the tags leisure, sport and shopping; thread 1 has one tag, leisure; thread 3 has none.
Scalar Subquery: a Single Value
A scalar subquery returns exactly one row with one column, and the outer query uses it like an ordinary value: compares against it in WHERE or prints it in the SELECT list. If the subquery returns more than one row, MySQL raises error 1242; if it returns no rows, its value is NULL.
Threads viewed more often than average. The average over three threads is (120 + 45 + 30) / 3 = 65:
SELECT id, title, views
FROM topics
WHERE views > (SELECT AVG(views) FROM topics); +----+---------+-------+
| id | title | views |
+----+---------+-------+
| 1 | Fishing | 120 |
+----+---------+-------+ Without a subquery you would first run SELECT AVG(views), remember 65 and paste it into a second query. You cannot write WHERE views > AVG(views): an aggregate function in WHERE gives error 1111 (the reason is explained in The SQL HAVING Clause).
In the SELECT list a scalar subquery is computed as one more column — for example, the difference from the average:
SELECT title, views,
views - (SELECT AVG(views) FROM topics) AS diff
FROM topics
ORDER BY id; +-------------+-------+----------+
| title | views | diff |
+-------------+-------+----------+
| Fishing | 120 | 55.0000 |
| Cycling | 45 | -20.0000 |
| Ice fishing | 30 | -35.0000 |
+-------------+-------+----------+ AVG of an integer column returns a DECIMAL with four digits after the point, which is why the difference is fractional too.
Error 1242: Subquery returns more than 1 row
Find the threads of the user who posted in thread 3:
SELECT title FROM topics
WHERE author_id = (SELECT author_id FROM messages WHERE topic_id = 3); ERROR 1242 (21000): Subquery returns more than 1 row Two people posted in thread 3, Alice and Carol, and = cannot compare one number with a list. The nasty part is that the same query works on other data: while thread 3 had one message, there was no error. There are three fixes, and each means something different: IN (threads of any of the authors — next section), an aggregate (SELECT MIN(author_id) ... — one specific author) or ORDER BY ... LIMIT 1 (the first one in a given order). LIMIT 1 without ORDER BY removes the error but returns an arbitrary row.
An Empty Subquery Gives NULL
If a scalar subquery finds no rows, there is no error — its value is NULL:
SELECT (SELECT id FROM users WHERE username = 'Frank') AS frank_id;
SELECT title FROM topics
WHERE author_id = (SELECT id FROM users WHERE username = 'Frank'); +----------+
| frank_id |
+----------+
| NULL |
+----------+
Empty set (0.00 sec) The second query does not tell you that there is no user Frank: author_id = NULL is never true, and the result is simply empty. A typo in the name looks exactly like “this user has no threads”. If you need a default value, wrap the subquery in COALESCE((SELECT ...), 0).
Subqueries with IN and NOT IN
value IN (SELECT column ...) is true if the value matches at least one row of the subquery. The subquery returns one column and any number of rows, and each row of the outer query appears in the result at most once, even if it has several matches. NOT IN is the reverse check, and it breaks when the subquery returns NULL.
Users who posted in the “Cycling” thread:
SELECT username
FROM users
WHERE id IN (SELECT author_id FROM messages WHERE topic_id = 2)
ORDER BY id; +----------+
| username |
+----------+
| Bob |
| Carol |
+----------+ The subquery returned 2, 3 and NULL (message 7). NULL does not hurt IN: Bob and Carol were found, and the others have nothing to match. The fixed query from the scalar section — WHERE author_id IN (SELECT author_id FROM messages WHERE topic_id = 3) — returns both of Alice’s threads, “Fishing” and “Ice fishing”: Carol has no threads.
NOT IN and NULL: Why the Result Is Empty
Back to the query from the top of the lesson:
SELECT username
FROM users
WHERE id NOT IN (SELECT author_id FROM messages); Empty set (0.00 sec) The subquery returned 1, 3, 2, 3, 1, 3 and NULL. For Dave the condition expands to 4 <> 1 AND 4 <> 3 AND ... AND 4 <> NULL; the last comparison is not true but NULL (UNKNOWN), and WHERE discards the row. How this works with a list of constants is shown in The IN Operator; with a subquery it is more dangerous, because the NULL comes from the data and can show up at any moment. Two reliable fixes:
-- 1. Filter out NULL in the subquery
SELECT username FROM users
WHERE id NOT IN (SELECT author_id FROM messages WHERE author_id IS NOT NULL);
-- 2. NOT EXISTS: NULL does not affect the result
SELECT username FROM users u
WHERE NOT EXISTS (SELECT 1 FROM messages m WHERE m.author_id = u.id); +----------+
| username |
+----------+
| Dave |
+----------+ Both queries return Dave. The second one is better: you do not have to remember whether the subquery column can contain NULL, and it will not break if the column becomes nullable tomorrow.
The query worked for months, then stopped
As long as no user has been deleted, messages.author_id holds no NULL, and NOT IN (SELECT author_id FROM messages) gives the right answer. The first deleted user (ON DELETE SET NULL) turns the result empty — no error, no change in the code. If the subquery column allows NULL, write NOT EXISTS.
MySQL does not allow LIMIT inside a subquery for IN: WHERE id IN (SELECT author_id FROM topics ORDER BY views DESC LIMIT 2) fails with ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'. The workaround is to wrap the subquery with LIMIT in a derived table (see Subquery in FROM): IN (SELECT author_id FROM (SELECT author_id FROM topics ORDER BY views DESC LIMIT 2) AS top).
ANY, SOME and ALL
The ANY operator (synonym SOME) and the ALL operator compare a value with every row of a column subquery using =, >, < or another comparison operator. > ANY is true if the condition holds for at least one row, > ALL — if it holds for all of them. In practice they are rare: IN, MIN or MAX is almost always clearer.
| Expression | Equivalent to | Empty subquery |
|---|---|---|
x = ANY (...) | x IN (...) | False |
x > ANY (...) | x > MIN(...), if the subquery has no NULL | False |
x <> ALL (...) | x NOT IN (...), with the same NULL problem | True |
x > ALL (...) | x > MAX(...), but only for a non-empty subquery | True |
Threads viewed more often than any of Bob’s threads (he has one, with 45 views):
SELECT title, views
FROM topics
WHERE views > ALL (SELECT views FROM topics WHERE author_id = 2); +---------+-------+
| title | views |
+---------+-------+
| Fishing | 120 |
+---------+-------+ And what if you substitute Dave, who has no threads?
SELECT title FROM topics
WHERE views > ALL (SELECT views FROM topics WHERE author_id = 4);
SELECT title FROM topics
WHERE views > (SELECT MAX(views) FROM topics WHERE author_id = 4); +-------------+
| title |
+-------------+
| Fishing |
| Cycling |
| Ice fishing |
+-------------+
Empty set (0.00 sec) ALL and MAX disagree on an empty set
“Greater than every element of an empty set” is true for any number by the rules of logic, so > ALL returned all three threads. MAX over an empty set is NULL, a comparison with it is not true, and the second query is empty. Before swapping one for the other during a refactoring, check whether the subquery can return nothing.
SQL EXISTS and NOT EXISTS
EXISTS (subquery) is true if the subquery returns at least one row and false if it returns none; NOT EXISTS is the opposite. The column values of the subquery are never checked, so by convention it is written as SELECT 1. Unlike IN and NOT IN, EXISTS is always true or false and never NULL.
A subquery in EXISTS is almost always correlated: it checks whether the current row of the outer query has related rows. Users who have at least one thread:
SELECT username
FROM users u
WHERE EXISTS (SELECT 1 FROM topics t WHERE t.author_id = u.id)
ORDER BY u.id; +----------+
| username |
+----------+
| Alice |
| Bob |
+----------+ Alice has two threads, but she appears once. This operation is called a semi-join: it takes the rows of the outer table that have at least one match, while the matches themselves do not get into the result. The opposite operation is an anti-join: NOT EXISTS keeps the rows that have no match. Threads without tags:
SELECT title
FROM topics t
WHERE NOT EXISTS (SELECT 1 FROM topic_tags tt WHERE tt.topic_id = t.id); +-------------+
| title |
+-------------+
| Ice fishing |
+-------------+ LEFT JOIN ... WHERE tt.topic_id IS NULL gives the same result — that technique is covered in LEFT JOIN with WHERE ... IS NULL. NOT EXISTS is shorter and says directly that you are checking for absence.
Whether you write SELECT 1, SELECT * or SELECT id inside makes no difference: MySQL ignores the column list in EXISTS and reads no extra data. SELECT 1 is a convention that tells the reader right away that the values do not matter.
Correlated Subqueries
A correlated subquery is a subquery that refers to columns of the outer query and is therefore logically re-evaluated for every row of the outer query. An independent subquery is computed once, while the result of a correlated one depends on the current row: for thread 1 it computes one thing, for thread 2 another.
The number of messages of each user — a correlated scalar subquery in the SELECT list:
SELECT u.username,
(SELECT COUNT(*) FROM messages m WHERE m.author_id = u.id) AS messages
FROM users u
ORDER BY u.id; +----------+----------+
| username | messages |
+----------+----------+
| Alice | 2 |
| Bob | 1 |
| Carol | 3 |
| Dave | 0 |
+----------+----------+ u.id inside the parentheses is a reference to the outer query. For Dave the subquery finds no rows, and COUNT(*) over an empty set is 0, so he is listed with zero instead of disappearing or getting NULL.
The Latest Message in Each Thread
The classic “last row in each group” task. The messages table has no date, so the latest message is the one with the highest id: AUTO_INCREMENT numbers grow as rows are inserted.
SELECT m.topic_id, m.id, m.body
FROM messages m
WHERE m.id = (SELECT MAX(m2.id)
FROM messages m2
WHERE m2.topic_id = m.topic_id)
ORDER BY m.topic_id; +----------+----+-----------------------------------------+
| topic_id | id | body |
+----------+----+-----------------------------------------+
| 1 | 2 | At the reservoir, early in the morning. |
| 2 | 7 | Selling a hybrid, almost new. |
| 3 | 6 | Not yet, it is still thin. |
+----------+----+-----------------------------------------+ The same table takes part in the query twice, so it has two aliases: m is the row of the outer query, m2 the rows of the subquery. Logically, the query walks through all seven messages:
| m.id | m.topic_id | Subquery: MAX(id) in this thread | m.id = result? |
|---|---|---|---|
| 1 | 1 | 2 | No |
| 2 | 1 | 2 | Yes |
| 3 | 2 | 7 | No |
| 4 | 2 | 7 | No |
| 5 | 3 | 6 | No |
| 6 | 3 | 6 | Yes |
| 7 | 2 | 7 | Yes |
A row subquery without correlation gives the same result: WHERE (m.topic_id, m.id) IN (SELECT topic_id, MAX(id) FROM messages GROUP BY topic_id) — the same technique answers “the row with the maximum in each group” in the GROUP BY lesson FAQ. MySQL 8.0+ also has the window function ROW_NUMBER() for such tasks.
Performance: What the MySQL 8 Optimizer Does
“Re-evaluated for every row” describes what the query means, not necessarily how it runs. The MySQL 8 optimizer rewrites many subqueries into joins: IN and EXISTS in WHERE become a semi-join (for EXISTS, since 8.0.16), NOT IN and NOT EXISTS become an anti-join (since 8.0.17). After such a rewrite the query is no slower than the equivalent JOIN.
A correlated scalar subquery in SELECT or in an = comparison, as in the two examples above, usually does run row by row. For seven messages you will not notice it; for a million rows an index on the linking column decides everything. Here m2.topic_id and m.author_id are foreign keys, and MySQL has already indexed them, so each subquery call reads a few rows rather than the whole table. To see what the optimizer chose, put EXPLAIN in front of the query: DEPENDENT SUBQUERY in the select_type column means row-by-row execution. Indexes and reading query plans deserve a lesson of their own.
Subquery in FROM: Derived Tables and WITH
A subquery in FROM is called a derived table: its result becomes a temporary table that the outer query selects from, filters and groups like an ordinary one. In MySQL a derived table must have an alias. The typical reason to use one is to compute an aggregate of an aggregate.
How many messages does an author write on average? You cannot write AVG(COUNT(*)) — nested aggregates give ERROR 1111 (HY000): Invalid use of group function. First count the messages per author (GROUP BY), then average the result:
SELECT AVG(cnt) AS avg_messages
FROM (SELECT author_id, COUNT(*) AS cnt
FROM messages
WHERE author_id IS NOT NULL
GROUP BY author_id) AS per_author; +--------------+
| avg_messages |
+--------------+
| 2.0000 |
+--------------+ The derived table per_author has three rows: Alice 2, Bob 1, Carol 3, and the average is 2. Dave is not in it: he has no messages, and GROUP BY over messages creates no groups for absent authors. Averaged over all four users, the answer would be 1.5 — which one is right depends on the question, not on SQL.
Without an alias the query does not run:
SELECT AVG(cnt)
FROM (SELECT author_id, COUNT(*) AS cnt FROM messages GROUP BY author_id); ERROR 1248 (42000): Every derived table must have its own alias WITH: Common Table Expressions (CTE)
Since MySQL 8.0 you can move the same subquery to the front and name it with WITH — a Common Table Expression (CTE). The query then reads top to bottom, and the named result can be used more than once. Users who wrote more messages than the average author:
WITH per_author AS (
SELECT author_id, COUNT(*) AS cnt
FROM messages
WHERE author_id IS NOT NULL
GROUP BY author_id
)
SELECT u.username, p.cnt
FROM per_author p
JOIN users u ON u.id = p.author_id
WHERE p.cnt > (SELECT AVG(cnt) FROM per_author); +----------+-----+
| username | cnt |
+----------+-----+
| Carol | 3 |
+----------+-----+ With derived tables you would have to write the same subquery twice: in FROM and inside the scalar subquery with the average. A CTE exists only within one statement. The recursive form WITH RECURSIVE walks hierarchies (a category tree, a chain of managers), but that is a separate topic. MySQL 5.7 has no WITH — only derived tables.
Subquery vs JOIN: Which One to Use
If you need columns from the second table, use a JOIN; if you only need to check whether related rows exist, use EXISTS or IN. Many tasks can be solved both ways, and MySQL 8 often builds the same plan for them, but the number of rows can differ: a JOIN repeats a row for every match, a subquery in WHERE does not.
SELECT username FROM users
WHERE id IN (SELECT author_id FROM topics)
ORDER BY id;
SELECT u.username
FROM users u
JOIN topics t ON t.author_id = u.id
ORDER BY t.id; +----------+
| username |
+----------+
| Alice |
| Bob |
+----------+
+----------+
| username |
+----------+
| Alice |
| Bob |
| Alice |
+----------+ Alice has two threads, and the JOIN returned her twice. To get the same answer as IN, you add DISTINCT to the JOIN — and that is extra work spent on finding duplicates. The kinds of joins are covered in SQL JOIN Types and UNION with Examples.
| Task | Subquery | JOIN | What to choose |
|---|---|---|---|
| Show a thread with its author’s name | A scalar subquery in SELECT for every row | JOIN users | JOIN: you need columns of both tables |
| Users who have threads | EXISTS / IN, each user once | JOIN + DISTINCT | Subquery: an existence check without duplicates |
| Users without messages | NOT EXISTS | LEFT JOIN ... IS NULL | Both work; NOT IN only if the subquery has no NULL |
| Comparison with an average or maximum | Scalar subquery | JOIN with a derived table | The subquery is shorter |
| Aggregate of an aggregate, reusing a result | Derived table or WITH | — | WITH, if the result is needed twice |
| Number of related rows for each row | Correlated COUNT(*), 0 for rows without a match | LEFT JOIN + GROUP BY + COUNT(column) | On large tables a JOIN with grouping is usually faster; check with EXPLAIN |
Subqueries in UPDATE and DELETE: Error 1093
In the WHERE clause of UPDATE and DELETE you write subqueries the same way as in SELECT. MySQL has one restriction: the subquery cannot read the table the statement is modifying — such a statement fails with error 1093. The workaround is a derived table that MySQL materializes in advance, or UPDATE ... JOIN.
A subquery on another table works without restrictions — remove the “shopping” tag from all threads:
DELETE FROM topic_tags
WHERE tag_id IN (SELECT id FROM tags WHERE name = 'shopping'); Query OK, 1 row affected (0.01 sec) Now add views to the threads that have fewer than average:
UPDATE topics
SET views = views + 10
WHERE views < (SELECT AVG(views) FROM topics); ERROR 1093 (HY000): You can't specify target table 'topics' for update in FROM clause MySQL refuses to read the table being modified in a subquery: otherwise the result would depend on how many rows had already been updated when the average was computed again. PostgreSQL and Oracle accept this statement — there the subquery sees the data as it was before the change. In MySQL you wrap the subquery in one more SELECT to make it a derived table:
UPDATE topics
SET views = views + 10
WHERE views < (SELECT avg_views
FROM (SELECT AVG(views) AS avg_views FROM topics) AS a); Query OK, 2 rows affected (0.01 sec)
Rows matched: 2 Changed: 2 Warnings: 0 The average of 65 was computed once, before the change, and threads 2 and 3 were updated: they now have 55 and 40 views. A multi-table UPDATE with a derived table in the JOIN gives the same result (do not run it right after the previous statement — the views would grow again):
UPDATE topics t
JOIN (SELECT AVG(views) AS avg_views FROM topics) AS a
ON t.views < a.avg_views
SET t.views = t.views + 10; How UPDATE with JOIN and safe update mode work is explained in SQL UPDATE: Changing Rows.
The wrapper helps only if the table is materialized
The workaround works because a derived table with an aggregate, DISTINCT, GROUP BY or LIMIT is computed into a temporary table before the modification starts. A plain wrapper such as (SELECT id FROM topics WHERE views < 50) AS x may be merged into the outer query by the MySQL 8 optimizer (derived_merge) — and error 1093 comes back. You can forbid the merge with the hint /*+ NO_MERGE(x) */ right after UPDATE or DELETE.
Where Subqueries Go Wrong
The worst subquery mistakes produce no error message: NOT IN with NULL, an empty scalar subquery and an unqualified column name all return a plausible but wrong result. MySQL rejects the rest with a clear error code — they are collected in the table together with the fixes.
| Situation | What MySQL 8 does | How to fix it |
|---|---|---|
NOT IN (SELECT ...) and the subquery contains NULL | Empty set without an error | NOT EXISTS or WHERE column IS NOT NULL in the subquery |
| A scalar subquery returned several rows | ERROR 1242: Subquery returns more than 1 row | IN, an aggregate (MAX, MIN) or ORDER BY ... LIMIT 1 |
| A scalar subquery found no rows | NULL: the comparison is not true, rows silently disappear | COALESCE((SELECT ...), value) or a data check |
| A derived table without an alias | ERROR 1248: Every derived table must have its own alias | FROM (SELECT ...) AS t |
The subquery reads the table that UPDATE/DELETE modifies | ERROR 1093: You can't specify target table ... for update in FROM clause | A materialized derived table or UPDATE ... JOIN |
LIMIT inside IN/ANY/ALL | ERROR 1235: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' | Wrap the subquery with LIMIT in a derived table |
AVG(COUNT(*)) instead of a subquery | ERROR 1111: Invalid use of group function | A derived table or WITH |
| A column in the subquery without a table name | If the subquery’s table has no such column, MySQL takes it from the outer query — without an error | Always qualify columns with an alias: tt.topic_id |
What Will This Query Print?
You need the messages from threads tagged “sport”. The table got mixed up: instead of the junction table topic_tags, the subquery reads tags, which has no topic_id column.
SELECT id, topic_id
FROM messages
WHERE topic_id IN (SELECT topic_id FROM tags WHERE name = 'sport')
ORDER BY id; +----+----------+
| id | topic_id |
+----+----------+
| 1 | 1 |
| 2 | 1 |
| 3 | 2 |
| 4 | 2 |
| 5 | 3 |
| 6 | 3 |
| 7 | 2 |
+----+----------+ No error, and all seven messages came back. Not finding topic_id in tags, MySQL followed the SQL name-resolution rules and took the column from the outer query — messages.topic_id. The subquery became correlated: for each message it returns that message’s own topic_id (once, because there is one “sport” tag), and topic_id IN (topic_id) is always true. With an alias the mistake shows up at once:
SELECT id, topic_id
FROM messages
WHERE topic_id IN (SELECT g.topic_id FROM tags g WHERE g.name = 'sport'); ERROR 1054 (42S22): Unknown column 'g.topic_id' in 'field list' The correct query goes through the junction table and returns the three messages of the “Cycling” thread:
SELECT id, topic_id
FROM messages
WHERE topic_id IN (SELECT tt.topic_id
FROM topic_tags tt
JOIN tags g ON g.id = tt.tag_id
WHERE g.name = 'sport')
ORDER BY id; +----+----------+
| id | topic_id |
+----+----------+
| 3 | 2 |
| 4 | 2 |
| 7 | 2 |
+----+----------+ Frequently Asked Questions
Are subqueries always slower than JOINs?
No, that advice dates back to MySQL 5.5, where IN (SELECT ...) was often re-executed for every row of the outer table. Since MySQL 5.6 the optimizer turns IN into a semi-join, since 8.0.16 it does the same for EXISTS, and since 8.0.17 NOT IN and NOT EXISTS run as an anti-join. What stays slow is a correlated scalar subquery on a large table without an index on the linking column. Compare the variants with EXPLAIN, not by a rule of thumb.
Can I use subqueries in JPQL and Spring Data JPA?
Yes. The JPQL specification allows subqueries in WHERE and HAVING: with IN, EXISTS, ALL, ANY/SOME and as a scalar value in a comparison. The subquery is written against entities, not tables: select u from User u where exists (select 1 from Topic t where t.author = u). HQL in Hibernate 6 also allows subqueries in SELECT and FROM. In Spring Data you put such a query in @Query.
Can I use ORDER BY and LIMIT inside a subquery?
ORDER BY without LIMIT in a subquery is pointless: the row order of a derived table or an IN list is not preserved, and the optimizer may drop it. Sort the result of the outer query instead. ORDER BY ... LIMIT works in a scalar subquery and in a derived table, while inside IN, ANY and ALL MySQL raises error 1235 — the workaround is in the section Subqueries with IN and NOT IN.
What is the difference between a CTE, a view and a temporary table?
A CTE (WITH) exists only inside one statement and is not stored anywhere. A view (CREATE VIEW) is a query saved in the database; every session can use it until it is dropped. A temporary table (CREATE TEMPORARY TABLE) holds data until the end of the session, so you can fill it once and read it in many queries. To break one complex query into steps, a CTE is the right tool.
Which should I use: IN or EXISTS?
For a positive check the result is the same, and MySQL 8 usually runs both as a semi-join: IN is convenient with a list of values from an independent subquery, EXISTS when the linking condition involves more than one column. For a negative check choose NOT EXISTS: NOT IN returns an empty result if the subquery contains NULL. The example is in NOT IN and NULL.
Comments