|Comparison between join and IN clause [message #355451]
||Fri, 24 October 2008 02:24
Registered: January 2008
Location: Guadalajara, Spain
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.
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
where nota >= 0.5
and id_matricula in (
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,
WHERE resultados.nota >= 0.5
GROUP BY resultados.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)?
|Re: Comparison between join and IN clause [message #355482 is a reply to message #355451]
||Fri, 24 October 2008 07:09
Registered: October 2005
Location: Melbourne, Australia
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.