Pacific-Design.com

    
Home Index

1. SQL Language

2. 03 SUBQUERIES

SQL Language / 03 SUBQUERIES /

SQL Subquery - FROM

SELECT AVG(rate) 
FROM ( 
    SELECT rate 
    FROM bank.interest 
    WHERE dt >= (NOW() - INTERVAL 1 MONTH) 
    ORDER BY rate DESC 
    LIMIT 20000
) 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
      GROUP BY depatment_id 
   ) AS a

ON sales.order_id = a.order_id
WHERE  sales.total_sale > a.average_sale


Reference: