Scalar, IN, EXISTS, and derived tables—optimizer may materialize subqueries; JOIN rewrite is sometimes faster.
IN and EXISTS
SELECT * FROM products
WHERE id IN (SELECT product_id FROM order_lines WHERE qty >= 10);
SELECT c.* FROM customers c
WHERE EXISTS (
SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.status = 'open'
);Practice: Run on practice.
Derived table
SELECT AVG(avg_price) FROM (
SELECT category, AVG(price) AS avg_price FROM products GROUP BY category
) t;Important interview questions and answers
- Q: EXISTS vs IN?
A: EXISTS often performs well for correlated checks; IN is readable for small sets. - Q: Derived table alias?
A: Required in MySQL—use) talias.
Self-check
- Find customers with any open order using EXISTS.
- Why alias derived table?
Tip: Alias derived tables—MySQL requires it.
Interview prep
- EXISTS?
Semi-join pattern—true if subquery returns any row.
- Derived alias?
Required in MySQL for subquery in FROM.