mysql in子查询

SELECT column_name(s)
FROM table_name
WHERE column_name IN (SELECT STATEMENT);

SELECT * FROM employees
WHERE city_id IN (SELECT city_id FROM cities WHERE city_name = 'New York');

-- 创建示例表
CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(255)
);

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(255),
    department_id INT
);

-- 插入示例数据
INSERT INTO departments (department_id, department_name) VALUES
(1, 'HR'),
(2, 'Engineering'),
(3, 'Marketing');

INSERT INTO employees (employee_id, employee_name, department_id) VALUES
(1, 'Alice', 1),
(2, 'Bob', 2),
(3, 'Charlie', 2),
(4, 'David', 3);

-- 使用IN子查询找出所有在Engineering部门工作的员工
SELECT * FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE department_name = 'Engineering');