Oracle Self Join
What is Self Join in Oracle?
- A self join is a join in which a table is joined with itself.
- To join a table itself means that each row of the table is combined with itself and with every other row of the table.
- The table appears twice in the FROM clause and is followed by table aliases that qualify column names in the join condition.
- The self join can be viewed as a join of two copies of the same table. The table is not actually copied, but SQL performs the command as though it were.
- To perform a self join, Oracle Database combines and returns rows of the table that satisfy the join condition
SELECT a.column_name, b.column_name... FROM table1 a, table1 b WHERE a.common_filed = b.common_field;
Example: Oracle Self Join
The following SQL query uses a self join to return the name of each employee along with the name of the employee's manager.
Sample table: employees
SQL> SELECT e1.last_name||' works for '||e2.last_name 2 "Employees and Their Managers" 3 FROM employees e1, employees e2 4 WHERE e1.manager_id = e2.employee_id;
Employees and Their Managers ---------------------------------- Smith works for Cambrault Ozer works for Cambrault Kumar works for Cambrault Fox works for Cambrault Bloom works for Cambrault Bates works for Cambrault Hunold works for De Haan Vishney works for Errazuriz Marvins works for Errazuriz Lee works for Errazuriz Greene works for Errazuriz ...
Self-Joins Using the ON Clause
The ON clause can be used to join columns that have different names, within the same table or in a different table. In following SQL query we have used employee_id and manager_id as joining columns. The query returs the name of each employee’s manager.
SELECT a.column_name, b.column_name... FROM table1 a JOIN table1 b ON (a.common_filed = b.common_field);
SQL> SELECT e1.last_name "Worker", e2.last_name "Manager" 2 FROM employees e1 JOIN employees e2 3 ON (e1.manager_id = e2.employee_id);
Worker Manager ------------------------- ----------- Smith Cambrault Ozer Cambrault Kumar Cambrault Fox Cambrault Bloom Cambrault Bates Cambrault Hunold De Haan Vishney Errazuriz Marvins Errazuriz Lee Errazuriz Greene Errazuriz Banda Errazuriz Ande Errazuriz Sarchand Fripp Olson Fripp Marlow Fripp Dellinger Fripp Cabrio Fripp Bull Fripp Bissot Fripp Atkinson Fripp ...
Note: The parenthesis around the joined columns ais optional.
Outputs of the said SQL statement shown here is taken by using Oracle Database 11g Express Edition.
- Exercises: Weekly Top 16 Most Popular Topics
- SQL Exercises, Practice, Solution - JOINS
- SQL Exercises, Practice, Solution - SUBQUERIES
- Java Array: Exercises, Practice, Solution
- C Programming Exercises, Practice, Solution : Conditional Statement
- HR Database - SORT FILTER: Exercises, Practice, Solution
- C Programming Exercises, Practice, Solution : String
- Python Data Types: Dictionary - Exercises, Practice, Solution
- Python Programming Puzzles - Exercises, Practice, Solution
- C++ Array: Exercises, Practice, Solution
- C# Sharp Basic Algorithm: Exercises, Practice, Solution
- Python Lambda - Exercises, Practice, Solution
- Python Pandas DataFrame: Exercises, Practice, Solution
- Conversion Tools