CREATE TABLE employee(
id INT,
empName VARCHAR(10),
deptId INT
)
CREATE TABLE dept(
id INT,
depName VARCHAR(10)
)
INSERT INTO employee VALUES (1, '张三', 1);
INSERT INTO employee VALUES (2, '李四', 1);
INSERT INTO employee VALUES (3, '王武', 2);
INSERT INTO employee VALUES (4, '陈六', 3);
INSERT INTO employee(id, empName) VALUES (5, '刘五');
INSERT INTO dept VALUES (1, '部门一');
INSERT INTO dept VALUES (2, '部门二');
INSERT INTO dept VALUES (3, '部门三');
INSERT INTO dept VALUES (4, '部门四');
SELECT * FROM employee;
SELECT * FROM dept;
--内连接等价于where形式
SELECT a.empName, b.depName FROM employee a, dept b WHERE a.deptId = b.id
--内连接等价于WHERE
SELECT a.empName, b.depName
FROM employee a
INNER JOIN dept b
ON a.deptId=b.id;
--以左边表为准去匹配右边
SELECT a.empName, b.depName
FROM employee a
LEFT OUTER JOIN dept b
ON a.deptId=b.id;
--以右边表为准 去匹配左边
SELECT a.empName, b.depName
FROM employee a
RIGHT OUTER JOIN dept b
ON a.deptId=b.id;
--自连接:查自身的上司
SELECT a.empName, b.empName
FROM employee a
LEFT OUTER JOIN employee b
ON a.deptId=b.id;
Oracle左外连接
Select * from dave a,bl b where a.id=b.id(+);
Oracle右外连接
Select * from dave a,bl b where a.id(+)=b.id;