Joins In Oracle

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))