Oracle 19c SQL

  1. Home
  2. Docs
  3. Oracle 19c SQL
  4. 4 Joins in Oracle
  5. 4.8 ANSI Join

4.8 ANSI Join

Oracle database complies to the SQL standards established by the American National Standards Institute (ANSI) and the International Organization for Standardization (ISO). What that means is, Oracle supports SQL standards defined by ANSI/ISO along with its own version of SQL.

Any RDBMS compliant with ANSI or ISO, they have to provide the features defined by these organizations. You can port your code to any database, if your code is written in ANSI format. Typically database migration job becomes easier for developers as ANSI format code can run in any ANSI compliant database with very minimal changes.

What are ANSI compliant Joins in Oracle?


Oracle INNER JOIN clause

An inner join (also called a simple join) is a join that returns only rows that satisfy the join condition. Inner joins are either equi joins or non-equi joins or self join.

INNER JOIN keyword sometimes is referred with only JOIN. So, you can remember like INNER keyword is optional in a join syntax.

Syntax:

SELECT 
  FROM Table1
 [INNER] JOIN Table2 ON join_condition;

INNER keyword is optional, but is good practice to keep INNER keyword for a inner join.

Q : Get the employee name, designation, salary, department id, department name and grade of employee?

Ans:

SELECT e.Ename, e.Job Designation, e.Sal, d.Deptno, d.Dname, s.Grade
  FROM Emp e
 INNER JOIN Dept d ON e.deptno = d.deptno
 INNER JOIN SalGrade s ON e.Sal BETWEEN s.LoSal AND s.HiSal;

ENAME      DESIGNATION        SAL     DEPTNO DNAME         GRADE
---------- ----------- ---------- ---------- ------------ ------
KING       PRESIDENT         5000         10 ACCOUNTING        5
SCOTT      ANALYST           3000         20 RESEARCH          4
FORD       ANALYST           3000         20 RESEARCH          4
JONES      MANAGER           2975         20 RESEARCH          4
BLAKE      MANAGER           2850         30 SALES             4
CLARK      MANAGER           2450         10 ACCOUNTING        4
ALLEN      SALESMAN          1600         30 SALES             3
TURNER     SALESMAN          1500         30 SALES             3
MILLER     CLERK             1300         10 ACCOUNTING        2
WARD       SALESMAN          1250         30 SALES             2
MARTIN     SALESMAN          1250         30 SALES             2
ADAMS      CLERK             1100         20 RESEARCH          1
JAMES      CLERK              950         30 SALES             1
SMITH      CLERK              800         20 RESEARCH          1

14 rows selected.

Inner join can be used to perform self joins. Multiple tables can be part of an inner join query, where each table must be preceded with INNER JOIN keyword.

Q : Get the employee name, employee’s department name and their manager name and manager’s department?

Ans:

--Innser join as Self join
SELECT
    e.ename  "Employee Name", 
    ed.dname "Employee Department", 
    m.ename  "Manager Name", 
    md.dname "Manager Department"
  FROM Emp e
 INNER JOIN Dept ed ON e.deptno = ed.deptno
 INNER JOIN Emp m   ON e.mgr = m.empno
 INNER JOIN Dept md ON m.deptno = md.deptno;

Employee Name     Employee Department  Manager Name     Manager Department
----------------- -------------------- ---------------- --------------------
MILLER            ACCOUNTING           CLARK            ACCOUNTING
CLARK             ACCOUNTING           KING             ACCOUNTING
JONES             RESEARCH             KING             ACCOUNTING
BLAKE             SALES                KING             ACCOUNTING
SMITH             RESEARCH             FORD             RESEARCH
ADAMS             RESEARCH             SCOTT            RESEARCH
FORD              RESEARCH             JONES            RESEARCH
SCOTT             RESEARCH             JONES            RESEARCH
JAMES             SALES                BLAKE            SALES
TURNER            SALES                BLAKE            SALES
MARTIN            SALES                BLAKE            SALES
WARD              SALES                BLAKE            SALES
ALLEN             SALES                BLAKE            SALES

13 rows selected.

Q : Get the employee name, employee’s department name, employee grade and their manager name, manager’s department and manager’s grade?

Ans:

