Mysql连接查询示例语句

SELECT *FROM ssm_emp;

SELECT * FROM ssm_dept;


#查询两表交集
SELECT * FROM ssm_emp e
INNER JOIN ssm_dept d
ON e.`d_id`=d.`dept_id`;

#左表为主表,左表中不符合连接条件的列用null填充,右表中不符合连接条件的列不显示,并按`emp_id`默认升序排列
SELECT * FROM ssm_emp e
LEFT JOIN ssm_dept d
ON e.`d_id`=d.`dept_id`
ORDER BY e.`emp_id`;

#查询左表中不符合连接条件的列
SELECT * FROM ssm_dept d
LEFT JOIN ssm_emp e
ON d.`dept_id`=e.`d_id`
WHERE e.`emp_id` IS NULL;

#查询左表中符合连接条件的列
SELECT * FROM ssm_emp e
LEFT JOIN ssm_dept d
ON e.`d_id`=d.`dept_id`
WHERE d.`dept_id` IS NOT NULL;

#联合查询,要求两个查询结果的列必须相同
SELECT e.`emp_id`,e.`emp_name`,d.`dept_id`,d.`dept_name` FROM ssm_emp e
LEFT JOIN ssm_dept d
ON e.`d_id`=d.`dept_id`
UNION
SELECT e.`emp_id`,e.`emp_name`,d.`dept_id`,d.`dept_name` FROM ssm_dept d
LEFT JOIN ssm_emp e 
ON e.`d_id`=d.`dept_id`
WHERE e.`emp_id` IS NULL;

#联合查询,并把结果作为一个新表并条件降序排列
SELECT * FROM (
	SELECT e.`emp_id`,e.`emp_name`,d.`dept_id`,d.`dept_name` FROM ssm_emp e
	LEFT JOIN ssm_dept d
	ON e.`d_id`=d.`dept_id`
	UNION
	SELECT e.`emp_id`,e.`emp_name`,d.`dept_id`,d.`dept_name` FROM ssm_dept d
	LEFT JOIN ssm_emp e 
	ON e.`d_id`=d.`dept_id`
	WHERE e.`emp_id` IS NULL
) AS t 
ORDER BY t.dept_id DESC;