40、子查詢：出現在其他SQL語句裡的SELECT語句
例如：SELECT sname,mark FROM student WHERE mark = (SELECT max(mark) FROM student);
SELECT sname,mark FROM student WHERE mark in (SELECT max(mark) FROM student);
等效於：SELECT sname,mark FROM student WHERE mark =any (SELECT max(mark) FROM student);
in等效於=any
not in等效於!=all 或<>all
41、多表連線查詢
內連線：INNER JOIN,JOIN,CROSS JOIN,NATURAL JOIN
意思：兩張表的交集，僅顯示符合連線條件的記錄，不符合的不顯示
用法：SELECT a.*,b.* FROM pa a INNER JOIN pb b ON a.id = b.id;
左(外)連線：LEFT (OUTER) JOIN
意思：顯示左表全部和左右表中符合連線條件的記錄
用法：SELECT a.*,b.* FROM pa a LEFT (OUTER) JOIN pb b ON a.id = b.id;
右(外)連線：RIGHT (OUTER) JOIN
意思：顯示右表全部和左右表中符合連線條件的記錄
用法：SELECT a.*,b.* FROM pa a RIGHT (OUTER) JOIN pb b ON a.id = b.id;
MySQL裡的全連線
SELECT a.*,b.* FROM pa a LEFT (OUTER) JOIN pb b ON a.id = b.id
UNION
SELECT a.*,b.* FROM pa a RIGHT (OUTER) JOIN pb b ON a.id = b.id;
UNION：會對結果去重
UNION ALL：不會對結果去重