SELECT
    e.ename  "Employee", 
    ed.dname "Emp Department", 
    es.Grade "Emp Grade",
    m.ename  "Manager", 
    md.dname "Mgr Department",
    ms.Grade "Mgr Grade"
  FROM Emp e
 INNER JOIN Dept ed ON e.deptno = ed.deptno
 INNER JOIN SalGrade es ON e.Sal BETWEEN es.LoSal AND es.HiSal
 INNER JOIN Emp m   ON e.mgr = m.empno
 INNER JOIN Dept md ON m.deptno = md.deptno
 INNER JOIN SalGrade ms ON m.Sal BETWEEN ms.LoSal AND ms.HiSal;

Employee   Emp Department  Emp Grade Manager   Mgr Department Mgr Grade
---------- -------------- ---------- --------- -------------- ----------
JONES      RESEARCH                4 KING      ACCOUNTING          5
BLAKE      SALES                   4 KING      ACCOUNTING          5
CLARK      ACCOUNTING              4 KING      ACCOUNTING          5
ADAMS      RESEARCH                1 SCOTT     RESEARCH            4
SMITH      RESEARCH                1 FORD      RESEARCH            4
SCOTT      RESEARCH                4 JONES     RESEARCH            4
FORD       RESEARCH                4 JONES     RESEARCH            4
MARTIN     SALES                   2 BLAKE     SALES               4
WARD       SALES                   2 BLAKE     SALES               4
ALLEN      SALES                   3 BLAKE     SALES               4
TURNER     SALES                   3 BLAKE     SALES               4
JAMES      SALES                   1 BLAKE     SALES               4
MILLER     ACCOUNTING              2 CLARK     ACCOUNTING          4

13 rows selected.

USING Clause with Oracle Inner Join

USING clause provides you another way for specifying join condition when both tables common columns have same names. Common columns need not be qualified.

Syntax:

SELECT <Column List>
  FROM Table1
 INNER JOIN Table2 USING (Common Column(s));
SELECT *
  FROM Dept d1
 INNER JOIN Dept d2 ON d1.deptno = d2.deptno;

    DEPTNO DNAME          LOC         DEPTNO     DNAME          LOC
---------- -------------- ----------- ---------- -------------- ----------
    10     ACCOUNTING     NEW YORK    10         ACCOUNTING     NEW YORK
    20     RESEARCH       DALLAS      20         RESEARCH       DALLAS  
    30     SALES          CHICAGO     30         SALES          CHICAGO 
    40     OPERATIONS     BOSTON      40         OPERATIONS     BOSTON  
SELECT *
  FROM Dept d1
 INNER JOIN Dept d2 USING (deptno);

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

Common columns need not be qualified when join is performed through USING clause.

--Error! When common column is qualified with table alias

SELECT e.Ename, e.Job, e.Sal, d.Deptno, d.Dname, d.Loc
  FROM Emp e 
 INNER JOIN Dept d
 USING (Deptno);

ERROR at line 1:
ORA-25154: column part of USING clause cannot have qualifier

Oracle CROSS JOIN clause

CROSS join is ANSI form for Cartesian product. A Cartesian product means that every row of one table is joined to all the rows of the other table. If “Table1” has “M” rows and “Table2” has “N” rows, then CROSS join results “M x N” rows.

CROSS JOIN does not have the ON clause for a join predicate. If you provide ON clause with Cross join, Oracle raises error.

WHERE clause is acceptable in a CROSS JOIN. SO, you can write CROSS JOIN as equi join, non-equi join or cartesian product.

Syntax:

SELECT 
  FROM Table1
 CROSS JOIN Table2;
--Error! CROSS join does not recognizes ON clause

SELECT e.Ename, e.Job, e.Sal, d.Deptno, d.Dname
  FROM Emp e
 CROSS JOIN Dept d ON e.deptno = d.deptno;
 CROSS JOIN Dept d ON e.deptno = d.deptno
                   *
ERROR at line 3:
ORA-00933: SQL command not properly ended
--Cross join as a Cartesian product

SELECT count(*)
  FROM Dept d1
 CROSS JOIN Dept d2;

  COUNT(*)
----------
	16
--Cross join as a Cartesian product

SELECT count(*)
  FROM Emp e
 CROSS JOIN Dept d;

  COUNT(*)
----------
	56

Q : Can you specify WHERE clause in Cross join?

Ans: Yes, WHERE clause predicates are accepted in a Cross join. You can specify a join predicate in a Cross join and the Cross join can behave like an Inner join.

