Self Join In SQL Server

Question

What is self join in SQL Server?

Satyapriya Nayak · Answer

Hi Swati The 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. Syntax: The basic syntax of SELF JOIN is as follows: SELECT a.column_name b.column_name... FROM table1 a table1 b WHERE a.common_filed = b.common_field; Here WHERE clause could be any given expression based on your requirement. Example: Consider following two tables (a) CUSTOMERS table is as follows: +----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 6 | Komal | 22 | MP | 4500.00 | | 7 | Muffy | 24 | Indore | 10000.00 | +----+----------+-----+-----------+----------+ Now let us join this table using SELF JOIN as follows: SQL> SELECT a.ID b.NAME a.SALARY FROM CUSTOMERS a CUSTOMERS b WHERE a.SALARY This would produce following result: +----+---------+---------+ | ID | NAME | SALARY | +----+---------+---------+ | 2 | Ramesh | 1500.00 | | 2 | kaushik | 1500.00 | | 1 | kaushik | 2000.00 | | 2 | kaushik | 1500.00 | | 3 | kaushik | 2000.00 | | 6 | kaushik | 4500.00 | | 1 | Hardik | 2000.00 | | 2 | Hardik | 1500.00 | | 3 | Hardik | 2000.00 | | 4 | Hardik | 6500.00 | | 6 | Hardik | 4500.00 | | 1 | Komal | 2000.00 | | 2 | Komal | 1500.00 | | 3 | Komal | 2000.00 | | 1 | Muffy | 2000.00 | | 2 | Muffy | 1500.00 | | 3 | Muffy | 2000.00 | | 4 | Muffy | 6500.00 | | 5 | Muffy | 8500.00 | | 6 | Muffy | 4500.00 | +----+---------+---------+ Please refer the below links http://www.tutorialspoint.com/sql/sql-self-joins.htm http://www.sql-statements.com/sql-self-join.html http://www.sqltutorial.org/sqlselfjoin.aspx http://www.udel.edu/evelyn/SQL-Class3/SQL3_self.html Thanks

Jignesh Trivedi · Answer

hi A table can be joined to itself in a self-join. Use a self-join when you want to create a result set that joins records in a table with other records in the same table. please refer... http://blog.sqlauthority.com/2007/06/03/sql-server-2005-explanation-and-example-self-join/ http://learnsqlserver.in/3/Self-Join.aspx hope this will help you.

Sudhakar Chaudhary · Answer

Normal 0 false false false EN-US X-NONE X-NONE MicrosoftInternetExplorer4 /* Style Definitions */ table.MsoNormalTable{mso-style-name:Table Normal;mso-tstyle-rowband-size:0;mso-tstyle-colband-size:0;mso-style-noshow:yes;mso-style-priority:99;mso-style-qformat:yes;mso-style-parent:;mso-padding-alt:0cm 5.4pt 0cm 5.4pt;mso-para-margin-top:0cm;mso-para-margin-right:0cm;mso-para-margin-bottom:10.0pt;mso-para-margin-left:0cm;line-height:115%;mso-pagination:widow-orphan;font-size:11.0pt;font-family:Calibrisans-serif;mso-ascii-font-family:Calibri;mso-ascii-theme-font:minor-latin;mso-fareast-font-family:Times New Roman;mso-fareast-theme-font:minor-fareast;mso-hansi-font-family:Calibri;mso-hansi-theme-font:minor-latin;} In a self join a table is joined with itself as result one row in a table correlates with other rows in the same table. In a self join a table name is used twice in the query. Therefore to differentiate the two instances of a single table the table is given two alias names. Ex: SELECT a . EmployeeID  a . Title as Employee_Des  a . ManagerID  b . Title As Manager_Des From HumanResources . Employee a  HumanResources . Employee b where a . ManagerID = b . EmployeeID The Following query joins the Employee table with itself to display the EmployeeId attribute and designation of all the employees with the designation of their managers. Why require : Normal 0 false false false EN-US X-NONE X-NONE MicrosoftInternetExplorer4 /* Style Definitions */ table.MsoNormalTable{mso-style-name:Table Normal;mso-tstyle-rowband-size:0;mso-tstyle-colband-size:0;mso-style-noshow:yes;mso-style-priority:99;mso-style-qformat:yes;mso-style-parent:;mso-padding-alt:0cm 5.4pt 0cm 5.4pt;mso-para-margin-top:0cm;mso-para-margin-right:0cm;mso-para-margin-bottom:10.0pt;mso-para-margin-left:0cm;line-height:115%;mso-pagination:widow-orphan;font-size:11.0pt;font-family:Calibrisans-serif;mso-ascii-font-family:Calibri;mso-ascii-theme-font:minor-latin;mso-fareast-font-family:Times New Roman;mso-fareast-theme-font:minor-fareast;mso-hansi-font-family:Calibri;mso-hansi-theme-font:minor-latin;} The Employee table stores only the designation and the EmployeeID of the manager for all the employees. The designation of the manager is not displayed. Therefore You need to join the table with itself. Here we require the self join.

Sandeep Singh Shekhawat · Answer

in the self join we join the table from it self means we join a table from itself. To define self join we need a table and create a two aliase of same table Like I have a COURSE Table SELECT * FROM COURSE c1 COURSE c2 where c1.course_id =c2.course_id and course_id is primary key so create two aliase c1c2 of COURSE table and join these. We don't need define to join keyword. its retrun total number of rows which are in course table but coulums will retrun as defined in select stament. Here i am using '*' so it will be return Course table one coulm two times in resultset. Its an old style join above self join query will be same result as below inner join query SELECT * FROM COURSE c1 INNER JOIN COURSE c2 ON c1.course_id =c2.course_id Thanks Sandeep