Search

NULL ๊ด€๋ จ ํ•จ์ˆ˜

NULL ๊ด€๋ จ ํ•จ์ˆ˜

NVL( ์ธ์ž1, ์ธ์ž2 )
์ธ์ž1์ด NULL ์ด๋ฉด, ์ธ์ž2๋ฅผ ๋ฐ˜ํ™˜ํ•˜๋Š” ํ•จ์ˆ˜
NVL2( ์ธ์ž1, ์ธ์ž2, ์ธ์ž3 )
์ธ์ž1์ด NULL์ด ์•„๋‹ˆ๋ฉด ์ธ์ž2, NULL ์ด๋ฉด ์ธ์ž3์„ ๋ฐ˜ํ™˜ํ•˜๋Š” ํ•จ์ˆ˜
COALESCE( ์ธ์ž1, ์ธ์ž2, ... )
์ธ์ž๋“ค ์ค‘ NULL ์ด ์•„๋‹Œ ์ฒซ๋ฒˆ์งธ ๊ฐ’์„ ๋ฐ˜ํ™˜ํ•˜๋Š” ํ•จ์ˆ˜ ex) ์ธ์ž1 : NULL, ์ธ์ž2 : NULL, ์ธ์ž3 : (๊ฐ’) ์ถœ๋ ฅ : ์ธ์ž3
LNNVL(์กฐ๊ฑด์‹)
์กฐ๊ฑด์‹์˜ ๊ฒฐ๊ณผ๊ฐ€ i) TRUE --> FALSE, ii) NULL, FALSE --> TRUE ๋กœ ๋ณ€ํ™˜ํ•˜๋Š” ํ•จ์ˆ˜
NULLIF( ์ธ์ž1, ์ธ์ž2 )
์ธ์ž1๊ณผ ์ธ์ž2๊ฐ€ ๊ฐ™์œผ๋ฉด NULL ์ธ์ž1๊ณผ ์ธ์ž2๊ฐ€ ๋‹ค๋ฅด๋ฉด ์ธ์ž1์„ ๋ฐ˜ํ™˜ํ•˜๋Š” ํ•จ์ˆ˜

HR ์Šคํ‚ค๋งˆ ์‹ค์Šต์ฝ”๋“œ

COALESCE()

SELECT employee_id ์‚ฌ์›๋ฒˆํ˜ธ ,first_name ์ด๋ฆ„ ,COALESCE( salary + (salary * commission_pct), salary, 0 ) ์ตœ์ข…๊ธ‰์—ฌ ,commission_pct FROM employees;
SQL
๋ณต์‚ฌ

LNNVL(์กฐ๊ฑด์‹)

SELECT employee_id, commission_pct FROM employees WHERE LNNVL(commission_pct >= 0.2);
SQL
๋ณต์‚ฌ
SELECT employee_id, commission_pct FROM employees WHERE NVL(commission_pct, 0) < 0.2;
SQL
๋ณต์‚ฌ
SELECT employee_id, commission_pct FROM employees WHERE commission_pct < 0.2;
SQL
๋ณต์‚ฌ

NULLIF()

SELECT employee_id ,TO_CHAR(start_date, 'YYYY') start_year ,TO_CHAR(end_date, 'YYYY') end_year ,NULLIF(TO_CHAR(end_date, 'YYYY'), TO_CHAR(start_date, 'YYYY')) nullif_year FROM job_history;
SQL
๋ณต์‚ฌ