SQL ISO vs SQL ANSI

Desde la versión 9i, Oracle incluyó el estandar SQL 92 en sus sentencias SQL. A continuación se verán algunos ejemplos de dicha adición y su correspondiente sintanxis tanto en el standar ISO como en el SQL 92 incluído.

Para esto, se tomarán como ejemplo los datos del conocido usuario SCOTT; principalmente las tablas EMP (Employees) y DEPT (Departments)

SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17/12/80        800                    20
      7499 ALLEN      SALESMAN        7698 20/02/81       1600        300         30
      7521 WARD       SALESMAN        7698 22/02/81       1250        500         30
      7566 JONES      MANAGER         7839 02/04/81       2975                    20
      7654 MARTIN     SALESMAN        7698 28/09/81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01/05/81       2850                    30
      7782 CLARK      MANAGER         7839 09/06/81       2450                    10
      7788 SCOTT      ANALYST         7566 19/04/87       3000                    20
      7839 KING       PRESIDENT            17/11/81       5000                    10
      7844 TURNER     SALESMAN        7698 08/09/81       1500          0         30
      7876 ADAMS      CLERK           7788 23/05/87       1100                    20

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
      7900 JAMES      CLERK           7698 03/12/81        950                    30
      7902 FORD       ANALYST         7566 03/12/81       3000                    20
      7934 MILLER     CLERK           7782 23/01/82       1300                    10
      8000 GOMEZ      SALESMAN        7698 15/04/06       1500

15 rows selected.

SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- ---------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

Inner Join

SQL 92

ISO

SELECT <column_name>, <column_name>
FROM <table_name alias> INNER JOIN <table_name alias>
ON <alias.column_name> = <alias.column_name>

SQL> SELECT ename, job, dname
  2  FROM emp e INNER JOIN dept d
  3  ON e.deptno = d.deptno;

ENAME      JOB       DNAME
---------- --------- --------------
CLARK      MANAGER   ACCOUNTING
KING       PRESIDENT ACCOUNTING
MILLER     CLERK     ACCOUNTING
SMITH      CLERK     RESEARCH
ADAMS      CLERK     RESEARCH
FORD       ANALYST   RESEARCH
SCOTT      ANALYST   RESEARCH
JONES      MANAGER   RESEARCH
ALLEN      SALESMAN  SALES
BLAKE      MANAGER   SALES
MARTIN     SALESMAN  SALES

JAMES      CLERK     SALES
TURNER     SALESMAN  SALES
WARD       SALESMAN  SALES

14 rows selected.

SELECT <column_name>, <column_name>
FROM <table_name alias>, <table_name alias>
WHERE <alias.column_name> = <alias.column_name>

SQL> SELECT ename, job, dname
  2  FROM emp e, dept d
  3  WHERE e.deptno = d.deptno;

ENAME      JOB       DNAME
---------- --------- --------------
CLARK      MANAGER   ACCOUNTING
KING       PRESIDENT ACCOUNTING
MILLER     CLERK     ACCOUNTING
SMITH      CLERK     RESEARCH
ADAMS      CLERK     RESEARCH
FORD       ANALYST   RESEARCH
SCOTT      ANALYST   RESEARCH
JONES      MANAGER   RESEARCH
ALLEN      SALESMAN  SALES
BLAKE      MANAGER   SALES
MARTIN     SALESMAN  SALES
JAMES      CLERK     SALES
TURNER     SALESMAN  SALES
WARD       SALESMAN  SALES

14 rows selected.

Left Outer Join

SQL 92

ISO

SELECT <column_name>, <column_name>
FROM <table_name alias> LEFT OUTER JOIN <table_name alias>
ON <alias.column_name> = <alias.column_name>

SQL> SELECT ename, job, dname
  2  FROM emp e LEFT OUTER JOIN dept d
  3  ON e.deptno = d.deptno;

ENAME      JOB       DNAME
---------- --------- --------------
MILLER     CLERK     ACCOUNTING
KING       PRESIDENT ACCOUNTING
CLARK      MANAGER   ACCOUNTING
FORD       ANALYST   RESEARCH
ADAMS      CLERK     RESEARCH
SCOTT      ANALYST   RESEARCH
JONES      MANAGER   RESEARCH
SMITH      CLERK     RESEARCH
JAMES      CLERK     SALES
TURNER     SALESMAN  SALES
BLAKE      MANAGER   SALES
MARTIN     SALESMAN  SALES
WARD       SALESMAN  SALES
ALLEN      SALESMAN  SALES
GOMEZ      SALESMAN

