How to Use Subqueries in MariaDB

Galaxy Glossary

How do I write and optimize subqueries in MariaDB?

A subquery is a query nested inside another SQL statement that returns a result set used by the outer query.

Sign up for the latest in SQL knowledge from the Galaxy Team!
Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)
Oops! Something went wrong while submitting the form.

Description

Table of Contents

What problems do MariaDB subqueries solve?

Subqueries let you filter, compute, or derive data inside a parent query without creating temporary tables. They are perfect for one-off lookups, conditional filtering, and aggregation on the fly.

Which subquery types exist?

MariaDB supports inline subqueries, correlated subqueries, and EXISTS/NOT EXISTS subqueries. Pick the type that matches your data volume and indexing strategy.

When should I choose a subquery over JOIN?

Use subqueries when the nested logic depends on aggregation or when readability is higher than with multiple JOINs. For large datasets, test both approaches for speed.

How do I write a basic SELECT subquery?

SELECT name, email
FROM Customers
WHERE id IN (SELECT customer_id FROM Orders WHERE total_amount > 500);

This subquery first finds high-value Orders, then filters Customers.

How do correlated subqueries work?

SELECT c.id, c.name,
(SELECT COUNT(*)
FROM Orders o
WHERE o.customer_id = c.id) AS order_count
FROM Customers c;

The inner query references the outer alias c on every row, recalculating counts per customer.

How can EXISTS improve performance?

SELECT *
FROM Products p
WHERE EXISTS (SELECT 1 FROM OrderItems oi WHERE oi.product_id = p.id);

EXISTS stops scanning once it finds one matching row, often beating IN for large sub-results.

Best practices for fast subqueries?

1) Index columns used for linkage. 2) Avoid SELECT * inside subqueries; return only needed columns. 3) Prefer EXISTS over IN for big result sets. 4) Analyze using EXPLAIN.

What are common mistakes to avoid?

Returning multiple columns where only one is expected and forgetting to correlate subqueries, causing full-table scans.

Can I update with a subquery?

Yes. Example: UPDATE Products SET stock = stock - (SELECT quantity FROM OrderItems WHERE product_id = Products.id). Ensure the subquery returns one row per outer row.

FAQs

Does MariaDB cache subqueries?

Non-correlated subqueries may be materialized and cached during execution. Correlated subqueries run per outer row unless the optimizer rewrites them.

Are CTEs faster than subqueries?

CTEs improve readability; performance is comparable to inline subqueries unless marked as MATERIALIZED. Benchmark both.

Why How to Use Subqueries in MariaDB is important

How to Use Subqueries in MariaDB Example Usage


-- List customers who bought out-of-stock products
SELECT DISTINCT c.name, c.email
FROM Customers c
JOIN Orders o  ON o.customer_id = c.id
JOIN OrderItems oi ON oi.order_id = o.id
WHERE oi.product_id IN (
    SELECT id FROM Products WHERE stock = 0
);

How to Use Subqueries in MariaDB Syntax


-- Inline subquery
SELECT name, email
FROM Customers
WHERE id IN (
    SELECT customer_id
    FROM Orders
    WHERE order_date >= '2024-01-01'
);

-- Correlated subquery in SELECT list
SELECT c.id, c.name,
       (SELECT SUM(total_amount)
        FROM Orders o
        WHERE o.customer_id = c.id) AS lifetime_value
FROM Customers c;

-- EXISTS subquery
SELECT p.name, p.price
FROM Products p
WHERE EXISTS (
    SELECT 1
    FROM OrderItems oi
    WHERE oi.product_id = p.id
);

-- Subquery in UPDATE
UPDATE Products p
SET stock = stock - (
    SELECT SUM(oi.quantity)
    FROM OrderItems oi
    WHERE oi.product_id = p.id
)
WHERE p.id = 42;

Common Mistakes

Frequently Asked Questions (FAQs)

Can I nest subqueries more than two levels deep?

Yes, but readability and performance decline quickly. Test each extra layer with EXPLAIN.

Why does my IN subquery run slowly?

IN loads the entire subquery set before filtering. Switch to EXISTS or add indexes on subquery output columns.

Do subqueries work inside INSERT statements?

Absolutely. Use INSERT ... SELECT to copy derived data into another table.

Want to learn about other SQL terms?

Trusted by top engineers on high-velocity teams
Aryeo Logo
Assort Health
Curri
Rubie Logo
Bauhealth Logo
Truvideo Logo
Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)
Oops! Something went wrong while submitting the form.