Ad

Why This Is A Valid Query In Postgres? Is It Expensive For The Dbms?

- 1 answer

I am struggling to understand a part of a query, how could it be valid, and whether is an expensive bad practice or not.

Basically I have something like this:

select student_name,
    case when student_name in
        (
            select students.student_name
            from class.checked_students as checked,
            where students.student_id = 1
            and students.stats = awards.id
        )
        then true
        else false
    end as awarded
from awards.award_items
inner join awards.awards on awards_schema.awards.type = awards.award_items.type

I want to show all awarded students, this works pretty fine but come to my mind one thing...

in the inner select there is this part

where students.student_id = 1
            and students.stats = awards.id

as its an inner query it should be independent (I believe) from outside data unless is specified, by doing award.id I dont have that info available (yet) until I do a inner join.

Does it mean the inner join is being applied before? or how is possible??

is it a bad practice?

I am using postgres

Ad

Answer

This is called a co-related sub-query. And no, the inner query is not independent of the outer query. The inner and outer query are co-related through the WHERE condition students.stats = awards.id of the inner query.

This means the inner query is run once for each row of the outer query - you can see this if you display the execution plan where the node of the inner query will be shown with loops = xxx where xxx is the number of rows in the main query.

If the two tables are related through a many-to-one relationship (for each row of the outer query, the inner query returns exactly one row) then this can be rewritten as an outer join (and e.g. the Oracle query optimizer will do that if foreign key constraints prove the many-to-one relationship).

It's not really considered bad practice, but quite often the equivalent solution using a JOIN is faster (if the query can be re-written!) - at least in Postgres, other query optimizer might be more efficient here.

Ad
source: stackoverflow.com
Ad