多表操作-笛卡尔集过滤讲解

 

#笛卡尔集
#连接查询
SELECT * FROM employee_china,employee_usa;


CREATE TABLE department(
        id int,
        name varchar(50)
);
INSERT INTO department VALUE(10001,'销售部');
INSERT INTO department VALUE(10002,'咨询部');
INSERT INTO department VALUE(10003,'人事部');
INSERT INTO department VALUE(10004,'技术部');

CREATE TABLE employee(
    id int,
    name varchar(50),
    depno int
);

INSERT INTO employee VALUES(1,'tony',10001);
INSERT INTO employee VALUES(2,'lucy',10001);
INSERT INTO employee VALUES(3,'mia',10001);

INSERT INTO employee VALUES(4,'amy',10002);
INSERT INTO employee VALUES(5,'jerry',10002);

INSERT INTO employee VALUES(6,'micheal',10003);
INSERT INTO employee VALUES(7,'lily',10003);

INSERT INTO employee VALUES(8,'elain',10004);
INSERT INTO employee VALUES(9,'ruly',10004);
INSERT INTO employee VALUES(10,'kk',10004);
INSERT INTO employee VALUES(11,'cici',10004);

#笛卡尔集过滤
SELECT * FROM employee e, department d WHERE e.depno = d.id;
SELECT * FROM employee , department WHERE employee.depno = department.id;

SELECT e.id '员工ID', e.name '员工名字',d.id '部门编号',d.name '部门名称' 
FROM employee e, department d 
WHERE e.depno = d.id;