Cross Join with WHERE Condition

--Cross join acting like a inner join or equi join

SELECT count(*)
  FROM Dept d1
 CROSS JOIN Dept d2
 WHERE d1.Deptno = d2.Deptno;

  COUNT(*)
----------
	4
--Cross join acting like a inner join or equi join

SELECT count(*)
  FROM Emp e
 CROSS JOIN Dept d
 WHERE e.Deptno = d.Deptno;

  COUNT(*)
----------
	14
--Cross join acting like a inner join or equi join

SELECT e.Ename, e.Deptno, d.Dname, d.Loc
  FROM Emp e
 CROSS JOIN Dept d
 WHERE e.Deptno = d.Deptno;

ENAME          DEPTNO DNAME          LOC
---------- ---------- -------------- -------------
CLARK              10 ACCOUNTING     NEW YORK
MILLER             10 ACCOUNTING     NEW YORK
KING               10 ACCOUNTING     NEW YORK
FORD               20 RESEARCH       DALLAS
SCOTT              20 RESEARCH       DALLAS
JONES              20 RESEARCH       DALLAS
SMITH              20 RESEARCH       DALLAS
ADAMS              20 RESEARCH       DALLAS
WARD               30 SALES          CHICAGO
MARTIN             30 SALES          CHICAGO
TURNER             30 SALES          CHICAGO
JAMES              30 SALES          CHICAGO
ALLEN              30 SALES          CHICAGO
BLAKE              30 SALES          CHICAGO

14 rows selected.
--Cross join acting like a non-equi join

SELECT e.Ename, e.Job, e.Sal, s.Grade
  FROM Emp e
 CROSS JOIN Salgrade s
 WHERE e.Sal BETWEEN s.Losal AND s.Hisal;

ENAME      JOB              SAL      GRADE
---------- --------- ---------- ----------
SMITH      CLERK            800          1
JAMES      CLERK            950          1
ADAMS      CLERK           1100          1
MARTIN     SALESMAN        1250          2
WARD       SALESMAN        1250          2
MILLER     CLERK           1300          2
TURNER     SALESMAN        1500          3
ALLEN      SALESMAN        1600          3
CLARK      MANAGER         2450          4
BLAKE      MANAGER         2850          4
JONES      MANAGER         2975          4
FORD       ANALYST         3000          4
SCOTT      ANALYST         3000          4
KING       PRESIDENT       5000          5

14 rows selected.
--Cross join acting like a Self join

SELECT
    e.ename  "Employee Name", 
    ed.dname "Employee Department", 
    m.ename  "Manager Name", 
    md.dname "Manager Department"
  FROM Emp e
 CROSS JOIN Dept ed
 CROSS JOIN Emp m
 CROSS JOIN Dept md
 WHERE e.deptno = ed.deptno
   AND e.mgr = m.empno
   AND m.deptno = md.deptno;

Employee Name     Employee Department  Manager Name     Manager Department
----------------- -------------------- ---------------- --------------------
MILLER            ACCOUNTING           CLARK            ACCOUNTING
CLARK             ACCOUNTING           KING             ACCOUNTING
JONES             RESEARCH             KING             ACCOUNTING
BLAKE             SALES                KING             ACCOUNTING
SMITH             RESEARCH             FORD             RESEARCH
ADAMS             RESEARCH             SCOTT            RESEARCH
FORD              RESEARCH             JONES            RESEARCH
SCOTT             RESEARCH             JONES            RESEARCH
JAMES             SALES                BLAKE            SALES
TURNER            SALES                BLAKE            SALES
MARTIN            SALES                BLAKE            SALES
WARD              SALES                BLAKE            SALES
ALLEN             SALES                BLAKE            SALES

13 rows selected.

NOTE:

You can use CROSS JOIN as Cartesian product, equi join or even for non-equi join. But such code writing is not recommended and not used in industry.

CROSS JOIN is only used for specifying Cartesian product to avoid confusion. If you want to use equi-join or non-equi join then you can only use JOIN keyword.


Oracle NATURAL JOIN clause

SELECT Ename, Deptno, Dname, Loc
  FROM Emp NATURAL JOIN Dept;

