[SQL] subquery | 子查詢

select, where , from, 等語句都可以內嵌子查詢的語法.
括號裡面的回傳欄位只能是一個

mysql> select* from testtable2 where price  > (select price from testtable2 where name = 'leo' ) ;
+----+------+------+-------+-----+-------+
| id | name | job  | price | car | title |
+----+------+------+-------+-----+-------+
|  4 | jim  | sw   | 7     |   6 | NULL  |
+----+------+------+-------+-----+-------+
1 row in set (0.00 sec)

IN

mysql> select* from testtable2 where name in ('lewis','leo' ) ;                     
+----+-------+------+-------+-----+-------+
| id | name  | job  | price | car | title |
+----+-------+------+-------+-----+-------+
|  1 | lewis | sw   | 2     |   2 | NULL  |
|  2 | lewis | sw   | 3     | 111 | NULL  |
|  3 | leo   | sw   | 4     | 123 | NULL  |
+----+-------+------+-------+-----+-------+
3 rows in set (0.00 sec)