Megan Taylor

front-end dev, volunteacher, news & data junkie, bibliophile, Flyers fan, sci-fi geek and kitteh servant

Intro to Databases: SQL Subqueries in WHERE clause

expressions involving subqueries: nested select statements within WHERE clause

Demo: simple college admissions database
College (cName, state, enrollment)
Student (sID, sName, GPA, sizeHS)
Apply (sID, cName, major, decision)

Find IDs and names of students who applied to major in CS at some college
select sID, sName
from Student
where sID in (select sID from Apply where major = ‘CS’);

Same thing can be done with a join
select distinct Student.sID, sName
from Student, Apply
where Student.sID = Apply.sID and major = ‘CS’;

Get names of students applying for CS
select sName
from Student
where sID in (select sID from Apply where major = ‘CS’);

vs using a join
select distinct sName
from Student, Apply
where Student.sID = Apply.sID and major = ‘CS’;

ERROR: students with duplicate names but different IDs can cause problems with distinct

Get GPA of students applying to CS major
select GPA
from Student
where sID in (select sID from Apply where major = ‘CS’);

vs using a join
select GPA
from Student, Apply
where Student.sID = Apply.sID and major = ‘CS’;

ERROR: duplicates! distinct will not fix this either.

Find students who applied to CS but not to EE
select sID sName
from Student
where sID in (select sID from Apply where major = ‘CS’)
and sID not in (select sID from Apply where major = ‘EE’);

can also be:
select sID sName
from Student
where sID in (select sID from Apply where major = ‘CS’)
and not sID in (select sID from Apply where major = ‘EE’);

This is the solution to cases where except is not supported!

Find all colleges such that there is some other college in the same state
select cName, state
from College C1
where exists (select * from College C2 where c2.state = C1.state and C1.cName <> C2.cName);

use exists to test whether a subquery is empty
correlated reference

Find college with the largest enrollment
select cName
from College C1
where not exists (select * from College C2 where C2.enrollment > C1.enrollment);

Find student with highest GPA
select sName
from Student C1
where not exists (select * from Student C2 where C2.GPA > C1.GPA);

same query with JOIN?
select distinct S1.sName, S1.GPA
from Student S1, Student S2
where S1.GPA > S2.GPA;
NOT POSSIBLE

Same query using ALL
select sName, GPA
from Student
where GPA >= all (select GPA from Student);

all keyword checks whether the value has a relationship with all results of subquery

select sName
from Student S1
where GPA > all (select GPA from Student S2 where S2.sID <> S1.sID);
WRONG – multiple students have 3.9 GPA

select cName
from College S1
where enrollment > all (select enrollment from College S2 where S2.cName <> S1.cName);
CORRECT – all enrollment values are unique

ANY keyword checks if result has a relationship with any attribute

looking for no other college with a bigger enrollment
select cName
from College S1
where not enrollment <= any (select enrollment from College S2 where S2.cName <> S1.cName);

Find all students NOT from smallest HS
select sID, sName, sizeHS
from Student
where sizeHS > any (select sizeHS from Student);

some systems do not support any or all operators. queries CAN ALWAYS be rewritten using exists

select sID, sName, sizeHS
from Student S1
where exists (select * from Student S2 where S2.sizeHS < S1.sizeHS); SAME RESULT Applied to major in CS, but not EE select sID, sName from Student where sID = any (select sID from Apply where major 'CS') and not sID = any (select sID from Apply where major = 'EE');

November 5, 2011 | Comments Off on Intro to Databases: SQL Subqueries in WHERE clause | Categories: Posts | Permalink

Comments are closed.

%d bloggers like this: