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?
- INNER JOIN or JOIN
- CROSS JOIN
- NATURAL JOIN
- LEFT OUTER JOIN (or simply LEFT JOIN)
- RIGHT OUTER JOIN (or simply RIGHT JOIN)
- FULL OUTER JOIN (or simply FULL JOIN)
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:
SELECTFROM 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:
SELECTFROM 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.