[SQLD] NULL 관련 함수, CASE, DECODE, SQL실행순서

 

중요한거 정리한거 아니고 내가 볼려고 헷갈리는거 정리함

 

기본적으로 ORACLE 기준으로 정리

MySQL이랑 MSSQL은 개별적으로 따로 정리 해 놓음

---

1. NVL(인수1, 인수2)

`인수1`의 값이 `NULL`일 경우 `인수2` 반환, `NULL` 아닐 경우 `인수 1` 반환

 

한 마디로 연산해야 하는데 NULL 값이 있으면 0으로 치환해줘야 되니까 0으로 바꾸는 함수라고 외우면 된다. 예를 들면 영화 리뷰하는데 평점 사이에 NULL이 껴 있어서 평균을 못 내줄 때 그냥 다 0으로 치환할 때 쓰는거라 이해하면 된다. 

NVL(expression, replacement_value)

• expression: 확인할 값.
• replacement_value: expression이 NULL일 때 대신 사용할 값.

 

ORACLE `NVL(인수1, 인수2)`

MySQL `IFNULL(인수1, 인수2)`

MSSQL `ISNULL(인수1, 인수2)`

NVL(REVIEW_SOCRE, 0) // Oracle
IFNULL(REVIEW_SOCRE, 0) // MYSQL
ISNULL (REVIEW_SOCRE, 0) // MSSQL

---

2. NULLIF(인수1, 인수2)

`인수1`과 `인수2`가 같으면 `NULL` 반환하고, 같지 않으면 `인수1` 반환

NULLIF(expression1, expression2)

• expression1: 첫 번째 값 (나누려는 값)
• expression2: 두 번째 값 (나누는 값)
• 두 값이 같으면 NULL을 반환하고, 그렇지 않으면 첫 번째 값을 반환

 

그냥 제로나누기 오류 방지용이라고 생각 하자

SELECT 100 / NULLIF(denominator, 0) AS result
FROM dual;

// denominator가 0이면 나누기 연산 자체가 NULL을 반환하게 되고, 오류발생X
// denominator가 0이 아니면 정상적으로 나누기 연산이 수행

 

---

3. COALESCE(인수1, 인수2, 인수3...)

null이 아닌 첫번째 값(인수) 반환

 

예) COALESCE(PHONE, EMAIL, FAX)

쉬우니까 건너 뛰자

---

4. NVL2(인수1, 인수2, 인수3)

인수1이 NULL이 아닌 경우 인수2 반환, NULL인 경우 인수3 반환(오라클에만 있음)

NVL2(REVIEW_SCORE, '리뷰있음', '리뷰없음')

 

---

5. CASE

CASE WHEN SUBWAY_LINE = '1' THEN 'BLUE'
     WHEN SUBWAY_LINE = '2' THEN 'GREEN'
     WHEN SUBWAY_LINE = '3' THEN 'ORANGE'
     [ELSE 'GRAY']
END

CASE SUBWAY_LINE
     WHEN '1' THEN 'BLUE'
     WHEN '2' THEN 'GREEN'
     WHEN '3' THEN 'ORANGE'
     [ELSE 'GRAY']
END

DECODE (SUBWAY_LINE,'1','BLUE','2','GREEN','3','ORANGE'[,'GRAY'])

 

CASE문에서는 ELSE 뒤의 값이 DEFAULT 값이 되고 별도의 ELSE가 없을 경우 NULL 값이 DEFAULT값이 된다. 

---

6. 연산자

부정비교연산자

`^=`, `!=`, `<>` 같지 않음

`where not col = 10`같지 않음

 

FAVORITES가 Y가 아닌 행 조회

SELECT PLAY_ID, NAME, FAVORITES
FROM PLAY_LIST
WEHRE FAVORITES <> 'Y';

 

---

TITLE이 IT Staff와 IT Manager이 아닌 행 조회하기

아래의 쿼리는 모두 같은 쿼리이다

SELECT LAST_NAME, FIRST_NAME, TITLE
FROM EMPLOYEE
WHERE TITLE NOT IN ('IT Staff', 'IT Manager');

SELECT LAST_NAME, FIRST_NAME, TITLE
FROM EMPLOYEE
WHERE NOT(TITLE IN('IT Staff', 'It Manager'));

SELECT LAST_NAME, FIRST_NAME, TITLE
FROM EMPLOYEE
WHERE NOT (TITLE = 'IT Staff' OR TITLE = 'IT Manager');

SELECT LAST_NAME, FIRST_NAME, TITLE
FROM EMPLOYEE
WHERE (TITLE <> 'IT Staff' AND TITLE <> 'IT Manager');

---

두개는 같은 표현

 

SELECT * FROM EMP WHERE NOT (SAL < 300 AND SAL > 500 );

SELECT * FROM EMP WHERE SAL >= 300 OR SAL <= 500

 

---

⭐⭐⭐ <>과 NOT 차이

 

title이 'sales support agent' 이거나 city가 'Calgary'인 직원만 제외하는 쿼리

title이 'sales support agent'가 아니고, city가 'Calgary'가 아닌 사람들만 반환

SELECT LAST_NAME, FIRST_NAME, TITLE, CITY
FROM EMPLOYEE
WHERE NOT (TITLE = 'SALES SUPPORT AGENT' OR CITY = 'CALGARY');

SELECT LAST_NAME, FIRST_NAME, TITLE, CITY
FROM EMPLOYEE
WHERE title <> 'sales support agent' AND city <> 'calgary';

 

 

title이 'sales support agent'이고 city가 'Calgary'인 직원만 제외

