01.内连接和外连接查询

-- a>内连接 INNER JOIN
SELECT * FROM employee e , department d WHERE e.depno = d.id;

SELECT e.id,e.name,e.depno,d.name FROM employee e , department d WHERE e.depno = d.id;
#以后查询时，需要什么数据，就搜索什么数据，不把多余的列显示，提高性能

#上面的写法就是内连接，理解成MySQL方言，也就是在其它数据库【Oracle】不一定能这样写

#标准内连接
SELECT * FROM employee e INNER JOIN department d WHERE e.depno = d.id;

SELECT * FROM employee e INNER JOIN department d ON e.depno = d.id;#工作中用这种写法,用ON
SELECT e.id,e.name,e.depno,d.name FROM employee e INNER JOIN department d ON e.depno = d.id;

-- 外连接
INSERT INTO department VALUE(10005,'保洁部');
INSERT INTO employee (id,name) VALUES(12,'ela');

-- 左连接[LEFT JOIN] == 左外连接[LEFT OUTER JOIN]:下面两种 写法等价
-- ：左连接：右表满足条件的会显示空，而上面内连接，右表不满足条件的就不会显示
-- 左连接的特性：左右表条件不满足时，左表显示数据,右表显示数据为NULL
SELECT e.id,e.name,e.depno,d.name FROM employee e LEFT OUTER JOIN department d ON e.depno = d.id;
SELECT e.id,e.name,e.depno,d.name FROM employee e LEFT JOIN department d ON e.depno = d.id;

-- 右连接 RIGHT JOIN = RIGHT OUTER JOIN【右外连接】
-- 右连接的特性：左右表条件不满足时，左表显示NULL,右表显示数据
SELECT e.id,e.name,e.depno,d.name FROM employee e RIGHT OUTER JOIN department d ON e.depno = d.id;
SELECT e.id,e.name,e.depno,d.name FROM employee e RIGHT JOIN department d ON e.depno = d.id;