mysql新手入門隨筆4

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：不會對結果去重