目录
内连接
1.隐式内连接 where
SELECT *
FROM emp,dept WHERE emp.dept_id=dept.id;
2.显式内连接inner join。。。on
select *
from emp inner join dept d on emp.dept_id = d.id;
tips:
- distinct 可以去重
- 连接查询后如果还有条件在where后直接加
UNION
剔除重复行;UNION ALL
不剔除重复行,保留两个表中的所有数据
外连接
2.1左外连接
SELECT *
FROM table1 LEFT JOIN table2 ON table1.key1=table2.key2;
2.2右外连接
SELECT *
FROM table1 RIGHT JOIN table2 ON table1.key1=table2.key2;
自然连接
SELECT *
FROM table1 NATURAL JOIN table2;