Duplicate table in Oracle query

I have a query that looks like this:

  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 on Windows 2003 x64.

Thanks, Tom


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".

Need Your Help

jQuery: selector for button inside a form

jquery forms jquery-selectors event-handling submit

I'm having trouble selecting an element inside a form. The code looks like this:

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.