From the Oracle SQL Reference we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimpse into the different types of joins with examples.
The join condition can be written in the WHERE clause
Prefix the column name with the table name when the same column name appears in more than one table
Joining n tables need a minimum of n-1 join conditions
For the examples I have taken HR schema and tables like EMPLOYEES , DEPARTMENTS and JOB_GRADES tables.
Initially this schema will be locked when you install Oracle on your system, you can access these tables by unloking your 'HR' schema or user through
login with sys previlages.
Equi join:
•Equijoin is based on equality sign, equals sign, or “=” condition
•Also called inner join or simple join
It simplify the queries
It also improve performance of the query
It provides a convenient to use "name" in place of the actual table name (needed for self joins - see below)
Example (using the alias "e" for employees and "d" for departments) :
Use Outer join is used to return records which don’t have direct match and Outer join operator is the plus sign (+).
In outer join operation , all records from the source table included in the result even though they don't satisfy the join condition.
Outer joins are classified into three types:
1) Left Outer Join
2) Right Outer Join
3) Full Outer Join
Syntax for Outer Joins:
SELECT column names from both tables FROM table name 1LEFT|RIGHT|FULL OUTER JOIN table name 2 on condition;
This type of outer join returns the unmatched rows from the left table which matched rows from the right table.
These selects all the matched rows of table 2 and unmatched rows from table 1.
SELECT e.first_name,e.last_name,e.salary,d.department_id ,d.department_nameFROM employees eLEFT OUTER JOIN departments d ON (e.department_id = d.department_id) ;
Right outer join is also like as Left Outer Join. But a little differcence like it returns all the unmatched rows from the right table
plus matched rows from left table.
Using this we can use the outer join operator in the right side of the table.
SELECT e.first_name,e.last_name,e.salary,d.department_id ,d.department_nameFROM employees eRIGHT OUTER JOIN departments d ON (e.department_id = d.department_id) ;
This is the third classification of the outer join. Full outer join is a combination of LEFT and RIGHT outer join.
In this the join operation combines both the result of left and right outer joins.
The join table will return the set of all the records from both tables and filling NULL values for the missing matches on either side.
SELECT e.first_name,e.last_name,e.salary,d.department_id ,d.department_nameFROM employees eFULL OUTER JOIN departments d ON (e.department_id = d.department_id) ;
When a table is joined to itself then it is called as Self join or in less words we can just say ” joining a table to itself is called self join” .
Example (and notice the use of table alias to provide a meaningful name for the data origin):
SELECT worker.last_name|| ‘ works for ‘ || manager.last_nameFROM employees worker, employees managerWHERE worker.manager_id = manager.employee_id ;
These joins are equivalent to Equi Joins.
Joins with all the common columns present in the tables.
The column names as well as the data types should match.
No matter how many common columns are available in the tables, NATURAL JOIN will join with all the common columns
Use USING clause to join with specified columns
Example :
SELECT employee_id, first_name, manager_id, department_nameFROM employeesJOIN departments USING (department,manager_id);
Comments (0)