ENAME          DEPTNO DNAME          LOC
---------- ---------- -------------- -------------
CLARK              10 ACCOUNTING     NEW YORK
MILLER             10 ACCOUNTING     NEW YORK
KING               10 ACCOUNTING     NEW YORK
FORD               20 RESEARCH       DALLAS
SCOTT              20 RESEARCH       DALLAS
JONES              20 RESEARCH       DALLAS
SMITH              20 RESEARCH       DALLAS
ADAMS              20 RESEARCH       DALLAS
WARD               30 SALES          CHICAGO
MARTIN             30 SALES          CHICAGO
TURNER             30 SALES          CHICAGO
JAMES              30 SALES          CHICAGO
ALLEN              30 SALES          CHICAGO
BLAKE              30 SALES          CHICAGO

14 rows selected.

NATURAL JOIN does not accept column qualify for common column. Except from common column all other columns can be qualified. If common column is qualified, Oracle raises error “ORA-25155: column used in NATURAL join cannot have qualifier”.

SELECT Ename, Dept.Deptno, Dname, Loc
  FROM Emp NATURAL JOIN Dept;

ERROR at line 1:
ORA-25155: column used in NATURAL join cannot have qualifier 
--Qualifying non-common columns in NATURAL JOIN

SELECT E.Ename, E.Job, E.Sal, Deptno, D.Dname, D.Loc
  FROM Emp E NATURAL JOIN Dept D;

ENAME      JOB              SAL     DEPTNO DNAME          LOC
---------- --------- ---------- ---------- -------------- -------------
CLARK      MANAGER         2450         10 ACCOUNTING     NEW YORK
MILLER     CLERK           1300         10 ACCOUNTING     NEW YORK
KING       PRESIDENT       5000         10 ACCOUNTING     NEW YORK
FORD       ANALYST         3000         20 RESEARCH       DALLAS
SCOTT      ANALYST         3000         20 RESEARCH       DALLAS
JONES      MANAGER         2975         20 RESEARCH       DALLAS
SMITH      CLERK            800         20 RESEARCH       DALLAS
ADAMS      CLERK           1100         20 RESEARCH       DALLAS
WARD       SALESMAN        1250         30 SALES          CHICAGO
MARTIN     SALESMAN        1250         30 SALES          CHICAGO
TURNER     SALESMAN        1500         30 SALES          CHICAGO
JAMES      CLERK            950         30 SALES          CHICAGO
ALLEN      SALESMAN        1600         30 SALES          CHICAGO
BLAKE      MANAGER         2850         30 SALES          CHICAGO

14 rows selected.

When Common column is discussed in Natural Join make sure common column means column name and range of data in both the tables are same as because Natural join concept defines common column like this. If range of data is same in both the tables but, column names are different, then Natural Join is just a Cartesian product.

NATURAL JOIN does not work on common data principle as we are not mentioning any Join condition while implementing NATURAL JOIN. NATURAL JOIN first searches for the common column (column with same name). If not found, then NATURAL JOIN is just a Cartesian Product. If found, NATURAL JOIN is performed same an inner join for matching records. If more than one column names are same in both tables, then NATURAL JOIN result is shown for matching records i.e. records displayed where combined values for the common columns are matching.

SELECT count(*) 
  FROM Emp E NATURAL JOIN SalGrade S;

  COUNT(*)
----------
	70

NATURAL JOIN concept is simple. Oracle optimizer searches for the common column that is column with same name, no matter columns have different data type. Then it performs inner join as internally it works for equality operator.

SELECT * FROM T1;

         A          B          C
---------- ---------- ----------
         1          1          1
         2          2          2
         3          3          3
         4          4          4

SELECT * FROM T2;

         A          E          F B
---------- ---------- ---------- --
         1          1          1 1
         3          3          3
         5          2          5
         4          4          6 4
         7          7          7

SELECT * 
  FROM  T1 NATURAL JOIN T2;

         A B           C          E          F
---------- -- ---------- ---------- ----------
         1 1           1          1          1
         4 4           4          4          6

NATURAL JOIN always works with two different tables. If we attempt NATURAL JOIN of the same table then optimizer will throw ORA-00918: column ambiguously defined error. But we can perform NATURAL JOIN of the same table using table alias.

SELECT * 
FROM Dept NATURAL JOIN Dept;

ERROR at line 1:
ORA-00918: column ambiguously defined 


SELECT * 
FROM Dept D1 NATURAL JOIN Dept D2;

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

LEFT OUTER JOIN

A left outer join is performed using the LEFT OUTER JOIN or LEFT JOIN keyword.

