SELF JOIN EXPANATION

What is a self join? Explain it with an example

Let’s illustrate the need for a self join with an example. Suppose we have the following table – that is called employee. The employee table has 2 columns – one for the employee name (called employee_name), and one for the employee location (called employee_location): employee employee_name employee_location Joe New York Sunil India Alex Russia Albert Canada Jack New York Now, suppose we want to find out which employees are from the same location as the employee named Joe. In this example, that location would be New York. Self join SELECT e1.employee_name FROM employee e1, employee e2 WHERE e1.employee_location = e2.employee_location AND e2.employee_name="Joe"; Working of Query. Well, we want to find all the employees who have the same location as Joe – so if we are doing a join we would want to make sure that the location is the same and that the employee name is Joe. So, our join predicate would be where e1.employee_location = e2.employee_location AND employee_name = "Joe". Note that e1 and e2 will represnt the 2 employee tables that we are doing a self join on.