SQL SELF JOIN - The Coding Shala

Home >> Learn SQL >> SQL SELF JOIN

SQL SELF JOIN

The SQL SELF JOIN is used to join a table to itself. To perform SELF JOIN we use aliases to temporarily change the table's name.
SQL SELF JOIN - The Coding Shala

SQL SELF JOIN Syntax

The basic syntax of SQL SELF JOIN is as follows - 

 SELECT a.column_name, b.column_name...  
 FROM table1 a, table1 b  
 WHERE a.common_field = b.common_field; 

SQL SELF JOIN Example

The following 'emp' table is used for the examples - 

 emp_id     emp_name     city     dept_no     salary  
 1          Akshay        Pune      101       50000  
 3          Nikhil        Pune      101       51000  
 5          Mohit         Delhi     103       40000  
 6          Shubham       Surat     105       42000  
 7          Akash         Mumbai    106       45000  

The following SQL statement will join this table using SELF JOIN - 

SQL >> select a.emp_id, a.emp_name, b.salary  
        from emp a, emp b  
        where a.salary < b.salary;  
 Output >>   
 emp_id     emp_name     salary  
 1          Akshay          51000  
 5          Mohit          50000  
 5          Mohit          51000  
 5          Mohit          42000  
 5          Mohit          45000  
 6          Shubham       50000  
 6          Shubham       51000  
 6          Shubham       45000  
 7          Akash          50000  
 7          Akash          51000  



Other Posts You May Like
Please leave a comment below if you like this post or found some error, it will help me to improve my content.

Comments

Popular Posts from this Blog

Shell Script to find sum, product and average of given numbers - The Coding Shala

Shell Script to Create a Simple Calculator - The Coding Shala

Add two numbers in Scala - The Coding Shala

LeetCode - Number of Good Pairs Solution - The Coding Shala

Richest Customer Wealth LeetCode Solution - The Coding Shala