Joins in
Oracle
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. If two tables in a join query have no join condition,
then Oracle Database returns their Cartesian product.
Most join queries contain WHERE clause conditions that
compare two columns, each from a different table. Such a condition is called a
join condition. 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.
Main purpose of Joins in Oracle
v Join the data across the table.
v A join is actually performed by the where clause which combines specified row of the table.
Types of Join
1. Equi Join
2. Non-Equi Join
3. Natural Join
4. Cross Join
5. Self Join
6. Outer Join
7. Outer Join
v Left Outer Join
v Right Outer Join
v Full Outer Join
8. Inner join
First We Create the Two Tables Using Foreign Key
First Table Department
Create table department(dept_no int primary key, dept_name char(20), location char(20))
Insert into department values(101,'Marketing','Birgunj')
Insert into department values(102,'Finance','Simraungadh')
Insert into department values(103,'HR','Ludhina')
Department
| DEPT_NO | DEPT_NAME | LOCATION |
|---|---|---|
| 101 | Marketing | Birgunj |
| 102 | Finance | Simraungadh |
| 103 | HR | Ludhina |
Second Table Employee
Create table employee(emp_no int primary key, emp_name char(20), job char(20), MGR int, dept_no int, foreign key(dept_no) references department(dept_no))
