1、inner join同时查3个表
人员表employee(字段:name,dept_id,post_id);
部门表dept(字段:dept_id,dept_name);
岗位表position(字段:post_id,post_name)。
SELECT employee.name,dept.dept_name,position.post_name from employee
inner join dept on employee.dept_id=dept.dept_id
inner join position on employee.post_id=position.post_id;
这样就可以一步查出姓名、部门名称、岗位名称了。
2、4种不同的JOINs:
INNER JOIN: Return rows when there is at least one match in both tables,只检索出两边都得有数据的行,下面的类推
LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table,右边没有没关系
RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table,左边没有没关系
FULL JOIN: Return rows when there is a match in one of the tables,任何一边有就行
语法基本一样,例句:
SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name