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

Anti Diagonals - The Coding Shala

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

LeetCode - Bulb Switcher Solution - The Coding Shala

New Year Chaos Solution - The Coding Shala

Sorting the Sentence LeetCode Solution - The Coding Shala