Duplicate table in Oracle query
I have a query that looks like this:
SELECT * FROM employees e, departments d1, departments d2, departments d3, departments d4 WHERE e.dep1 = d1.dep(+) AND e.dep2 = d2.dep(+) AND e.dep3 = d3.dep(+) AND e.dep4 = d4.dep(+);
Is there a better way to write this so that I don't need to use the same table in my query multiple times? I know Oracle's optimizer probably works around this rather nicely, but if there is a more efficient way to write a query like this, I'm all ears. Keep in mind that the above is merely an example, my actual query has a lot more going on in it. I'm using Oracle 188.8.131.52 on Windows 2003 x64.
This is perfectly valid.
The contrived example me and my colleague often use is city_of_birth and city_of_residence. Let's assume we want to query employees who now live in a different country to which they were born in (and that all countries have states).
You would query this as follows:
select e.* from employees e, cities city_of_birth, cities city_of_residence, states state_of_birth, states state_of_residence where e.city_of_birth_id = city_of_birth.id and e.city_of_residence_id = city_of_residence.id and city_of_birth.state_id = state_of_birth.id and city_of_residence.state_id = state_of_residence.id and state_of_birth.country_id != state_of_residence.country_id;
The thing to note is that tables need to be referenced in the from clause of a query as often as there are different meanings to them.
Another way to think about it: you need to reference the same table multiple times in the from clause if you're going to be selecting different rows from each "instance".