describe

Alter table dept_emp;

The first line indicates that the department whose employee number is 10001 is d001.

Dept_manager = dept_manager;

The first line shows that the manager of department D001 is employee 10002.

Get all employees and their corresponding managers, except if the employee is the manager, as shown below:

Example 1

Input:

drop table if exists  `dept_emp` ; 
drop table if exists  `dept_manager` ; 
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`));
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_emp VALUES(10003,'d002','1995-12-03','9999-01-01');
INSERT INTO dept_manager VALUES('d001',10002,'1996-08-03','9999-01-01');
INSERT INTO dept_manager VALUES('d002',10003,'1990-08-05','9999-01-01');
Copy the code

Output:

10001 | 10002Copy the code

My answer

select dept_emp.emp_no, dept_manager.emp_no from dept_emp join dept_manager on dept_emp.dept_no = dept_manager.dept_no where dept_emp.emp_no not  in (select emp_no from dept_manager);Copy the code