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.
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 toTRUE. 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.
IA 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 WHEREclauses
is also subject to other restrictions.
Equijoins
An equijoin is a join with a join condition containing
an equality operator. An equijoin combines rows that have equivalent values for
the specified columns. Depending on the internal algorithm the optimizer chooses to
execute the join, the total size of the 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 a data block is specified by the initialization parameter
DB_BLOCK_SIZE.
Self Joins
A self join is a join of a table to itself. This table appears
twice in the
FROM clause and is followed by table aliases that qualify column
names in the join condition. To perform a self join, Oracle Database combines and returns
rows of the table that satisfy the join condition.
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.
Inner Joins
An inner join (sometimes
called a simple
join) is a join of two or more tables that returns only those rows
that satisfy the join condition.
Outer Joins
An outer join extends
the result of a simple join. An outer join returns all rows that satisfy the
join condition and also returns some or all of those rows from one table for
which no rows from the other satisfy the join condition.
·
To write a query that performs an outer join of tables A and B
and returns all rows from A (a left outer join), use the
LEFT [OUTER] JOIN syntax in theFROM clause, or apply the outer join operator (+) to all columns of B
in the join condition in the WHERE clause. For all rows in A that have no matching rows in B,
Oracle Database returns null for any select list expressions containing columns
of B.
·
To write a query that performs an outer join of tables A and B
and returns all rows from B (a right outer join), use the
RIGHT [OUTER] JOIN syntax in the FROM clause, or apply the
outer join operator (+) to all columns of A in the join condition in the WHERE clause. For all rows in
B that have no matching rows in A, Oracle returns null for any select list
expressions containing columns of A.
·
To write a query that performs an outer join and returns all
rows from A and B, extended with nulls if they do not satisfy the join
condition (a full
outer join), use the
FULL [OUTER] JOIN syntax in the FROM clause.
You cannot compare a column with a
subquery in the
WHERE clause of any outer join, regardless which form you specify.
You
can use outer joins to fill gaps in sparse data. Such a join is called a partitioned outer
join and is formed
using the query_partition_clause of thejoin_clause syntax. Sparse data is data that does not have rows for all
possible values of a dimension such as time or department. For example, tables
of sales data typically do not have rows for products that had no sales on a
given date. Filling data gaps is useful in situations where data sparsity
complicates analytic computation or where some data might be missed if the
sparse data is queried directly.
Oracle recommends that you use the FROM clause OUTER JOIN syntax rather
than the Oracle join operator. Outer join queries that use the Oracle join operator
(+) are subject to the following rules and restrictions, which do not apply to
the FROM clause OUTER JOIN syntax:
- You cannot specify the (+)
operator in a query block that also contains FROM clause
join syntax.
- The (+) operator can appear
only in the WHERE clause or, in the context of left-correlation
(when specifying the TABLE clause) in the FROM clause,
and can be applied only to a column of a table or view.
- If A and B are joined by
multiple join conditions, then you must use the (+) operator in all of
these conditions. If you do not, then Oracle Database will return only the
rows resulting from a simple join, but without a warning or error to
advise you that you do not have the results of an outer join.
- The (+) operator does not
produce an outer join if you specify one table in the outer query and the
other table in an inner query.
- You cannot use the (+) operator
to outer-join a table to itself, although self joins are valid. For
example, the following statement is not valid:
·
-- The following statement
is not valid:
·
SELECT empno, mgr
·
FROM emp
·
WHERE emp.mgr(+) = emp.empno;
However, the following self join is valid:
SELECT e1.empno, e1.mgr,
e2.empno
FROM emp e1, emp e2
WHERE e1.mgr(+) = e2.empno;
The (+) operator can be applied only to a column, not to an
arbitrary expression. However, an arbitrary expression can contain one or more
columns marked with the (+) operator.
- A WHERE condition
containing the (+) operator cannot be combined with another condition
using the OR logical operator.
- A WHERE condition
cannot use the IN comparison condition to compare a column marked
with the (+) operator with an expression.
If the WHERE clause contains a condition that
compares a column from table B with a constant, then the (+) operator must be
applied to the column so that Oracle returns the rows from table A for which it
has generated nulls for this column. Otherwise Oracle returns only the results
of a simple join.
In a query that performs outer joins of more than two pairs of
tables, a single table can be the null-generated table for only one other
table. For this reason, you cannot apply the (+) operator to columns of B in
the join condition for A and B and the join condition for B and C.
Antijoins
An antijoin returns rows from the left side
of the predicate for which there are no corresponding rows on the right side of
the predicate. It returns rows that fail to match (
NOT IN) the subquery on the right side.
Semijoins
A semijoin returns rows that match an
EXISTS subquery without
duplicating rows from the left side of the predicate when multiple rows on the
right side satisfy the criteria of the subquery.
Semijoin and antijoin transformation
cannot be done if the subquery is on an
OR branch of the WHERE clause.
Example :
SQL> create table
suppliers (sid number primary key ,sname varchar2(15));
Table created.
SQL>
create table orders(oid number primary
key,sid number references suppliers(sid),orderdate date);
Table created.
SQL> select *
from suppliers;
SID SNAME
----------
---------------
11 ibm
22 hewlett packard
33 microsoft
44 sony
SQL> select *
from orders;
OID SID ORDERDATE
----------
---------- ---------
10 11 12-AUG-10
20 33 09-NOV-11
44 11 07-SEP-09
55 13-DEC-12
Join opearation
Simple join or inner join or equi join
SQL> select
s.sid,s.sname,o.orderdate
from
suppliers s,orders o
where
s.sid=o.sid;
SID SNAME ORDERDATE
----------
--------------- ---------
11 ibm 12-AUG-10
33 microsoft 09-NOV-11
11 ibm 07-SEP-09
SQL> select
s.sid,s.sname,o.orderdate
from
suppliers s
join
orders o
on s.sid=o.sid;
SID SNAME ORDERDATE
----------
--------------- ---------
11 ibm 12-AUG-10
33 microsoft 09-NOV-11
11 ibm 07-SEP-09
Outer joins
Left join
SQL> select
s.sid,s.sname,o.orderdate
from suppliers s
left join
orders o
on s.sid=o.sid;
SID SNAME ORDERDATE
----------
--------------- ---------
11 ibm 12-AUG-10
33 microsoft 09-NOV-11
11 ibm 07-SEP-09
44 sony
22 hewlett Packard
SQL> select
s.sid,s.sname,o.orderdate
from
suppliers s,orders o
where
s.sid=o.sid(+);
SID SNAME ORDERDATE
----------
--------------- ---------
11 ibm 12-AUG-10
33 microsoft 09-NOV-11
11 ibm 07-SEP-09
44 sony
22 hewlett Packard
Right join
SQL> select
s.sid,s.sname,o.orderdate
from
suppliers s
right
join
orders o
on s.sid=o.sid;
SID SNAME ORDERDATE
----------
--------------- ---------
11 ibm 07-SEP-09
11 ibm 12-AUG-10
33 microsoft 09-NOV-11
13-DEC-12
SQL> select
s.sid,s.sname,o.orderdate
from
suppliers s , orders o
where
s.sid(+)=o.sid;
SID SNAME ORDERDATE
----------
--------------- ---------
11 ibm 07-SEP-09
11 ibm 12-AUG-10
33 microsoft 09-NOV-11
13-DEC-12
Note:
In some databases LEFT JOIN is called LEFT OUTER
JOIN,
RIGHT JOIN
is called RIGHT OUTER JOIN.
Full join
SQL>
select s.sid,s.sname,o.orderdate
from
suppliers s
full join
orders o
on s.sid=o.sid;
SID SNAME ORDERDATE
----------
--------------- ---------
11 ibm 12-AUG-10
33 microsoft 09-NOV-11
11 ibm 07-SEP-09
44 sony
22 hewlett packard
13-DEC-12
Anti join
SQL>
select e.ename,d.loc
from emp
e,dept d
where e.deptno=d.deptno;
ENAME LOC
----------
-------------
SMITH DALLAS
ALLEN CHICAGO
WARD CHICAGO
JONES DALLAS
MARTIN CHICAGO
BLAKE CHICAGO
CLARK NEW YORK
SCOTT DALLAS
KING NEW YORK
TURNER CHICAGO
ADAMS DALLAS
ENAME LOC
----------
-------------
JAMES CHICAGO
FORD DALLAS
MILLER NEW YORK
14
rows selected.
SQL>
SELECT ename FROM emp
WHERE deptno NOT IN
(SELECT deptno FROM dept
WHERE loc = 'NEW YORK')
ORDER BY ename;
ENAME
----------
ADAMS
ALLEN
BLAKE
FORD
JAMES
JONES
MARTIN
SCOTT
SMITH
TURNER
WARD
11
rows selected.
SQL>
select ename ,deptno from emp;
ENAME DEPTNO
----------
----------
SMITH 20
ALLEN 30
WARD 30
JONES 20
MARTIN 30
BLAKE 30
CLARK 10
SCOTT 20
KING 10
TURNER 30
ADAMS 20
ENAME DEPTNO
----------
----------
JAMES 30
FORD 20
MILLER 10
14
rows selected.
SQL>
select deptno from dept;
DEPTNO
----------
10
20
30
40
SQL>
SELECT ename FROM emp
WHERE deptno NOT IN
(SELECT deptno FROM dept);
no
rows selected
Semi join
SQL>
select * from emp;
EMPNO ENAME JOB
MGR HIREDATE SAL
COMM DEPTNO
7369 SMITH CLERK
7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES
MANAGER 7839 02-APR-81 2975
20
7654 MARTIN SALESMAN
7698 28-SEP-81 1250 1400
30
7698 BLAKE MANAGER
7839 01-MAY-81 2850 30
7782 CLARK MANAGER
7839 09-JUN-81 2450 10
7788 SCOTT ANALYST
7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81
5000 10
7844 TURNER SALESMAN
7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK
7788 23-MAY-87 1100 20
7900 JAMES CLERK
7698 03-DEC-81 950 30
7902 FORD ANALYST
7566 03-DEC-81 3000 20
7934 MILLER CLERK
7782 23-JAN-82 1300 10
14
rows selected.
SQL>
select * from dept;
DEPTNO DNAME LOC
----------
-------------- ------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL>
SELECT * FROM dept
WHERE
EXISTS
(SELECT * FROM emp
WHERE dept.deptno = emp.deptno
AND emp.sal > 2500);
DEPTNO DNAME LOC
----------
-------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
Identify type of join
operation to perform
Display
names of the employees and their department names.
Display
names of the employees along their manager names.
Display
names of the employees who are working
in NEW YORK.
Display
names of the employee,salary of each and grade of each .
Display names of the employees who are not in a
particular set of departments.
Display details of department exists with
salary of employee
Is greater than 2500;