15 rows selected.

SELECT <column_name>, <column_name>
FROM <table_name alias>, <table_name alias>
WHERE <alias.column_name = <alias.column_name>
AND <alias.column_name> = <alias.column_name> (+);

SQL> SELECT ename, job, dname
  2  FROM emp e, dept d
  3  WHERE e.deptno = d.deptno(+);

ENAME      JOB       DNAME
---------- --------- --------------
MILLER     CLERK     ACCOUNTING
KING       PRESIDENT ACCOUNTING
CLARK      MANAGER   ACCOUNTING
FORD       ANALYST   RESEARCH
ADAMS      CLERK     RESEARCH
SCOTT      ANALYST   RESEARCH
JONES      MANAGER   RESEARCH
SMITH      CLERK     RESEARCH
JAMES      CLERK     SALES
TURNER     SALESMAN  SALES
BLAKE      MANAGER   SALES
MARTIN     SALESMAN  SALES
WARD       SALESMAN  SALES
ALLEN      SALESMAN  SALES
GOMEZ      SALESMAN

15 rows selected.

Right Outer Join

SQL 92

ISO

SQL> SELECT ename, job, dname
  2  FROM emp e RIGHT OUTER JOIN dept d
  3  ON e.deptno = d.deptno;

ENAME      JOB       DNAME
---------- --------- --------------
CLARK      MANAGER   ACCOUNTING
KING       PRESIDENT ACCOUNTING
MILLER     CLERK     ACCOUNTING
SMITH      CLERK     RESEARCH
ADAMS      CLERK     RESEARCH
FORD       ANALYST   RESEARCH
SCOTT      ANALYST   RESEARCH
JONES      MANAGER   RESEARCH
ALLEN      SALESMAN  SALES
BLAKE      MANAGER   SALES
MARTIN     SALESMAN  SALES
JAMES      CLERK     SALES
TURNER     SALESMAN  SALES
WARD       SALESMAN  SALES
                     OPERATIONS

15 rows selected.

SELECT <column_name>, <column_name>
FROM <table_name alias>, <table_name alias>
WHERE <alias.column_name> (+) = <alias.column_name>;

SQL> SELECT ename, job, dname
  2  FROM emp e, dept d
  3  WHERE e.deptno(+) = d.deptno;

ENAME      JOB       DNAME
---------- --------- --------------
CLARK      MANAGER   ACCOUNTING
KING       PRESIDENT ACCOUNTING
MILLER     CLERK     ACCOUNTING
SMITH      CLERK     RESEARCH
ADAMS      CLERK     RESEARCH
FORD       ANALYST   RESEARCH
SCOTT      ANALYST   RESEARCH
JONES      MANAGER   RESEARCH
ALLEN      SALESMAN  SALES
BLAKE      MANAGER   SALES
MARTIN     SALESMAN  SALES
JAMES      CLERK     SALES
TURNER     SALESMAN  SALES
WARD       SALESMAN  SALES
                     OPERATIONS

15 rows selected.

Full Outer Join

SQL 92

ISO

SELECT <column_name>, <column_name>
FROM <table_name alias> FULL OUTER JOIN <table_name alias>
ON <alias.column_name> = <alias.column_name>

SQL> SELECT ename, job, dname
  2  FROM emp e FULL OUTER JOIN dept d
  3  ON e.deptno = d.deptno;

ENAME      JOB       DNAME
---------- --------- --------------
MILLER     CLERK     ACCOUNTING
KING       PRESIDENT ACCOUNTING
CLARK      MANAGER   ACCOUNTING
FORD       ANALYST   RESEARCH
ADAMS      CLERK     RESEARCH
SCOTT      ANALYST   RESEARCH
JONES      MANAGER   RESEARCH
SMITH      CLERK     RESEARCH
JAMES      CLERK     SALES
TURNER     SALESMAN  SALES
BLAKE      MANAGER   SALES
MARTIN     SALESMAN  SALES
WARD       SALESMAN  SALES
ALLEN      SALESMAN  SALES
GOMEZ      SALESMAN
                     OPERATIONS

16 rows selected.

