HomePagev2 Forums SQL Discussions What are self joins and why are they useful?

Tagged: ,

  • What are self joins and why are they useful?

  • Satish Vadlamani

    Administrator
    September 10, 2020 at 6:58 pm

    Community,

    Please can you help us understand with examples what self joins are and how they are useful?

  • Sai Gowtham Babu AMBURI Gowtham Babu AMBURI

    Member
    September 10, 2020 at 8:37 pm

    a self join can be used to join a table by itself and perform some conditions and  calculations on it

    For example, we can sort the data using the below example based on salary of a person.

    `SELECT a.ID, b.NAME, a.SALARY
    FROM CUSTOMERS a, CUSTOMERS b
    WHERE a.SALARY < b.SALARY;`
    

     

    I would @satishvadlamani sir, @rajasekhar123, @sindhri, @shreekadiyala, @saichaitanya, @akondiseetharam to add more points for my explanation🙂

  • Karri Bindu Madhavi

    Member
    September 11, 2020 at 3:34 am

    <p style=”text-align: left;”>A self join is the one which can be used to join a table itself without depending on any other.</p>
    It is useful to compare rows or query the data within the table.

    A self join also use inner join(return rows when there is a match in both rows) and left join(return all rows from the left table even if there are no matches in the right table).

    Self join syntax:

    “SELECT column_name(s)

    FROM table1 T1, table1 T2

    WHERE <b>condition;”</b>

    T1,T2 are the different table aliases for the same table.

  • Badri Rajasekhar Reddy Rajasekhar Reddy

    Member
    September 11, 2020 at 12:25 pm

    In SQL SELF JOIN is used to join a table to itself as if the table were two tables,temporarily renaming at least one table in the SQL statement.
    basic syntax of SELF JOIN :

    SELECT a.ID, b.Name……
    FROM CUSTOMERS a, CUSTOMERS b
    WHERE a.SALARY SELECT a.ID, b.NAME, a.SALARY

    example :

    SQL> SELECT  a.ID, b.NAME, a.SALARY
         FROM CUSTOMERS a, CUSTOMERS b
         WHERE a.SALARY < b.SALARY;

    example output:
    +————————————+
    | ID | NAME | SALARY |
    +————————————+
    | 1    | Atharva |  20000  |
    | 2   | Karma   |   24000  |
    | 3   | Guru      |   30000  |

Log in to reply.

Original Post
0 of 0 posts June 2018
Now