SQL - Oracle vs ANSI SQL

Oracle SQL과 ANSI SQL의 차이랄까요 ? 사실 별차이는 구조의 차이 일까요????

오라클 쿼리는 ANSI로도 표현을 할 수 있습니다. 대신, 회사나, 사람마다의 선호의 차이가 있지 않을까요?

성능상에 차이가 있는지는 저도 잘 모르겠습니다.

이 글을 보시는 분들은 예제 데이터가 없으므로, 형태의 구조만 보시는 걸 권장 합니다.

--ORIGIN
SELECT a.employee_id , a.emp_name , b.department_id, b.department_name
 FROM EMPLOYEES a, DEPARTMENTS b;
 --ANSI
SELECT a.employee_id , a.emp_name , b.department_id, b.department_name
 FROM EMPLOYEES a
      CROSS JOIN
      DEPARTMENTS b; 
--기존 오라클 문법
SELECT a.employee_id
      ,a.emp_name
      ,b.department_id
      ,b.department_name
 FROM employees a
      ,departments b
 WHERE a.department_id = b.department_id
   AND a.hire_date >= TO_DATE('2003-01-01','YYYY-MM-DD');
--ANSI SQL
SELECT a.employee_id
      ,a.emp_name
      ,b.department_id
      ,b.department_name
  FROM employees a
  INNER JOIN
       departments b
    ON (a.department_id = b.department_id)
  WHERE a.hire_date >= TO_DATE('2003-01-01', 'YYYY-MM-DD');
  
--외부조인의 경우
--기존 oracle
SELECT a.employee_id
      ,a.emp_name
      ,b.job_id
      ,b.department_id
 FROM employees a
      ,job_history b
 WHERE a.employee_id = b.employee_id(+)
   AND a.department_id = b.department_id(+);
--ANSI 
SELECT a.employee_id
      ,a.emp_name
      ,b.job_id
      ,b.department_id
  FROM employees a
  LEFT OUTER JOIN -- 먼저 명시된 테이블 기준으로 LEFT/RIGHT 붙임. 
       job_history b
   ON (a.employee_id = b.employee_id 
       AND 
       a.department_id = b.department_id
       );

--연관성 없는 서브 쿼리
SELECT count(*)
 FROM employees
 WHERE salary >= (
                    SELECT AVG(salary)
                      FROM employees
                  );
SELECT count(*)
 FROM employees
 WHERE department_id IN ( SELECT department_id
                           FROM departments
                           WHERE parent_id IS NULL
                        );
SELECT employee_id
      ,emp_name
      ,job_id
 FROM employees
 WHERE ( employee_id, job_id ) IN ( SELECT employee_id
                                           ,job_id
                                     FROM job_history
                                   );
UPDATE employees
  SET salary = ( SELECT AVG(salary)
                   FROM employees
                );
DELETE employees
 WHERE salary >= (
                  SELECT AVG(salary)
                   FROM employees
                  );
ROLLBACK;

SELECT a.department_id
      ,a.department_name
 FROM departments a
 WHERE EXISTS ( 
                SELECT 1 
                 FROM job_history b
                 WHERE a.department_id = b.department_id
              );
SELECT a.employee_id,
      (
       SELECT b.emp_name
         FROM employees b
        WHERE a.employee_id = b.employee_id ) AS emp_name,
        a.department_id,
      ( 
       SELECT b.department_name
        FROM departments b
        WHERE a.department_id = b.department_id ) AS dep_name
  FROM job_history a;
  
SELECT a.department_id
      ,a.department_name
 FROM departments a
 WHERE EXISTS ( SELECT 1
                 FROM employees b
                WHERE a.department_id = b.department_id
                 AND b.salary > ( SELECT AVG(salary)
                                   FROM employees )
              );
SELECT department_id , AVG(salary)
 FROM employees a
WHERE department_id IN ( SELECT department_id
                          FROM  departments
                          WHERE parent_id = 90)
GROUP BY department_id;

무단 수정 및 배포는 금지합니다.   모든 내용은 본 블로그 운영자가 정리한 내용입니다.   참조한 정보에 대해서는 출처를 남기고 있습니다.  틀린 내용 / 오류가 포함된 내용은 댓글로 남겨주세요.  choiseungho0822@gmail.com 보내주셔도 됩니다.  Seungdols Wiki 운영중입니다.