03.自然连接NATRUAL-JOIN讲解

#03.自然连接：不需要添加主外键的等式，也可以去除笛卡集的数据
-- 但是有个前提条件：两张连接的表中名称和类型完全一致的列作为条件，例如emp和dept表都存在deptno

CREATE TABLE t_department(
        depno varchar(10),
        depname varchar(50),
        primary key(depno)
);

CREATE TABLE t_employee(
    eid int,
    ename varchar(50),
    depno varchar(10),
    foreign key(depno) references t_department(depno)
);

insert into t_department values('10','财务');
insert into t_department values('20','市场');
insert into t_department values('30','Java研发部');
insert into t_department values('40','PHP研发部');
insert into t_employee values(1,'gyf','10');
insert into t_employee values(2,'ljl','20');
insert into t_employee values(3,'hg','30');

SELECT * FROM t_employee e INNER JOIN t_department d ON e.depno =  d.depno;#内连接

SELECT * FROM t_employee NATURAL JOIN t_department;#自然连接
SELECT * FROM t_employee INNER JOIN t_department USING(depno);#等效于自然连接

SELECT * FROM t_employee  NATURAL LEFT JOIN t_department;#自然左连接 效果等于左连接

SELECT * FROM t_employee  NATURAL RIGHT JOIN t_department;#自然右连接 效果等于右连接