SQL表连接查询

一、内连接查询（INNER JOIN | ON)

SELECT * FROM t_employees
INNER JOIN t_jobs
ON t_employees.`job_id` = t_jobs.`job_id`;

二、三表连接

SELECT * FROM t_employees
INNER JOIN t_departments
ON t_employees.`department_id` = t_departments.`dpID`
INNER JOIN t_jobs
ON t_jobs.`job_address` = t_departments.`dpID`;

三、左外连接（LEFT JOIN ON)

左外连接，是以左表为主表，依次向右匹配，匹配到，返回结果，匹配不到，则返回NULL值填充

SELECT employee_id,first_name,salary,department_id 
FROM t_employees
LEFT JOIN t_departments
ON t_departments.`dpID` = t_employees.`department_id`;

上诉代码查询t_employees表和t_departments表，连接的过程当中用的是左连接，也就是LEFT JOIN，左连接就是以左表为主表去向右表去匹配，匹配到就显示这个完整的结果，如果左表t_employees对应的t_departments表没有结果则显示NULL

四、右外连接（RIGHT JOIN ON)

SELECT employee_id,first_name,salary,department_id 
FROM t_employees
RIGHT JOIN t_departments
ON t_departments.`dpID` = t_employees.`department_id`;