Null 속성의 이해

-- Oracle: 연습용 스키마에서 처음 한 번만 실행
CREATE TABLE STUDENT_SCORE (
    STUDENT_ID VARCHAR2(5) PRIMARY KEY,
    NAME       VARCHAR2(20 CHAR) NOT NULL,
    SCORE      NUMBER(3),
    BONUS      NUMBER(3)
);

INSERT INTO STUDENT_SCORE VALUES ('S1', '서연', 80, 5);
INSERT INTO STUDENT_SCORE VALUES ('S2', '민준', NULL, NULL);
INSERT INTO STUDENT_SCORE VALUES ('S3', '하은', 60, NULL);
INSERT INTO STUDENT_SCORE VALUES ('S4', '도윤', 40, 0);
COMMIT;

-- 잘못된 비교: 0건
SELECT NAME FROM STUDENT_SCORE WHERE SCORE = NULL;

-- 올바른 비교: 민준
SELECT NAME FROM STUDENT_SCORE WHERE SCORE IS NULL;

-- Before / After: 보너스를 더한 최종 점수
SELECT NAME,
       SCORE + BONUS          AS TOTAL_BEFORE,
       SCORE + NVL(BONUS, 0)  AS TOTAL_AFTER,
       NVL2(BONUS, '있음', '없음') AS BONUS_FLAG
  FROM STUDENT_SCORE
 ORDER BY STUDENT_ID;

CREATE TABLE WORK_MEMBER (
    MEMBER_ID   NUMBER PRIMARY KEY,
    MEMBER_NAME VARCHAR2(30) NOT NULL,
    PHONE       VARCHAR2(30),
    EMAIL       VARCHAR2(80)
);

CREATE TABLE WORK_RESERVATION (
    RESERVATION_ID NUMBER PRIMARY KEY,
    MEMBER_ID      NUMBER NOT NULL REFERENCES WORK_MEMBER(MEMBER_ID),
    BASE_PRICE     NUMBER(8) NOT NULL,
    EXTRA_PRICE    NUMBER(8),
    PAYMENT_AMOUNT NUMBER(8)
);

INSERT INTO WORK_MEMBER VALUES (1, '지우', '010-1100', 'jiu@example.test');
INSERT INTO WORK_MEMBER VALUES (2, '도윤', NULL, 'doyun@example.test');
INSERT INTO WORK_MEMBER VALUES (3, '하린', NULL, NULL);
INSERT INTO WORK_MEMBER VALUES (4, '민재', ' ', 'minjae@example.test');
INSERT INTO WORK_MEMBER VALUES (5, '소율', '', NULL);
INSERT INTO WORK_MEMBER VALUES (6, '채원', NULL, 'chaewon@example.test');

INSERT INTO WORK_RESERVATION VALUES (101, 1, 100, 10, 100);
INSERT INTO WORK_RESERVATION VALUES (102, 2, 200, NULL, NULL);
INSERT INTO WORK_RESERVATION VALUES (103, 3, 300, 20, 200);
INSERT INTO WORK_RESERVATION VALUES (104, 4, 400, NULL, 0);
INSERT INTO WORK_RESERVATION VALUES (105, 5, 500, 0, NULL);

-- 원본 산술과 NULL을 0으로 대체한 계산 비교
SELECT R.RESERVATION_ID,
       R.EXTRA_PRICE,
       R.BASE_PRICE + R.EXTRA_PRICE AS RAW_QUOTE,
       R.BASE_PRICE + NVL(R.EXTRA_PRICE, 0) AS FINAL_QUOTE,
       R.PAYMENT_AMOUNT,
       COALESCE(TRIM(M.PHONE), M.EMAIL, '연락처 없음') AS CONTACT
FROM WORK_RESERVATION R
JOIN WORK_MEMBER M ON M.MEMBER_ID = R.MEMBER_ID
ORDER BY R.RESERVATION_ID;

-- NULL 전용 조건과 상태 표시
SELECT RESERVATION_ID, NVL2(PAYMENT_AMOUNT, '기록됨', '미기록') AS PAYMENT_STATUS
FROM WORK_RESERVATION
WHERE PAYMENT_AMOUNT IS NULL
ORDER BY RESERVATION_ID;

-- 틀린 비교: UNKNOWN이므로 0행
SELECT RESERVATION_ID FROM WORK_RESERVATION WHERE PAYMENT_AMOUNT = NULL;

-- 서브쿼리에 NULL이 포함되어 NOT IN이 0행
SELECT RESERVATION_ID FROM WORK_RESERVATION
WHERE BASE_PRICE NOT IN (SELECT PAYMENT_AMOUNT FROM WORK_RESERVATION);

-- 상관 NOT EXISTS: BASE_PRICE와 같은 결제액이 없는 예약
SELECT R.RESERVATION_ID
FROM WORK_RESERVATION R
WHERE NOT EXISTS (
    SELECT 1 FROM WORK_RESERVATION X
    WHERE X.PAYMENT_AMOUNT = R.BASE_PRICE
)
ORDER BY R.RESERVATION_ID;