다중행 연산(MSSQL 문법 기준)

CREATE TABLE EMPLOYEES(
EMP_ID INT PRIMARY KEY,
EMP_NAME VARCHAR(50),
DEPT_ID INT
);

CREATE TABLE SALARIES(
EMP_ID INT,
SALARY DECIMAL(10, 2),
PRIMARY KEY(EMP_ID)
);

INSERT INTO EMPLOYEES(EMP_ID, EMP_NAME, DEPT_ID)
VALUES
(1, 'ALICE', 1),
(2, 'BOB', 2),
(3, 'CHARLIE', 3),
(4, 'DAVID', 3),
(5, 'EVA', 2),
(6, 'FRANK', 4),
(7, 'GEORGE', 4);

INSERT INTO SALARIES(EMP_ID, SALARY)
VALUES
(1, 60000.00),
(2, 50000.00),
(3, 55000.00),
(4, 45000.00),
(5, 70000.00),
(6, 48000.00),
(7, 52000.00);

SELECT *
FROM EMPLOYEES;

SELECT * 
FROM SALARIES;

IN 연산

SELECT EMP_NAME
FROM EMPLOYEES
WHERE DEPT_ID IN(2, 3);

ANY 연산

SELECT EMP_NAME
FROM EMPLOYEES
WHERE EMP_ID = ANY(SELECT EMP_ID FROM SALARIES WHERE SALARY > 48000);

ALL 연산

SELECT EMP_NAME
FROM EMPLOYEES
WHERE EMP_ID = ALL(SELECT EMP_ID FROM SALARIES WHERE SALARY < 48000);

EXISTS 연산

SELECT EMP_NAME
FROM EMPLOYEES
WHERE EXISTS(SELECT 1 FROM EMPLOYEES WHERE DEPT_ID = 1);