Topic describes

Select * from employees;

Dept_emp = dept_EMP = dept_EMP

Dept_manager = dept_manager;

There is a salary table as follows:

Dept_no, EMP_NO and salary. Output the above example:

Example 1 Enter: drop table if exists' dept_emp '; drop table if exists `dept_manager` ; drop table if exists `employees` ; drop table if exists `salaries` ; CREATE TABLE `dept_emp` ( `emp_no` int(11) NOT NULL, `dept_no` char(4) NOT NULL, `from_date` date NOT NULL, `to_date` date NOT NULL, PRIMARY KEY (`emp_no`,`dept_no`)); CREATE TABLE `dept_manager` ( `dept_no` char(4) NOT NULL, `emp_no` int(11) NOT NULL, `from_date` date NOT NULL, `to_date` date NOT NULL, PRIMARY KEY (`emp_no`,`dept_no`)); CREATE TABLE `employees` ( `emp_no` int(11) NOT NULL, `birth_date` date NOT NULL, `first_name` varchar(14) NOT NULL, `last_name` varchar(16) NOT NULL, `gender` char(1) NOT NULL, `hire_date` date NOT NULL, PRIMARY KEY (`emp_no`)); CREATE TABLE `salaries` ( `emp_no` int(11) NOT NULL, `salary` int(11) NOT NULL, `from_date` date NOT NULL, `to_date` date NOT NULL, PRIMARY KEY (`emp_no`,`from_date`)); INSERT INTO dept_emp VALUES(10001,'d001','1986-06-26','9999-01-01'); INSERT INTO dept_emp VALUES(10002,'d001','1996-08-03','9999-01-01'); INSERT INTO dept_manager VALUES('d001',10002,'1996-08-03','9999-01-01'); INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','1986-06-26'); INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1996-08-03'); INSERT INTO salaries VALUES (58, 10001889 '1986-06-26', '9999-01-01'); INSERT INTO salaries VALUES (27, 10002725, '1996-08-03', '9999-01-01'); Copy the output: d001 | 10001 | 88958Copy the code

Answer key

JOIN adds a subquery

Elapsed time: 14ms over 76.03% Sqlite code footprint: 3456KB over 27.00% Sqlite code

SELECT t.dept_no, em.emp_no, t.salary FROM employees AS em
JOIN (
    SELECT de.emp_no, de.dept_no, sa.salary
    FROM dept_emp de JOIN salaries sa
    ON de.emp_no = sa.emp_no
    AND de.to_date = '9999-01-01'
) AS t
WHERE em.emp_no NOT IN (
    SELECT emp_no FROM dept_manager
)
AND t.emp_no = em.emp_no;
Copy the code