Skip to content
Tutorials Center
My learning

Topics

Lesson 18 of 40 ยท Joins and Subqueries

Subqueries in MariaDB

Nest queries in MariaDB: scalar, row and table subqueries, IN, ANY, ALL, EXISTS, correlated subqueries, derived tables in FROM, CTEs, and when a join is the better choice.

A subquery is a SELECT inside another statement. It lets you answer a question in two steps without creating a temporary table: "find the average price, then list products above it". MariaDB allows subqueries in the SELECT list, FROM, WHERE, HAVING and in INSERT/UPDATE/DELETE. They are always written in parentheses.

Scalar subqueries: one value

A subquery that returns one row with one column can be used wherever a single value fits:

SQL Products above the average price ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

If a scalar subquery returns more than one row, MariaDB raises error 1242 ("Subquery returns more than 1 row"). If it returns no rows, the value is NULL.

You can also place one in the SELECT list to show a comparison next to each row:

SQL Difference from the average ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

IN and NOT IN: a list of values

When a subquery returns one column and many rows, test membership with IN:

SQL Customers who ordered something ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

NOT IN has a well-known trap: if the subquery returns even one NULL, x NOT IN (...) is never true, and you get an empty result. Use NOT EXISTS instead, or filter the NULLs inside the subquery (WHERE col IS NOT NULL).

SQL The NOT IN and NULL trap

Press Run (or Ctrl + Enter) to see the result here.

ANY, SOME and ALL

Compare a value with a whole set using a comparison operator:

  • > ANY (subquery): greater than at least one value (SOME is a synonym).
  • > ALL (subquery): greater than every value.
  • = ANY is the same as IN.
SQL
SELECT name FROM products WHERE price > ALL (SELECT price FROM products WHERE category_id = 3);

SQLite lacks ALL and ANY; the equivalent uses MAX and MIN, as in the stand-in below, which finds products dearer than every product in category 3:

SQL The ALL idea with MAX ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

EXISTS: does a matching row exist?

EXISTS (subquery) is true when the subquery returns at least one row. What the subquery selects does not matter (SELECT 1 is the convention), and the server can stop at the first match, making it fast for large tables.

SQL Products that were ordered at least once ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Correlated subqueries

An ordinary subquery runs once. A correlated subquery refers to a column of the outer query, so it is logically evaluated for each outer row. EXISTS above is correlated. Another example: employees paid above their own department's average:

SQL Above your department's average ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Correlated subqueries are readable, but they can be slow on large tables when the optimizer cannot turn them into a join. MariaDB transforms many IN/EXISTS subqueries into semi-joins automatically; when in doubt, compare with EXPLAIN (covered in the performance chapter) or rewrite with a join or a derived table.

Derived tables: a subquery in FROM

A subquery in FROM acts as a temporary table. MariaDB requires it to have an alias:

SQL Average of per-customer totals ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

You cannot do this with one query level, because AVG(COUNT(*)) is not allowed. Derived tables are also the fix for double aggregation caused by joins: aggregate first, then join the small result.

SQL Aggregate first, then join ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Subqueries in INSERT, UPDATE and DELETE

SQL
INSERT INTO vip_customers (customer_id)
SELECT customer_id FROM orders GROUP BY customer_id HAVING COUNT(*) >= 5;

UPDATE products SET stock = 0
WHERE id NOT IN (SELECT product_id FROM order_items WHERE product_id IS NOT NULL);

A MariaDB-specific limit: you cannot modify a table and select from the same table in a subquery of that statement (ERROR 1093) with the plain form. Wrap the inner query in a derived table (SELECT * FROM (SELECT ...) AS x) or use a join. MariaDB 10.3.2 and later relax this for DELETE, and since 11.x also allow UPDATE in some forms.

Subquery, join or CTE?

  • A join is usually the most direct when you want columns from both tables.
  • EXISTS / NOT EXISTS express "is there a match" without duplicating rows.
  • A derived table or WITH clause (CTE) names an intermediate result, making long queries readable. A CTE is just a named subquery:
SQL The same query with a CTE ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What happens if a scalar subquery returns two rows?

    A scalar subquery must produce at most one row and one column.
  2. 2. Why is NOT IN dangerous when the subquery may contain NULL?

    x NOT IN (1, NULL) evaluates to NULL or false for every x. Use NOT EXISTS or filter the NULLs.
  3. 3. What is a correlated subquery?

    It depends on the current outer row, so it is logically re-evaluated per row (or rewritten by the optimizer).
  4. 4. What does a subquery in FROM require in MariaDB?

    Every derived table must have its own alias.
  5. 5. Which operator checks only whether at least one matching row exists?

    EXISTS stops at the first match and ignores the selected columns.

Summary

  • Scalar subqueries return one value; IN, ANY and ALL compare against a list; EXISTS tests for a match.
  • Correlated subqueries refer to the outer row; the optimizer often rewrites them into semi-joins.
  • Derived tables (aliased subqueries in FROM) and CTEs name intermediate results.
  • Beware of NOT IN with NULL and of selecting from the table you are modifying.

Next: Views