NOT 연산자를 사용하여, 이 조건을 만족하는 사람들을 제외하고,

title이 'sales support agent'이고 city가 'Calgary'인 사람만 결과로 반환합니다.

 

SELECT LAST_NAME, FIRST_NAME, TITLE, CITY
FROM EMPLOYEE
WHERE NOT (title = 'sales support agent' AND city = 'Calgary');

SELECT LAST_NAME, FIRST_NAME, TITLE, CITY
FROM EMPLOYEE
WHERE NOT (title <> 'sales support agent' OR city <> 'Calgary');

 

title이 sales support agent라도 city가 Calgary이면 safe

city가 Calgary라도 title이 sales support agent이면 safe

그냥 어느쪽이든 하나라도 해당하는게 아니면 됨.

즉 둘 다 해당하는 사람만 제외

---

 

연산자 관련 문제

⭕논리 연산자는 SQL에 명시된 순서와 상관 없이 () -> NOT -> AND -> OR 순으로 처리

⭕NULL과의 연산(+, - , *, /)의 결과는 항상 NULL이다.

❌조건식에서 컬럼명은 반드시 좌측에 위치해야 한다. 

 => 조건식에서 컬럼명은 반드시 좌측에 위치해야 한다.

---

 

SQL연산자

연산자	의미	예시
BETWEEN A AND B	A와 B의 사이(A, B포함)	where col between 1 and 10
LIKE '비교 문자열'	비교문자열 포함 '%'는 문자열 '_'는 하나의 문자	where col like '방탄%' where col like '%소년단' where col like '%탄소년%' where col like '방_소%'
IN (LIST)	LIST 중 하나와 일치	where col in (1, 3, 5)
IS NULL	NULL 값	where col is not null

 

 

SQL의 ESCAPE 

'_' or '%'기호포함 문자 검색시 ESCAPE 지정

 

SQL의 ESCAPE 키워드는 LIKE 연산자에서 특수 문자를 이스케이프(escape) 처리할 때 사용됩니다. 기본적으로 LIKE 연산자는 와일드카드 문자인 %(임의의 문자열)와 _(임의의 단일 문자)를 사용합니다. 그러나 검색할 문자열에 이러한 와일드카드 문자가 포함된 경우, 이를 문자 그대로 검색하려면 이스케이프 처리가 필요합니다.

 

특수문자 %를 검색하고 싶을 때

SELECT * 
FROM table_name
WHERE col LIKE '100#%' ESCAPE '#';

 

---

⭐ 7. SQL문의 실행 순서

 

5 `SELECT`

1 `FROM`

2 `WHERE`

3 `GROUP BY` 

4 `HAVING` 

6 `ORDER BY`

 

FROM - WHERE - GROUP BY - HAVING - SELECT - ORDER BY

 

HAVING절 VS WHERE절

WHERE 절은 Group by 전에 수행

HAVING 절은 CROUP BY 절 이후에 수행되기 때문에 그룹핑 후에 가능한 집계 함수로 조건 부여

 

// 2021년 7월에 주문된 제품 코드별로 주문 수를 계산하고
// 그 주문 수가 1000개 이상인 제품 코드들만 결과로 반환하는 쿼리

SELECT PRODUCT_CODE,
       COUNT(ORDER_CNT) AS ORDER_CNT
FROM ORDERED_PRODUCT
WHERE ORDER_DATE BETWEEN '20210701' AND '20210731'
GROUP BY PRODUCT_CODE
HAVING COUNT(ORDER_CNT) >= 1000;

 

HAVING절은 논리적으로 SELECT 절 전에 수행되기 때문에 SELECT 절에 명시되지 않은 집계함수로도 조건을 부여할 수 있다. 주의할 점은 WHERE 절을 사용해도 되는 조건까지 HAVING 절에 써버리면 성능상 불리할 수 있다는 것이다(수행시 오류가 나지 않는다). 왜냐하면 WHERE 절에서 필터링이 선행되어야 GROUP BY를 할 데이터량이 줄어들기 때문이다. GROUP BY는 비교적 많은 비용이 드는 작업이므로 수행 전에 데이터량을 최소로 줄여놓는것이 바람직하다. (정미나, 유선배 SQL개발자 과외노트)

 

---

ORDER BY 절에서 ALIAS 안써도 되는 이유

ORDER BY 절에서ALIAS(별칭)써도 되지만 기존 컬럼명을 그대로 사용해도 되는 이유는 SQL에서 ORDER BY 절이 SELECT 절에서 반환되는 컬럼의 이름을 그대로 사용할 수 있기 때문이다.

 

ORDER BY COL1 DESC, COL2, COL3 DESC; 

 

COL1 내림차순, COL2 는 오름차순, COL3 내림차순으로 정렬

옵션 생략시 기본적으로 ASC (오름차순) 정렬

---

8. 집계함수

⭐`COUNT(*)` 전체 Row를 Count (NULL값 포함)

`COUNT(컬럼)` 컬럼값이 NULL인 Row 제외하고 Count

`COUNT(DISTINCT 컬럼)` 컬럼값이 NULL이 아닌 Row에서 중복 제거하고 Count

 

나머지 집계함수에서 NULL값은 제외됨

평균 구할 때도 null 값 제외하고 더해서 null 값인 행은 빼고 나머지 행 개수로 나눔

---

⭐ Oracle은 NULL 값이 최대값이다.

---

⭐ UNION 과 UNION ALL 차이

union은 중복값 제거

union all 은 중복값도 반환

https://bigtechinterviews.com/sql-union-vs-union-all/