SELECT E.Empno, 
       E.Ename, 
       NVL(TO_CHAR(M.Empno), 'NIL') AS ManagerNo, 
       NVL(M.Ename, 'No Manager')   AS Manager
  FROM Emp E
  LEFT OUTER JOIN Emp M ON E.Mgr = M.Empno;

     EMPNO ENAME      MANAGERNO  MANAGER
---------- ---------- ---------- ----------
      7369 SMITH      7902       FORD
      7566 JONES      7839       KING
      7782 CLARK      7839       KING
      7788 SCOTT      7566       JONES
      7839 KING       NIL        No Manager
      7876 ADAMS      7788       SCOTT
      7902 FORD       7566       JONES
      7934 MILLER     7782       CLARK
      7499 ALLEN      7698       BLAKE
      7521 WARD       7698       BLAKE
      7654 MARTIN     7698       BLAKE
      7844 TURNER     7698       BLAKE
      7900 JAMES      7698       BLAKE
      7698 BLAKE      7839       KING

14 rows selected.

RIGHT OUTER JOIN

A right outer join is performed using the RIGHT OUTER JOIN or RIGHT JOIN keyword.

SELECT 
      NVL(E.Ename, 'No Employee') Ename, 
      NVL(E.Job, 'NA') Job, 
      NVL(TO_CHAR(E.Sal), 'NA') Salary, 
      D.Deptno, 
      D.Dname
  FROM Emp e
 RIGHT OUTER JOIN Dept d ON E.Deptno = D.Deptno;

ENAME       JOB       SALARY        DEPTNO DNAME
----------- --------- ---------- --------- ---------------
KING        PRESIDENT 5000              10 ACCOUNTING
CLARK       MANAGER   2450              10 ACCOUNTING
MILLER      CLERK     1410              10 ACCOUNTING
SCOTT       ANALYST   3000              20 RESEARCH
JONES       MANAGER   2975              20 RESEARCH
SMITH       CLERK     910               20 RESEARCH
FORD        ANALYST   3000              20 RESEARCH
ADAMS       CLERK     1100              20 RESEARCH
ALLEN       SALESMAN  1600              30 SALES
WARD        SALESMAN  1250              30 SALES
MARTIN      SALESMAN  1250              30 SALES
JAMES       CLERK     1060              30 SALES
TURNER      SALESMAN  1500              30 SALES
BLAKE       MANAGER   2850              30 SALES
No Employee NA        NA                40 OPERATIONS

15 rows selected.

FULL OUTER JOIN

A full outer join is performed using the FULL OUTER JOIN or FULL JOIN keyword.

INSERT INTO Emp(Empno, Ename, Job, Sal)
VALUES(1234, 'SAMPLE', 'HR', 2650);

1 row created.


SELECT 
      NVL(E.Ename, 'No Employee')            AS Ename, 
      NVL(E.Job, 'NA')                       AS Job, 
      NVL(TO_CHAR(E.Sal), 'NA')              AS Salary, 
      NVL(TO_CHAR(D.Deptno), 'Not Assigned') AS Deptno, 
      NVL(DName, 'No Department')            AS Dname
  FROM Emp e
  FULL OUTER JOIN Dept d ON E.Deptno = D.Deptno;

ENAME       JOB       SALARY     DEPTNO          DNAME
----------- --------- ---------- --------------- --------------------
KING        PRESIDENT 5000       10              ACCOUNTING
BLAKE       MANAGER   2850       30              SALES
CLARK       MANAGER   2450       10              ACCOUNTING
JONES       MANAGER   2975       20              RESEARCH
SCOTT       ANALYST   3000       20              RESEARCH
FORD        ANALYST   3000       20              RESEARCH
SMITH       CLERK     800        20              RESEARCH
ALLEN       SALESMAN  1600       30              SALES
WARD        SALESMAN  1250       30              SALES
MARTIN      SALESMAN  1250       30              SALES
TURNER      SALESMAN  1500       30              SALES
ADAMS       CLERK     1100       20              RESEARCH
JAMES       CLERK     950        30              SALES
MILLER      CLERK     1300       10              ACCOUNTING
SAMPLE      HR        2650       Not Assigned    No Department
No Employee NA        NA         40              OPERATIONS

16 rows selected.


ROLLBACK;

Rollback complete.
Was this article helpful to you? Yes No

How can we help?