A subquery is a query nested inside another SQL statement that returns a result set used by the outer query.
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.
MariaDB supports inline subqueries, correlated subqueries, and EXISTS/NOT EXISTS subqueries. Pick the type that matches your data volume and indexing strategy.
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.
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.
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.
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.
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
.
Returning multiple columns where only one is expected and forgetting to correlate subqueries, causing full-table scans.
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.
Non-correlated subqueries may be materialized and cached during execution. Correlated subqueries run per outer row unless the optimizer rewrites them.
CTEs improve readability; performance is comparable to inline subqueries unless marked as MATERIALIZED. Benchmark both.
Yes, but readability and performance decline quickly. Test each extra layer with EXPLAIN.
IN loads the entire subquery set before filtering. Switch to EXISTS or add indexes on subquery output columns.
Absolutely. Use INSERT ... SELECT to copy derived data into another table.