CODEWITHSUNDEEP
mysqlsqloracle-database
jamesT
jamesT

Reputation: 349

how to rewrite this nested query in a better way?

I have this sql which finds the total number of (distinct) students who have taken course sections taught by the instructor with ID 10101.

            select count (distinct ID)
            from takes
            where (course_id, sec_id, semester, year) in 
            (select course_id, sec_id, semester, year
            from teaches
            where teaches.ID= 10101);

What is another or best way to rewrite it.

Your help will be apprecaited.

Upvotes: 1

Views: 671

Answers (2)

OraNob
OraNob

Reputation: 694

select count (distinct ta.id)
from takes ta
where EXISTS
(select 1 from teaches te
where te.ID=10101
and te.course_id=ta.course_id
and te.sec_id=ta.sec_id
and te.semester=ta.semester 
and te.year=ta.year)

Use EXISTS since this returns a BOOLEAN true/false to the outer query as soon as it has been evaluated.

Upvotes: 0

Madhivanan
Madhivanan

Reputation: 13700

Why don't you use ANSI Join?

select 
    count (distinct t1.ID) 
from 
    takes as t1
    inner join teaches as t2 on 
        t1.course_id=t2.course_id and
    t1.sec_id=t2.sec_id and
    t1.semester=t2.semester and
    t1.year=t2.year 
where 
    t2.ID= 10101

Upvotes: 3

Related Questions