How to decide which key to use in self joining

Screen Link:

Hello, I did not understand how we are deciding use reports_to column to e1 table and employee_id to e2 tables.
Can somebody help me with this one?
Thank you.

1 Like

Hi @oykukenanli,

One thing to note is the values in reports_to is linked to the values in employee_id.

On a single table, we can see the relationship between reports_to and employee_id in the following image:

Because reports_to and employee_id are related, they are suitable as keys for joining between the two tables.

When joining e1 and e2, you can see the relationships in the following image:

e1 in the above is checking its reports_to and trying to find a match in the employee_id column of e2. If there’s a match, the rows that match in e1 and e2 will be joined together. For example, Nancy will be joined with Andrew because Nancy reports to an employee with the employee id 1 which is Andrew. The end result is you’ll get a table with all employees matched with their supervisors (assuming they have someone they report to).

2 Likes

Hi @wanzulfikri okayö got it now. Thank you for your answer. :slight_smile:

2 Likes