JOIN关联和LEFT JOIN区别？

CREATE TABLE `departments` (
`dept_no` char(4) NOT NULL,
`dept_name` varchar(40) NOT NULL,
PRIMARY KEY (`dept_no`));
CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
INSERT INTO departments VALUES('d001','Marketing');
INSERT INTO departments VALUES('d002','Finance');
INSERT INTO departments VALUES('d003','Human Resources');
INSERT INTO dept_emp VALUES(10001,'d001','1986-06-26','9999-01-01');
INSERT INTO dept_emp VALUES(10002,'d001','1996-08-03','9999-01-01');
INSERT INTO dept_emp VALUES(10003,'d002','1990-08-05','9999-01-01');
INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','1986-06-26');
INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21');
INSERT INTO employees VALUES(10003,'1959-12-03','Parto','Bamford','M','1986-08-28');
INSERT INTO employees VALUES(10004,'1954-05-01','Chirstian','Koblick','M','1986-12-01');
INSERT INTO employees VALUES(10005,'1939-07-01','左连接','测试','M','1986-12-01');

复制到SQL工具里面执行

select t1.last_name,t1.first_name,t3.dept_name from employees t1 
LEFT JOIN dept_emp t2 on t1.emp_no = t2.emp_no 
LEFT JOIN departments t3 on t2.dept_no =t3.dept_no

解析下SQL
select t1.last_name,t1.first_name,t3.dept_name from employees t1
这个好理解就是查询这个表中的字段
LEFT JOIN dept_emp t2 on t1.emp_no = t2.emp_no 
开始连接t2的这个表，t1表中里面的字段 得等于 t2表中的字段
在接着走
LEFT JOIN departments t3 on t2.dept_no =t3.dept_no
开始连接第三张 这个是那t2表中的字段 等于 t3表中的字段 这样就把这三张表关联上了
先看下结果.......

说下我的数据库join 理解：
JOIN默认是内连接 数据必须是和管理数据一致的，如果关联的数据t2表没有数据t1表有数据这条数据测不显示
相反
LEFT JOIN 是t1关联t2表t1表中有十条数据t2表有5条数据t1和t2关联后t1显示所有t2显示5条其余显示null

• 好啦今天分析完毕，希望对你有帮助！