Multi-table query for MySQL

1. Cartesian product

select * from emp,dep; select * from emp,dep where emp.dep_id = dep.id;select * from emp,dep where emp.dep_id = dep.id and dep.name = "技术";

2, within the connection: only two tables have a corresponding record of the relationship

select * from emp inner join dep on emp.dep_id = dep.id;select * from emp inner join dep on emp.dep_id = dep.id                                                        where dep.name = "技术";        inner join 连接两个表, where过滤条件,只取条件符合的

3, left join: On the basis of the internal connection to retain the left table does not have a corresponding relationship record

select * from emp left join dep on emp.dep_id = dep.id;

4, right connection: On the basis of the internal connection to keep the right table does not have a corresponding relationship record

select * from emp right join dep on emp.dep_id = dep.id;

5, the full connection: On the basis of the internal connection to keep left, the right table does not correspond to the record

select * from emp left join dep on emp.dep_id = dep.idunionselect * from emp right join dep on emp.dep_id = dep.id;

Multi-table query for MySQL