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

Thanks, Tom

Answers


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.