SQL Subquery

SQL Subquery

can be used inside of WHERE, SELECT,FROM
在嵌套查询是，我们经常会使用到exists、in、any、all等

1.when subquery is used inside WHERE, it returns a column

find the name of all salesmen who had more than one customer:
(correlated subquery)

SELECT salesman_id,name 
FROM salesman AS s
WHERE 1 < 
    (SELECT COUNT(*) 
     FROM customer AS c
     WHERE c.salesman_id=s.salesman_id);

2.when subquery is used inside FROM, it returns a table

3.when subquery is used inside SELECT, it returns a single value