多表查询
内连接
显示内连接比隐式内连接执行速度更快
sql
-- 隐式内连接
SELECT * FROM student s , score c WHERE s.s_id = c.s_id
-- 显示内连接
SELECT * FROM student s INNER JOIN score c ON s.s_id = c.s_id
-- INNER 可以省略
SELECT * FROM student s JOIN score c ON s.s_id = c.s_id外连接
- 左外连接
sql
SELECT s.*,c.s_score FROM student s LEFT OUTER JOIN score c ON s.s_id = c.s_id
-- OUTER 可以省略
SELECT s.*,c.s_score FROM student s LEFT JOIN score c ON s.s_id = c.s_id右外连接
sql
SELECT c.*,s.s_name FROM student s RIGHT OUTER JOIN score c ON s.s_id = c.s_id
-- OUTER 可以省略
SELECT c.*,s.s_name FROM student s RIGHT JOIN score c ON s.s_id = c.s_id联合查询
- 两张表合并允许重复
sql
SELECT * FROM user where id > 20
UNION ALL
SELECT * FROM user where age > 30- 两张表合并不允许重复
sql
SELECT * FROM user where id > 20
UNION
SELECT * FROM user where age > 30