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’;