by Dinesh Thakur Category: SQL Commands

A self-join is a query in which a table is joined (compared) to itself. Self-joins are used to compare values in a column with other values in the same column in the same table. The SQL self-join can be done by using table aliases to cheat one table like a different table and then join them together.

SQL self-join can be any form of join such as inner join, outer join ... so you can apply any join to the SQL self-join. SQL self-join is useful when you want to retrieve related data storing in one table such as organization structure.

In order to perform the operation of Self join we need to open the two copies of same table. Because the tables are same so we can use table alias to avoid confusion.

To find out the Manager of each employee.

Select w.ename || 'works for' || m.ename "Employees and their Managers"From emp w, emp mWhere w.mgr = m.empno;

15 rows selected

On execution the result is shown as above in the query. The two different names for table are used as 'w' and 'M'. We used two Aliases for different names. This query will return all employees and their respective managers whom under they work.

Related Articles of SQL

About Dinesh Thakur

Dinesh ThakurDinesh Thakur holds an B.C.A, MCSE, MCDBA, CCNA, CCNP, A+, SCJP certifications. Dinesh authors the hugely popular blog. Where he writes how-to guides around Computer fundamental , computer software, Computer programming, and web apps. For any type of query or something that you think is missing, please feel free to Contact us.