SELECTAVG(rate)
FROM (
SELECT rate
FROM bank.interest
WHERE dt >= (NOW() -INTERVAL1MONTH)
ORDERBY rate DESCLIMIT20000
) a
SQL Subquery - IN
SELECT ProductName
FROM Product
WHERE Id IN (
SELECT ProductId
FROM OrderItem
WHERE Quantity >100
)
SQL Subquery - WHERE
SELECT*FROM t1
WHERE column1 = (SELECT column1 FROM t2);
SQL Subquery - JOIN Alias (Facebook)
SELECT salesman, SUM(amount) AS total_sale
FROM sales
JOIN
(
SELECT depatment_id, AVG(amount) AS average_sale
FROM sales
GROUPBY depatment_id
) AS a
ON sales.order_id = a.order_id
WHERE sales.total_sale > a.average_sale