Tag Archives: query

How does database (oracle) joins handle filter condition

I was wondering how does database interpret join queries. When we create a join between 2 tables in Oracle, with some additional filter condition on one or both tables, will oracle join the tables first and then filter or will it filter the conditions first and then join.

For example I have 2 simple queries

Query 1:

select e.name, d.name from employee e, department d
where e.dept_id=d.id and e.salary>50000;

Query 2:

select e.name, d.name from
(select * from employee where salary>50000) e, department d
where e.dept_id=d.id;

Now which is a better option?

I posted my doubts on a couple of forums and did some googling, and this is what I found

“Both are same”

An easy way to check this out id to use “explain plan for” clause, which tells you how actually oracle interprets your query after optimization.

explain plan for
select e.name, d.name from employee e, department d
where e.dept_id=d.id and e.salary>50000;
select * from table(dbms_xplan.display());

output was same as

explain plan for
elect e.name, d.name from
(select * from employee where salary>50000) e, department d
where e.dept_id=d.id;