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 FROM clause.

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.

The query SELECT list can have any or all columns from any or all of the tables from the FROMclause.

The common columns (columns having same name) within the tables must be qualified with the table names or table alias in the query.

Join Syntax:

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 SELECT List.
• The WHERE clause 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

Guidelines:

• When writing a SELECT statement 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.