[Oracle SQL] SELECT 실습_ NULL

[Oracle SQL] SELECT 실습_ NULL 

I NULL

▶ unavailable, unassigned, unknown, inapplicable            : 연산불가, 비교불가, 적용불가

(접근불가, 정의되지 않은, 존재하지 않는, 적용 불가능한)

1. 연산불가

---

SELECT 100*200, 300/0 FROM DUAL;

---

0으로 나누어지지 않기 때문에 오류 발생

---

SELECT 100*200, 300+NULL, 400/NULL FROM DUAL;

---

---

SELECT EMPNO,ENAME,SAL,COMM,COMM+(SAL*0.2) AS BONUS FROM EMP;

---

---

SELECT EMPNO,ENAME,SAL,NVL(COMM,0) AS COMM,NVL(COMM,0)+(SAL*0.2) AS BONUS FROM EMP;

---

---

SELECT EMPNO,ENAME,SAL,NVL(COMM,SAL) AS COMM,NVL2(COMM,SAL,0),NULLIF(JOB,'SALESMAN') FROM EMP;

---

NVL2(COMM,SAL,0) : COMM열에 SAL 넣고, 열의 레코드가 NULL이면 0을 넣는다.

2. 비교불가 

---

SELECT EMPNO,ENAME,SAL,COMM FROM EMP WHERE COMM > -1;

---

---

SELECT EMPNO,ENAME,SAL,COMM FROM EMP WHERE COMM = NULL;

---

---

SELECT EMPNO,ENAME,SAL,COMM FROM EMP WHERE COMM IS NULL;

---

---

SELECT EMPNO,ENAME,SAL,COMM FROM EMP WHERE COMM IS NOT NULL;

---

3. 적용불가

---

SELECT EMPNO,ENAME,LENGTH(ENAME),COMM,LENGTH(COMM) FROM EMP;

---

4. NULL 무시 함수

---

SELECT CONCAT(ENAME||'''S COMM IS ',COMM),NVL(COMM,0),DECODE(COMM,NULL,0,COMM) FROM EMP;

---