🧑💻/SQL
SQL) Oracle vs SQL Server 명령어 전체 비교 (2)
유리맥
2021. 3. 24. 00:04
반응형
이전 포스트를 참고해주세요! 😄
[🧑💻/SQL] - SQL) Oracle vs SQL Server 명령어 전체 비교 (1) - DDL, DML, ...
집합연산자
| Oracle Database | SQL Server | |
| 합집합 (중복 행 제외, 정렬 O) | UNION | UNION |
| 합집합 (중복 행 포함, 정렬 X) | UNION ALL | UNION ALL |
| 교집합 | INTERSECT | INTERSECT |
| 차집합 | MINUS | EXCEPT |
| 곱집합 | CROSS JOIN | CROSS JOIN |
조인
| Oracle Database | SQL Server | |
| 조건에서 동일한 값이 있는 행만 반환 | 테이블1 INNER JOIN 테이블2 ON|USING ( 조건 ) | 테이블1 INNER JOIN 테이블2 ON ( 조건 ) |
| 동일한 이름을 갖는 모든 컬럼에 대해 EQUI JOIN | 테이블1 NATRUAL JOIN 테이블2 ON|USING ( 조건 ) | 없음 |
| 좌측 테이블로 데이터를 읽어 온다. 조건이 맞지 않으면 NULL로 채움. | 테이블1 LEFT [OUTER] JOIN 테이블2 ON|USING ( 조건 ) | 테이블1 LEFT [OUTER] JOIN 테이블2 ON ( 조건 ) |
| 우측 테이블로 데이터를 읽어 온다. 조건이 맞지 않으면 NULL로 채움 | 테이블1 RIGHT [OUTER] JOIN 테이블2 ON|USING ( 조건 ) | 테이블1 RIGHT [OUTER] JOIN 테이블2 ON ( 조건 ) |
| 좌우측 테이블의 모든 데이터를 읽어 온다. 중복 행 삭제 | 테이블1 FULL [OUTER] JOIN 테이블2 ON|USING ( 조건 ) | 테이블1 FULL [OUTER] JOIN 테이블2 ON ( 조건 ) |
| M*N 건의 데이터 조합을 반환 | 테이블1 CROSS JOIN 테이블2 | 테이블1 CROSS JOIN 테이블2 |
계층형 질의
| Oracle Database | |
| 계층 구조 전개의 시작(루트) 위치 지정 | START WITH |
| 다음에 전개될 자식 데이터 지정 | CONNECT BY |
| 부모 -> 자식 순방향 전개 자식 -> 부모 역방향 전개 |
PRIOR 자식 = 부모 PRIOR 부모 = 자식 |
| 동일한 데이터 전개 X | NOCYCLE |
| 동일 LEVEL 사이 정렬 | ORDER SIBLINGS BY |
| 루트는 1, 하위 데이터는 1씩 증가 | LEVEL |
| 리프 데이터면 1, 아니면 0 | CONNECT_BY_ISLEAF |
| 사이클 발생하면 1, 아니면 0 | CONNECT_BY_ISCYCLE |
| 루트부터 현재까지 경로 표시 | SYS_CONNECT_ BY_PATH |
| 루트 표시 | CONNECT_BY_ROOT |
예제로 보는 서브쿼리
| Oracle Databse | SQL Server | |
| 단일 행 서브쿼리 (결과 1건 이하) =, <, <=, >, >=, <> |
SELECT PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버 FROM PLAYER WHERE HEIGHT <= (SELECT AVG(HEIGHT) FROM PLAYER) ORDER BY PLAYER_NAME; |
|
| 다중 행 서브쿼리 (결과 여러 건 이하) IN, ALL, ANY, SOME, EXISTS |
SELECT REGION_NAME 연고지명, TEAM_NAME 팀명, E_TEAM_NAME 영문팀명 FROM TEAM WHERE TEAM_ID IN (SELECT TEAM_ID FROM PLAYER WHERE PLAYER_NAME = '정현수') ORDER BY TEAM_NAME; |
|
| 다중 컬럼 서브쿼리 (결과 여러 컬럼 반환) | SELECT TEAM_ID 팀코드, PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키 FROM PLAYER WHERE (TEAM_ID, HEIGHT) IN (SELECT TEAM_ID, MIN(HEIGHT) FROM PLAYER GROUP BY TEAM_ID) ORDER BY TEAM_ID, PLAYER_NAME; |
없음 |
그룹 함수
| Oracle Databse | SQL Server | |
| N+1 레벨의 SubTotal 생성 / 순서 중요 | ROLLUP(컬럼1[, 컬럼2, ...]) | ROLLUP(컬럼1[, 컬럼2, ...]) |
| 결합 가능한 모든 값에 대해 다차원 집계 생성 | CUBE(컬럼1[, 컬럼2, ...]) | CUBE(컬럼1[, 컬럼2, ...]) |
| 개별 집계 생성 | GROUPING SETS(컬럼1[, 컬럼2, ...]) | GROUPING SETS(컬럼1[, 컬럼2, ...]) |
| ROLLUP, CUBE, GROUPING SETS에 의한 SubTotal이면 1, 아니면 0 | GROUPING(컬럼) | GROUPING(컬럼) |
예제로 보는 윈도우 함수
| Oracle Database | SQL Server | |
| -M ~ +N 범위 | RANGE BETWEEN M PRECEDING AND N FOLLOWING | |
| 앞 M행 ~ 뒤 N행 범위 | ROWS BETWEEN M PRECEDING AND N FOLLOWING | |
| 1,2,2,4 동일한 값에 대해 동일한 순서를 부여 | SELECT JOB, ENAME, SAL, RANK( ) OVER (ORDER BY SAL DESC) ALL_RANK, RANK( ) OVER (PARTITION BY JOB ORDER BY SAL DESC) JOB_RANK FROM EMP; | |
| 1,2,2,3 동일한 순위를 하나의 등수로 간주 | SELECT JOB, ENAME, SAL, DENSE_RANK( ) OVER (ORDER BY SAL DESC) DR FROM EMP; | |
| 1,2,3,4 동일한 값이라도 고유한 순위 부여 | SELECT JOB, ENAME, SAL, ROW_NUMBER() OVER (ORDER BY SAL DESC) RN FROM EMP; | |
| 파티션별 카운트를 인수 값으로 N등분한 결과 | SELECT ENAME, SAL, NTILE(4) OVER (ORDER BY SAL DESC) as QUAR_TILE FROM EMP | |
| 파티션별 윈도우의 합 | SELECT MGR, ENAME, SAL, SUM(SAL) OVER (PARTITION BY MGR) MGR_SUM FROM EMP; |
|
| 파티션별 윈도우의 최대값 | SELECT MGR, ENAME, SAL, MAX(SAL) OVER (PARTITION BY MGR) as MGR_MAX FROM EMP; | |
| 파티션별 윈도우의 최소값 | SELECT MGR, ENAME, HIREDATE, SAL, MIN(SAL) OVER(PARTITION BY MGR ORDER BY HIREDATE) as MGR_MIN FROM EMP; | |
| 조건에 맞는 데이터의 평균값 | SELECT MGR, ENAME, HIREDATE, SAL, ROUND (AVG(SAL) OVER (PARTITION BY MGR ORDER BY HIREDATE ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)) as MGR_AVG FROM EMP; | |
| 조건에 맞는 데이터의 개수 | SELECT ENAME, SAL, COUNT(*) OVER (ORDER BY SAL RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING) as SIM_CNT FROM EMP; | |
| 파티션별 윈도우의 첫번째 값 | SELECT DEPTNO, ENAME, SAL, FIRST_VALUE(ENAME) OVER (PARTITION BY DEPTNO ORDER BY SAL DESC ROWS UNBOUNDED PRECEDING) as DEPT_RICH FROM EMP; | 없음 |
| 파티션별 윈도우의 마지막 값 | SELECT DEPTNO, ENAME, SAL, LAST_VALUE(ENAME) OVER (PARTITION BY DEPTNO ORDER BY SAL DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) as DEPT_POOR FROM EMP; | 없음 |
| 파티션별 윈도우의 이전 N번째 행의 값 | SELECT ENAME, HIREDATE, SAL, LAG(SAL) OVER (ORDER BY HIREDATE) as PREV_SAL FROM EMP WHERE JOB = 'SALESMAN'; | 없음 |
| 파티션별 윈도우의 이후 N번째 행의 값 | SELECT ENAME, HIREDATE, LEAD(HIREDATE, 1) OVER (ORDER BY HIREDATE) as "NEXTHIRED" FROM EMP; | 없음 |
| 파티션 내 전체 합에 대한 행별 컬럼 값의 백분율. >0, <=1 | SELECT ENAME, SAL, ROUND(RATIO_TO_REPORT(SAL) OVER (), 2) as R_R FROM EMP WHERE JOB = 'SALESMAN'; | 없음 |
| 파티션별 행의 순서 백분율. >=0, <=1 | SELECT DEPTNO, ENAME, SAL, PERCENT_RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) as P_R FROM EMP; | 없음 |
| 현재 행보다 작거나 같은 건수에 대한 누적 백분율. >0, <=1 | SELECT DEPTNO, ENAME, SAL, CUME_DIST() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) as CUME_DIST FROM EMP; | 없음 |
DCL
| Oracle Databse | SQL Server | |
| 시스템 권한 부여 | GRANT CREATE TABLE | USER | ... TO 계정; | GRANT CREATE TABLE | USER | ... TO 계정; |
| 객체 권한 부여 | GRANT SELECT | INSERT | DELETE | UPDATE ON 테이블 TO 계정; |
GRANT SELECT | INSERT | DELETE | UPDATE ON 테이블 TO 계정; |
| ROLE 사용하여 권한을 한번에 부여 | e.g.) CREATE ROLE LOGIN_TABLE; GRANT CREATE SESSION, CREATE TABLE TO LOGIN_TABLE; GRANT LOGIN_TABLE TO JISUNG; |
dbcreater, sysadmin, db_owner 등 제공되는 ROLE 멤버에 참여하는 방식 |
| 시스템 권한 회수 | REVOKE CREATE TABLE | USER | ... FROM 계정; | REVOKE CREATE TABLE | USER | ... FROM 계정; |
| 객체 권한 회수 | REVOKE SELECT | INSERT | DELETE | UPDATE ON 테이블 FROM 계정; | REVOKE SELECT | INSERT | DELETE | UPDATE ON 테이블 FROM 계정; |
| 타 계정 테이블 접근 시 | 계정 이름으로 접근 e.g.) SELECT * FROM PJS.MENU; |
스키마 이름으로 접근 e.g.) SELECT * FROM dbo.MENU |
절차형 SQL
| PL/SQL (Oracle Database) | T-SQL (SQL Server) |
| CREATE Procedure Procedure_name ( argument1 mode data_type1, argument2 mode date_type2, ... ... ) IS AS ... ... BEGIN ... ... EXCEPTION ... ... END; DROP Procedure Procedure_name; |
CREATE Procedure schema_name.Procedure_name @parameter1 data_type1 mode, @parameter2 date_type2 mode, ... ... WITH AS ... ... BEGIN ... ... ERROR 처리 ... ... END; DROP Procedure schema_name.Procedure_name; |
| DECLARE 선언부(변수, 상수) BEGIN 실행부 EXCEPTION 예외 처리부 END |
DECLARE 선언부(변수, 상수) BEGIN 실행부 ERROR 처리 예외 처리부 END |
2편 포스팅 마무리를 시험 끝나고 나서야 하게됐네요. 정신없이 바쁜 한 주 였어서.. ㅠㅠ
SQLD 시험 보기 전 마지막 핸드폰으로 스르륵 보는 것을 추천합니다. 💯💯💯
🌈 참고
반응형