|
오라클
|
오라클
|
PostgreSQL
|
MSSQL
|
|
Dual
|
SELECT 1 FROM DUAL
|
SELECT 1
|
SELECT 1
|
|
NULL 함수
|
SELECT NVL(param1, param2) DAUL
|
NVL(param1, param2)
|
ISNULL(param1, param2)
|
|
|
|
coalesce(name, 'No Data') -- 지정 문자열
|
|
|
|
|
coalesce(name, null) -- null
|
|
|
|
|
coalesce(name, '{}')::json as name -- json
|
|
|
|
|
coalesce(name, id) -- 컬럼
|
|
|
DECODE
|
DECODE
|
CASE WHEN THEN ELSE END
|
IIF( boolean_expression, true_value, false_value )
|
|
|
CASE WHEN THEN ELSE END
|
|
CASE WHEN THEN ELSE END
|
|
데이터 타입
|
NUMBER
|
NUMERIC
|
FLOAT
|
|
|
NUMBER
|
|
NUMERIC
|
|
|
VARCHAR2
|
VARCHAR
|
VARCHAR
|
|
|
TIMESTAMP
|
CURRENT_TIMESTAMP
|
DATETIME
|
|
|
SYSDATE
|
NOW()
|
GETDATE()
|
|
시퀀스
|
시퀀스명.NEXTVAL
|
NEXTVAL('시퀀스명')
|
SELECT NEXT VALUE FOR SEQ
|
|
ROWNUM
|
ROWNUM
|
WHERE 절 : LIMIT 1
|
SELECT 절 : ROW_NUMBER() OVER(ORDER BY 필드 asc)
|
|
|
|
SELECT 절 : ROW_NUMBER() OVER() AS ROWNUM
|
|
|
INSTR() 함수
|
INSTR()
|
position()
|
CHARINDEX("찾을문자", "문자열", "시작위치")
|
|
JOIN
|
SELECT b.col1, a.col2
FROM base_table b, attributes a
WHERE b.id=a.b_id(+);
|
SELECT b.col1, a.col2
FROM base_table b
LEFT JOIN attributes a ON b.id = a.b_id;
|
SELECT a.empno
, a.ename
, a.job
, a.mgr
, a.deptno
, b.dname
FROM emp AS a
LEFT OUTER JOIN dept AS b
ON a.deptno = b.deptno
|