Home » SQL & PL/SQL » SQL & PL/SQL » Comparison between join and IN clause (Oracle10G Linux)
Comparison between join and IN clause [message #355451] Fri, 24 October 2008 02:24 Go to next message
Messages: 28
Registered: January 2008
Location: Guadalajara, Spain
Junior Member
Hi! I have a question regarding performance of 2 sql statements.
I have 2 tables: RESULTADOS and ALUMNOS_CURSOS they are related by the ID_MATRICULA column.
id_elemento number,
fecha_examen date,
xml_resultado blob,
nota number,
id_matricula NUMBER)

I want to know the number of entries in resultado that have nota >= 0.5 for every ALUMNOS_CURSOS entry that has a defined id_curso. I implemented this this way:

select count(*) num_aprobados
from resultados 
where nota >= 0.5 
and id_matricula in (
  select id_matricula
  from alumnos_cursos
  where id_curso = &id_curso_1

I know there is the possibility of implementing it trough a join like this:
SELECT SUM(tests_aprobados) num_tests_aprobados 
 FROM alumnos_cursos ac 
  (SELECT COUNT(*) tests_aprobados,
     FROM resultados
    WHERE resultados.nota >= 0.5
 GROUP BY resultados.id_matricula
  ) r
     ON (ac.id_matricula=r.id_matricula)
  WHERE ac.id_curso     = &id_curso

what's the most optimal way of getting this information? I assume it's the first one but maybe in the future if there are hundreds of thousands of rows it may change. What's your opinion? How do you usually do this kind of select (getting some info from some table that's present on another table)?
Regards, Joaquin

Re: Comparison between join and IN clause [message #355482 is a reply to message #355451] Fri, 24 October 2008 07:09 Go to previous message
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Modern versions of Oracle will generally unnest the IN subquery and perform it as a join. So there is no appreciable difference.

Both methods have about the potential to achieve a similar optimal plan. When you run them, one or both may choose a sub-optimal plan, so neither one is "best" from a performance perspective.

My golden rule is to use the version that makes sense. If you want rows from A that match the rows in B, use IN. If you want rows from A and B combined, use a join.

Pick one and then start tuning if it is sub-optimal.

Ross Leishman
Previous Topic: function returns multiple values (2columns, numerous rows)
Next Topic: Columns into Rows Thru ORACLE SQL
Goto Forum:

Current Time: Sat Dec 10 22:19:48 CST 2016

Total time taken to generate the page: 0.07421 seconds