You are here

SQL: SELF JOIN

self join

SELF JOIN

  • Self joins are used to compare values in a column with other values in the same column in the same table.
  • In some situations, it is necessary to join a table to itself, as through joining two separate tables. This is referred to self join.
  • There is no specific ‘SELF JOIN’ keyword, we have to write ordinary join where both tables involved in the join are the same tables.
  • Self join indicates the tables are not normalized.
  • self join can be a inner join or left outer join or cross join. A table is joined to itself based upon a column that have duplicate data in different rows.
  • self-joined table will be treated as a separate table while retrieving data from the disk, thus self-join incurs more I/O activities and increases locking overhead as it touches the same table twice or more.
  • Syntax:
    Select col1, col2,…., coln from <table_name> as <alias1> INNER/LEFT  JOIN <table_name> as <alias2> ON <predicate>;

Example:

Table: Employee (emp_id is primary key)

emp_id fname man_id
1 Riya NULL
2 Diya 1
3 Jiya 2
4 Priya 3

Self join with left join:
Retrieve all employee names with their manager name including CEO of the company:
select e.fname as emp, m.fname as manager from Employee as e LEFT JOIN Employee as m on e.man_id=m.emp_id;
output:

Emp Manager
Riya NULL
Diya Riya
Jiya Diya
Priya Jiya

Explanation:
In above example we used left join in self join that’s why we get the name of CEO. Employee name Riya is the CEO of the company because it’s manager is NULL.
Self join with inner join:
Retrieve all employee names with their manager name excluding CEO of the company:
select e.fname as emp, m.fname as manager from Employee as e INNER JOIN Employee as m on e.man_id=m.emp_id;
output:

Emp Manager
Diya Riya
Jiya Diya
Priya Jiya

Explanation:
In above example we used inner join in self join so we only get name of employee and it’s manager we can not get CEO of the company.

Download Example [media-downloader media_id=”271″ texts=”Download Code”]

Keep Visiting TechwithR.com for more upcoming top technical articles and also see my blog Mag4info.

Detail course about DBA visit TechNet Consultancy .

You can also by SQL 70-761 book write by me from Amazon.
You can also by Kotlin Book write by me from Amazon.

Leave a Reply

Top