where not exist (sub query) 사용 예제

SELECT *
FROM users
WHERE NOT EXISTS (
    SELECT 1
    FROM orders 
    WHERE orders.user_id =user.id
    );

where 절에 들어 간

orders테이블에서 orders.user_id = user.id 인 것을 제외하고 

users 테이블에서 * (모든 컬럼)을 보여주는 쿼리

 

예제 테이블 생성

-- employees 테이블 생성
CREATE TABLE employees (
    employee_id NUMBER PRIMARY KEY,
    employee_name VARCHAR2(50) NOT NULL,
    hire_date DATE NOT NULL
);

-- orders 테이블 생성
CREATE TABLE orders (
    order_id NUMBER PRIMARY KEY,
    employee_id NUMBER NOT NULL,
    order_date DATE NOT NULL,
    FOREIGN KEY (employee_id) REFERENCES employees(employee_id)
);

-- sample data 입력
INSERT INTO employees (employee_id, employee_name, hire_date) VALUES (1, 'Alice', '2022-01-01');
INSERT INTO employees (employee_id, employee_name, hire_date) VALUES (2, 'Bob', '2022-01-01');
INSERT INTO employees (employee_id, employee_name, hire_date) VALUES (3, 'Charlie', '2022-01-01');

INSERT INTO orders (order_id, employee_id, order_date) VALUES (1, 1, '2022-02-01');
INSERT INTO orders (order_id, employee_id, order_date) VALUES (2, 1, '2022-02-02');
INSERT INTO orders (order_id, employee_id, order_date) VALUES (3, 2, '2022-02-03');

employees 테이블:

employee_id	employee_name	hire_date
1	Alice	2022-01-01
2	Bob	2022-01-01
3	Charlie	2022-01-01

orders 테이블:

order_id	employee_id	order_date
1	1	2022-02-01
2	1	2022-02-02
3	2	2022-02-03

 

SELECT *
FROM employees e
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.employee_id = e.employee_id
);

 

 

 

select 결과

employees 테이블:

employee_id	employee_name	hire_date
3	Charlie	2022-01-01

 

NOT EXISTS(subquery) 

서브쿼리절에 해당하지 않는 행을 반환한다. 

예제는 oders 테이블에 존재하지 않는 id를 찾아 반환 하는 결과로 

사원중에 주문을 하지 않은 사원을 색출 하는? 쿼리문이다.