Explanation of self-joins

by

You can view self-join as two identical tables. But in normalization, you cannot create two copies of the table so you just simulate having two tables with self-join.

Suppose you have two tables:

Table emp1

Id Name Boss_id            
1   ABC   3                   
2   DEF   1                   
3   XYZ   2

Table emp2

Id Name Boss_id            
1   ABC   3                   
2   DEF   1                   
3   XYZ   2

Now, if you want to get the name of each employee with his or her boss’ names:

select c1.Name , c2.Name As Boss
from emp1 c1
    inner join emp2 c2 on c1.Boss_id = c2.Id

Which will output the following table:

Name  Boss
ABC   XYZ
DEF   ABC
XYZ   DEF

More Related Contents:

  1. query to find number of employees under each manager
  2. Simplest way to do a recursive self-join?
  3. How to get matching data from another SQL table for two different columns: Inner Join and/or Union?
  4. What is SELF JOIN and when would you use it? [duplicate]
  5. What is a self join for? (in english)
  6. combinations (not permutations) from cross join in sql
  7. SQL query for salary in emp table
  8. What is wrong with this DB Diagram? [closed]
  9. I’m getting ‘Column name is invalid in the select list’ error when i execute the query
  10. You can’t specify target table for update in FROM clause
  11. Not equal != operator on NULL
  12. How can I select the first day of a month in SQL?
  13. How to select rows with no matching entry in another table?
  14. How do I determine the last day of the previous month using PostgreSQL?
  15. SQL Server : Transpose rows to columns
  16. SQL – many-to-many table primary key
  17. Constraint for only one record marked as default
  18. How to Replace Multiple Characters in SQL?
  19. Why is a UDF so much slower than a subquery?
  20. SQL comma-separated row with Group By clause
  21. How to calculate the number of “Tuesdays” between two dates in TSQL?
  22. Get everything after and before certain character in SQL Server
  23. T-SQL trim &nbsp (and other non-alphanumeric characters)
  24. How to return a incremental group number per group in SQL
  25. Generate DEFAULT values in a CTE UPSERT using PostgreSQL 9.3
  26. Cumulative Total in MS Sql server [duplicate]
  27. Return number from Oracle Select statement after parsing date
  28. How do you interpret a query’s explain plan?
  29. Conversion failed when converting the nvarchar value … to data type int
  30. How to call Oracle MD5 hash function?

Leave a Comment