GRETEST/LEAST VS MAX/MIN 비교 (오라클)

MIN, MAX 예제

--GREATEST, LEAST VS MAX, MIN TEST
WITH GREATET_TEST AS (SELECT 1111 AS COL1 FROM DUAL 
UNION ALL
SELECT 2222 AS COL2 FROM DUAL
), GREATEST_TEST_1 AS ( SELECT 3333 AS COL1 
UNION ALL SELECT 4444 AS COL2 FROM DUAL)
SELECT MAX(A.COL1), MAX(B.COL1), MIN(A.COL1), MIN(A.COL1) -- 각각 한개의 컬럼만을 비교할 수 있다.
FROM GREATEST_TEST A, GREATEST_TEST_1 B
GROUP BY A.COL1, B.COL1

결과값▼

MAX(A.COL1)	MAX(B.COL1)	MIN(A.COL1)	MIN(B.COL1)
2222	4444	2222	4444
1111	4444	1111	4444
1111	3333	1111	3333
2222	3333	2222	3333

 

GREATEST/LEAST 예제

--GREATEST, LEAST VS MAX, MIN TEST
WITH GREATET_TEST AS (SELECT 1111 AS COL1 FROM DUAL 
UNION ALL
SELECT 2222 AS COL2 FROM DUAL
), GREATEST_TEST_1 AS ( SELECT 3333 AS COL1 
UNION ALL SELECT 4444 AS COL2 FROM DUAL)
SELECT A.COL1, B.COL1
,GREATEST(A.COL1, B.COL1) --여러개의 컬럼값의 최대값을 비교할 수 있다.
,LEAST(A.COL1, B.COL1) --상동
FROM GREATEST_TEST A, GREATEST_TEST_1 B
-- END OF TEST

결과값▼

COL1	COL1_1	GREATEST(A.COL1,B.COL1)	LEAST(A.COL1,B.COL1)
1111	3333	3333	1111
1111	4444	4444	1111
2222	3333	3333	2222
2222	4444	4444	2222