Joins

           A join is a query that combines rows from two or more tables, views, or materialized views. Oracle Database performs a join whenever multiple tables appear in the FROM clause of the query. The select list of the query can select any columns from any of these tables. If any two of these tables have a column name in common, then you must qualify all references to these columns throughout the query with table names to avoid ambiguity.

Join Conditions:

            Most join queries contain at least one join condition, either in the FROM clause or in the WHERE clause. The join condition compares two columns, each from a different table. To execute a join, Oracle Database combines pairs of rows, each containing one row from each table, for which the join condition evaluates to TRUE. The columns in the join conditions need not also appear in the select list.
            To execute a join of three or more tables, Oracle first joins two of the tables based on the join conditions comparing their columns and then joins the result to another table based on join conditions containing columns of the joined tables and the new table. Oracle continues this process until all tables are joined into the result. The optimizer determines the order in which Oracle joins tables based on the join conditions, indexes on the tables, and, any available statistics for the tables.
            In WHERE clause that contains a join condition can also contain other conditions that refer to columns of only one table. These conditions can further restrict the rows returned by the join query.

Note:

            You cannot specify LOB columns in the WHERE clause if the WHERE clause contains the join condition. The use of LOBs in WHERE clauses are also subject to other restrictions.

Cartesian Products:

            If two tables in a join query have no join condition, then Oracle Database returns their Cartesian product. Oracle combines each row of one table with each row of the other. A Cartesian product always generates many rows and is rarely useful. For example, the Cartesian product of two tables, each with 100 rows, has 10,000 rows. Always include a join condition unless you specifically need a Cartesian product. If a query joins three or more tables and you do not specify a join condition for a specific pair, then the optimizer may choose a join order that avoids producing an intermediate Cartesian product.
Ex:
  • Select Empno, Ename, Dname, Loc From Emp, Dept;
  • Select Empno, Ename, Sal, Grade From Emp, Salgrade;
  • Select Empno, Ename, Dname, Loc, Salgrade From Emp, Dept, Salgrade;

Equi Joins OR Simple Joins OR Inner Joins:

Ø  An Equijoin is a join with a join condition containing an equality operator.
Ø  It Combines rows that have equivalent values for the specified columns.
Ø  The total size of columns in the Equijoin condition in a single table may be limited to the size of a data block minus some overhead.
Ø  The size of the data block is specified by the initialization parameter DB_BLOCK_SIZE.

Qualifying Ambiguous Column Names:

Ø  The Names of the column names should be qualified in the where clause, with the table name to avoid ambiguity.
Ø  If there are no common column names between the two tables, the qualification is not necessary but it is better.

Ex:

  • Select
            Emp.Empno Empno,
            Emp.Ename Ename,
            Emp.Deptno Deptno,
            Dept.Deptno Deptno,
            Dept.Dname Dname,
            Dept.Loc Loc
From Emp, Dept Where Emp.Deptno = Dept.Deptno;

  • Select
Empno, Ename,Emp.Deptno, Loc From Emp, Dept where Emp.Deptno = Dept.Deptno And Job = UPPER(’manager’);
  • Select
            Empno, Ename, Sal * 12 Annsal, Emp.Deptno, Loc From Emp, Dept
            Where Emp.Deptno = Dept.Deptno;

Using Table Aliases:

Ø  Table aliases can be used instead of original table names.
Ø  A Table alias gives an alternate name for the existing queried table.
Ø  Table aliases help in keeping the sql code smaller, hence using less memory.
Ø  The table alias is specified in the FROM clause.

Guidelines:

Ø  A table alias can be up to 30 characters in length.
Ø  If a table alias is used for a particular table name in the FROM clause, then that table alias must be substituted for the table name throughout the select statement.
Ø  A table alias should be meaningful and should be maintained as short as possible.
Ø  A table alias is valid only for the current select statement only.

Ex:


  • Select E.Empno, E.Ename, D.Deptno, D.Dname From Emp E, Dept D where E.deptno = D.Deptno;
  • Select E.Ename, E.job, D.Deptno, D.Dname, D.Loc From Emp E, Dept D where E.deptno = D.Deptno And E.Job IN(’ANALYST’, ’MANAGER’);
  • Select E.Ename, E.Job, D.Dname, D.Loc From Emp E, Dept D where E.Deptno = D.Deptno And D.Dname <>  ’SALES’;