What Is a Self Join For? (In English)

What is a self join for? (in english)

The reason why the employee-manager example is so common, is because it hits the nail on the head. A self join on a table looks for pairs of rows, like any join, but with both rows coming from the same table. Nothing special really.

What is SELF JOIN and when would you use it?

You use a self join when a table references data in itself.

E.g., an Employee table may have a SupervisorID column that points to the employee that is the boss of the current employee.

To query the data and get information for both people in one row, you could self join like this:

select e1.EmployeeID, 
    e1.FirstName, 
    e1.LastName,
    e1.SupervisorID, 
    e2.FirstName as SupervisorFirstName, 
    e2.LastName as SupervisorLastName
from Employee e1
left outer join Employee e2 on e1.SupervisorID = e2.EmployeeID

Explanation of self-joins

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

self join ON clause in SQL

in some databases the != operator is write like <>,
the query will be the same

SELECT *
FROM
point_2d p1
Inner JOIN
point_2d p2
ON p1.x <> p2.y;

If you don't like use the expllicit join, you can also use this way

SELECT *
FROM
point_2d p1, point_2d p2
WHERE p1.x <> p2.y

But I prefere the first way because it more explicit and I think you can read better the query

If you have some doubts I have found for you a list of operators used in SQL
https://www.w3schools.com/sql/sql_operators.asp

difficult query: Join Parameter, self join, and contradicting conditions

I just found out:

after 'JOIN' follows 'ON' and in this we can specify the join condition, but also things we would put in 'WHERE'

this makes it possible to have tailored tables, which can be tailored on in where

sooo :

old query:

SELECT t.textidea,t.translation 

FROM translation AS t  
LEFT JOIN translation AS w             

ON t.textidea = w.textidea 
WHERE (t.language = 'en-US' 
   AND w.language = 'de-DE'         
   AND w.translation IS NULL            
   AND t.translation IS NOT NULL)

change:

ON t.textidea = w.textidea AND w.language = 'de-DE'

new working query:

SELECT t.textidea,t.translation 

FROM translation AS t  
LEFT JOIN translation AS w             

ON t.textidea = w.textidea AND w.language = 'de-DE'
WHERE (t.language = 'en-US' 
   AND w.translation IS NULL            
   AND t.translation IS NOT NULL)

Understanding Self-Join Distinct Query

This query returns all employees who share a last name with another employee, but not a title or employee id.

A more efficient way to write this query uses exists:

select e.*
from employee e
where exists (select 1
              from employee e2
              where e2.last_name = e.last_name and
                    e2.employee_id <> e.employee_id and
                    e2.title <> e.title
             );

Writing the query this way saves the effort for the removing duplicates from the join.

For performance, you would want an index on employee(last_name, employee_id, title).

---

Related Topics