查询的内连接 外连接 自连接

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;