SQLD 기출문제 예상문제 47번

다음 문제를 풀어보세요.

다음 중 아래 SQL을 실행했을 때, 가장 효율적인 실행 계획을 생성하도록 쿼리를 변환한 것으로 적절한 것을 고르시오.

-- 기존 쿼리
SELECT A.CUSTOMER_ID, A.CUSTOMER_NAME, B.ORDER_ID, B.ORDER_DATE
FROM CUSTOMER A
LEFT OUTER JOIN ORDER B ON A.CUSTOMER_ID = B.CUSTOMER_ID
WHERE A.CUSTOMER_ID IN (SELECT CUSTOMER_ID FROM ACCOUNT WHERE BALANCE > 1000000);

[테이블 구조]
CREATE TABLE CUSTOMER (
    CUSTOMER_ID VARCHAR2(10) PRIMARY KEY,
    CUSTOMER_NAME VARCHAR2(50)
);

CREATE TABLE ORDER (
    ORDER_ID VARCHAR2(10) PRIMARY KEY,
    CUSTOMER_ID VARCHAR2(10),
    ORDER_DATE DATE,
    FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER(CUSTOMER_ID)
);

CREATE TABLE ACCOUNT (
    ACCOUNT_ID VARCHAR2(10) PRIMARY KEY,
    CUSTOMER_ID VARCHAR2(10),
    BALANCE NUMBER,
    FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER(CUSTOMER_ID)
);

SELECT A.CUSTOMER_ID, A.CUSTOMER_NAME, B.ORDER_ID, B.ORDER_DATE
FROM CUSTOMER A
LEFT OUTER JOIN ORDER B ON A.CUSTOMER_ID = B.CUSTOMER_ID
WHERE EXISTS (SELECT 1 FROM ACCOUNT WHERE CUSTOMER_ID = A.CUSTOMER_ID AND BALANCE > 1000000);

SELECT A.CUSTOMER_ID, A.CUSTOMER_NAME, B.ORDER_ID, B.ORDER_DATE
FROM (SELECT CUSTOMER_ID, CUSTOMER_NAME FROM CUSTOMER WHERE CUSTOMER_ID IN (SELECT CUSTOMER_ID FROM ACCOUNT WHERE BALANCE > 1000000)) A
LEFT OUTER JOIN ORDER B ON A.CUSTOMER_ID = B.CUSTOMER_ID;

SELECT A.CUSTOMER_ID, A.CUSTOMER_NAME, B.ORDER_ID, B.ORDER_DATE
FROM CUSTOMER A
LEFT OUTER JOIN ORDER B ON A.CUSTOMER_ID = B.CUSTOMER_ID
WHERE A.CUSTOMER_ID = ANY (SELECT CUSTOMER_ID FROM ACCOUNT WHERE BALANCE > 1000000);

SELECT A.CUSTOMER_ID, A.CUSTOMER_NAME, B.ORDER_ID, B.ORDER_DATE
FROM CUSTOMER A
LEFT OUTER JOIN ORDER B ON A.CUSTOMER_ID = B.CUSTOMER_ID
WHERE A.CUSTOMER_ID = (SELECT CUSTOMER_ID FROM ACCOUNT WHERE BALANCE > 1000000);