MySQL5 多表查询 外连接

外连接

数据准备

DROP DATABASE IF EXISTS TEST;
CREATE DATABASE IF NOT EXISTS TEST;
USE TEST;
DROP TABLE EMP;
DROP TABLE DEPT;
DROP TABLE SALGRADE;
CREATE TABLE DEPT
(
	deptno integer(4) not null,
	dname varchar(14),
	loc varchar(13)
);

ALTER TABLE dept ADD CONSTRAINT pk_dept PRIMARY KEY(deptno);
CREATE TABLE EMP
(
	empno integer(4) not null,
	ename varchar(10),
	job varchar(9),
	mgr integer(4),
	hiredate date,
	sal decimal(7,2),
	comm decimal(7,2),
	deptno integer(2)
);
ALTER TABLE EMP ADD CONSTRAINT pk_emp PRIMARY KEY (empno);
ALTER TABLE EMP ADD CONSTRAINT fk_deptno FOREIGN KEY (deptno) REFERENCES DEPT (deptno);
CREATE TABLE SALGRADE
(
	grade integer(1),
	losal decimal(7,2),
	hisal decimal(7,2)
);
INSERT INTO DEPT (DEPTNO, DNAME, LOC) VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT (DEPTNO, DNAME, LOC) VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO DEPT (DEPTNO, DNAME, LOC) VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO DEPT (DEPTNO, DNAME, LOC) VALUES (40, 'OPERATIONS', 'BOSTON');
INSERT INTO SALGRADE (GRADE, LOSAL, HISAL) VALUES (1, 700, 1200);
INSERT INTO SALGRADE (GRADE, LOSAL, HISAL) VALUES (2, 1201, 1400);
INSERT INTO SALGRADE (GRADE, LOSAL, HISAL) VALUES (3, 1401, 2000);
INSERT INTO SALGRADE (GRADE, LOSAL, HISAL) VALUES (4, 2001, 3000);
INSERT INTO SALGRADE (GRADE, LOSAL, HISAL) VALUES (5, 3001, 9999);
/* 下面要特别注意，%d-%m-%Y 里的d和m一定要小写 */
INSERT INTO  EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES (7369, 'SMITH', 'CLERK', 7902, STR_TO_DATE('17-12-1980', '%d-%m-%Y'), 800.00, null, 20);
INSERT INTO  EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES (7499, 'ALLEN', 'SALESMAN', 7698, STR_TO_DATE('20-02-1981', '%d-%m-%Y'), 1600.00, 300.00, 30);
INSERT INTO  EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES (7521, 'WARD', 'SALESMAN', 7698, STR_TO_DATE('22-02-1981', '%d-%m-%Y'), 1250.00, 500.00, 30);
INSERT INTO  EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES (7566, 'JONES', 'MANAGER', 7839, STR_TO_DATE('02-04-1981', '%d-%m-%Y'), 2975.00, null, 20);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES (7654, 'MARTIN', 'SALESMAN', 7698, STR_TO_DATE('28-09-1981', '%d-%m-%Y'), 1250.00, 1400.00, 30);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES (7698, 'BLAKE', 'MANAGER', 7839, STR_TO_DATE('01-05-1981', '%d-%m-%Y'), 2850.00, null, 30);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES (7782, 'CLARK', 'MANAGER', 7839, STR_TO_DATE('09-06-1981', '%d-%m-%Y'), 2450.00, null, 10);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES (7788, 'SCOTT', 'ANALYST', 7566, STR_TO_DATE('19-04-1987', '%d-%m-%Y'), 3000.00, null, 20);
INSERT INTO  EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES (7839, 'KING', 'PRESIDENT', null, STR_TO_DATE('17-11-1981', '%d-%m-%Y'), 5000.00, null, 10);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES (7844, 'TURNER', 'SALESMAN', 7698, STR_TO_DATE('08-09-1981', '%d-%m-%Y'), 1500.00, 0.00, 30);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES (7876, 'ADAMS', 'CLERK', 7788, STR_TO_DATE('23-05-1987', '%d-%m-%Y'), 1100.00, null, 20);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES (7900, 'JAMES', 'CLERK', 7698, STR_TO_DATE('03-12-1981', '%d-%m-%Y'), 950.00, null, 30);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES (7902, 'FORD', 'ANALYST', 7566, STR_TO_DATE('03-12-1981', '%d-%m-%Y'), 3000.00, null, 20);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES (7934, 'MILLER', 'CLERK', 7782, STR_TO_DATE('23-01-1982', '%d-%m-%Y'), 1300.00, null, 10);

左外连接

左外连接
SELECT 字段 FROM 表1 LEFT [OUTER] JOIN 表2 ON 连接条件

SELECT * 
FROM emp e RIGHT OUTER JOIN dept d ON
e.dept_id = d.deptno;

SELECT e.eid,e.ename,e.age,e.dept_id,d.deptno,d.name 
FROM dept d RIGHT JOIN emp e ON e.dept_id=d.deptno;

右外连接

右外连接
SELECT 字段 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 连接条件

SELECT * 
FROM emp e RIGHT OUTER JOIN dept d ON
e.dept_id = d.deptno;

SELECT e.eid,e.ename,e.age,e.dept_id,d.deptno,d.name 
FROM dept d RIGHT JOIN emp e ON e.dept_id=d.deptno;

满外连接

全外连接,满外连接
UNION ALL 不去重
UNION 去重

SELECT e.eid,e.ename,e.age,e.dept_id,d.deptno,d.name 
FROM dept d LEFT JOIN emp e ON 
e.dept_id=d.deptno
UNION ALL
SELECT e.eid,e.ename,e.age,e.dept_id,d.deptno,d.name 
FROM dept d RIGHT JOIN emp e ON 
e.dept_id=d.deptno;

SELECT e.eid,e.ename,e.age,e.dept_id,d.deptno,d.name 
FROM dept d LEFT JOIN emp e ON 
e.dept_id=d.deptno
UNION
SELECT e.eid,e.ename,e.age,e.dept_id,d.deptno,d.name 
FROM dept d RIGHT JOIN emp e ON 
e.dept_id=d.deptno;