SELECT <column_name>, <column_name>
FROM <table_name alias>, <table_name alias>
WHERE <alias.column_name = <alias.column_name>
AND <alias.column_name> = <alias.column_name> (+)
UNION
SELECT <column_name>, <column_name>
FROM <table_name alias>, <table_name alias>
WHERE <alias.column_name> (+) = <alias.column_name>;

SQL> SELECT ename, job, dname
  2  FROM emp e, dept d
  3  WHERE e.deptno = d.deptno(+)
  4  UNION
  5  SELECT ename, job, dname
  6  FROM emp e, dept d
  7  WHERE e.deptno(+) = d.deptno;

ENAME      JOB       DNAME
---------- --------- --------------
ADAMS      CLERK     RESEARCH
ALLEN      SALESMAN  SALES
BLAKE      MANAGER   SALES
CLARK      MANAGER   ACCOUNTING
FORD       ANALYST   RESEARCH
GOMEZ      SALESMAN
JAMES      CLERK     SALES
JONES      MANAGER   RESEARCH
KING       PRESIDENT ACCOUNTING
MARTIN     SALESMAN  SALES
MILLER     CLERK     ACCOUNTING

ENAME      JOB       DNAME
---------- --------- --------------
SCOTT      ANALYST   RESEARCH
SMITH      CLERK     RESEARCH
TURNER     SALESMAN  SALES
WARD       SALESMAN  SALES
                     OPERATIONS

16 rows selected.

Natural Join

SQL 92

ISO

SELECT adult_name, child_name
FROM parents NATURAL JOIN child;

SQL> SELECT ename, job, dname
  2  FROM emp e NATURAL JOIN  dept d;

ENAME      JOB       DNAME
---------- --------- --------------
CLARK      MANAGER   ACCOUNTING
KING       PRESIDENT ACCOUNTING
MILLER     CLERK     ACCOUNTING
SMITH      CLERK     RESEARCH
ADAMS      CLERK     RESEARCH
FORD       ANALYST   RESEARCH
SCOTT      ANALYST   RESEARCH
JONES      MANAGER   RESEARCH
ALLEN      SALESMAN  SALES
BLAKE      MANAGER   SALES
MARTIN     SALESMAN  SALES

ENAME      JOB       DNAME
---------- --------- --------------
JAMES      CLERK     SALES
TURNER     SALESMAN  SALES
WARD       SALESMAN  SALES

14 rows selected.

Self Join

SQL 92

ISO

SELECT p1.last_name, t1.title_name, t2.title_name
FROM person p1 INNER JOIN title t1
ON p1.title_1 = t1.title_abbrev,
person p2 INNER JOIN title t2
ON p2.title_2 = t2.title_abbrev;

SQL> SELECT e.ename, e.job, e1.ename
  2  FROM emp e INNER JOIN emp e1
  3  ON e.mgr = e1.empno;

ENAME      JOB       ENAME
---------- --------- ----------
SCOTT      ANALYST   JONES
FORD       ANALYST   JONES
ALLEN      SALESMAN  BLAKE
WARD       SALESMAN  BLAKE
JAMES      CLERK     BLAKE
TURNER     SALESMAN  BLAKE
MARTIN     SALESMAN  BLAKE
GOMEZ      SALESMAN  BLAKE
MILLER     CLERK     CLARK
ADAMS      CLERK     SCOTT
JONES      MANAGER   KING

ENAME      JOB       ENAME
---------- --------- ----------
CLARK      MANAGER   KING
BLAKE      MANAGER   KING
SMITH      CLERK     FORD

14 rows selected.

SELECT <column_name>, <column_name>
FROM <table_name alias>, <table_name alias>, <table_name alias>
WHERE <alias .column_name> = < alias.column_name>
AND <alias .column_name> = <alias.column_name>;

SQL> SELECT e.ename, e.job, e1.ename
  2  FROM emp e, emp e1
  3  WHERE e.mgr = e1.empno;

ENAME      JOB       ENAME
---------- --------- ----------
SCOTT      ANALYST   JONES
FORD       ANALYST   JONES
ALLEN      SALESMAN  BLAKE
WARD       SALESMAN  BLAKE
JAMES      CLERK     BLAKE
TURNER     SALESMAN  BLAKE
MARTIN     SALESMAN  BLAKE
GOMEZ      SALESMAN  BLAKE
MILLER     CLERK     CLARK
ADAMS      CLERK     SCOTT
JONES      MANAGER   KING
CLARK      MANAGER   KING
BLAKE      MANAGER   KING
SMITH      CLERK     FORD

14 rows selected.