Oracle JOIN is used to retrieve data from multiple tables, views or materialized views. In SQL, a JOIN is performed whenever multiple tables appear in queries
Data from multiple tables specified in the
FROM clause are retrieved based on a rule or condition known as Join Condition. Join condition is a condition specified in the query
WHERE clause, which compares two columns each from different table.
WHERE clause can also contain other conditions that refer to columns from any one table.
SELECT list can have any or all columns from any or all of the tables from the
The common columns (columns having same name) within the tables must be qualified with the table names or table alias in the query.
To execute a JOIN…
- Oracle combines pairs of rows, each containing one row from each table, for which the JOIN CONDITION evaluates to true.
- The columns in the JOIN CONDITION needn’t be part of
WHEREclause of JOIN query can also contain other conditions, that refer to columns of only one table.
To execute a JOIN of three or more tables,
- Oracle first joins two of tables based on the join conditions, comparing these columns and then joins the result to another table.
- The Oracle optimizer determines the order in which ORACLE should join the tables based on-
- Given JOIN condition(s)
- INDEXES upon the tables
- STATISTICS for the tables
- When writing a
SELECTstatement that joins tables, precede the column name with the table name for clarity and enhance database access.
- To JOIN ‘n’ tables together, we need a minimum of ‘n-1’ JOIN conditions. The rule doesn’t apply, if the table contains a concatenated Primary key.
- If the same column name appears in more than one table, the column name must be prefixed with the table name. Otherwise Oracle displays “ORA-00918: column ambiguously defined ” error. Watch..
- All other non-common columns mayn’t be prefixed, but is a good coding habit and increases database performance.
-—Error! Column prefix is missing for Deptno SELECT E.Empno, E.Ename, E.Sal, Deptno, Dname FROM Emp E, Dept D WHERE E.Deptno = D.Deptno; Error at Command Line : 1 Column : 33 Error report – SQL Error: ORA-00918: column ambiguously defined 00918. 00000 – “column ambiguously defined”
“deptno” is the common column (Column having same name) in both the tables and must be prefixed with table name.
SELECT E.Empno, E.Ename, E.Sal, D.Deptno, D.Dname FROM Emp E, Dept D WHERE E.Deptno = D.Deptno;
Q : Can you apply filters along with join condition?
Ans: Yes, You can include other filter conditions along with join condition in a query.
SELECT E.Empno, E.Ename, E.Sal, D.Deptno, D.Dname FROM Emp E, Dept D WHERE E.Deptno = D.Deptno AND E.Sal > 2000 AND E.Ename LIKE '%S%'; EMPNO ENAME SAL DEPTNO DNAME ---------- ---------- ---------- ---------- -------------- 7566 JONES 2975 20 RESEARCH 7788 SCOTT 3000 20 RESEARCH
If you miss a join condition when getting data from multiple tables, then also the query executes without errors.
SELECT E.Empno, E.Ename, E.Sal, D.Deptno, D.Dname FROM Emp E, Dept D WHERE E.Deptno = 10 AND D.Loc = 'CHICAGO';
WHERE E.Deptno = 10 is not a JOIN condition because it does not include two columns, each from different tables. Similarly,
D.Loc = 'CHICAGO' is also not a Join condition.
DName = 'SALES' AND D.Loc = 'CHICAGO' are two filter conditions.