Databases Querying Relational Databases Subqueries Subqueries

5 Answers

Yassin Chiguer
Yassin Chiguer
5,098 Points

Try this: select modelname from model where modelid in (select modelid from car where stickerprice > 30000)

ivana kantnerova
ivana kantnerova
12,439 Points

Select ModelName from Model where ModelID IN ( Select ModelID from Car where StickerPrice > 30000)

Impala Colorado Cherokee Grand Cherokee Accord Camry Sienna

Gia Hoang Nguyen
Gia Hoang Nguyen
3,271 Points

Select ModelName from Model where ModelID IN ( Select ModelID from Car where StickerPrice > 30000)

Can someone give me a clear explanation for this please?

Thank you!

ivana kantnerova
ivana kantnerova
12,439 Points

the first one will be made a "nested select", and the result of this selection will be a group of ids the second one will be the "main select" which will show the names of cars, which ids are in that group

Gia Hoang Nguyen
Gia Hoang Nguyen
3,271 Points

Technically, does it mean that because 2 tables share the same ModelID so I can use the Where clause with

Where ModelID IN ( Select ModelID from Car where StickerPrice > 30000)?

My other approach for the challenge is to use the Left Join to combine 2 tables without using IN like below

Select M.ModelID, M.ModelName from Model M Left Join Select C.ModelID, C.StickerPrice from C On M.ModelID = M.ModelID where Sticker Price > 30000;

Is this also correct?

Which approach will be more optimal?

Thank you! Gia

ivana kantnerova
ivana kantnerova
12,439 Points

technically, it means that when two (or more) tabels have the ids which have the same value (as a primary key in the one table and foreign key on the other table(s)) you can compare them and use restrictions .. for ex. where .. and doesn't matter if it is in nested select or you use directly join between them

more optimal .. it depends of how many records have the table car in nested select... if we suppose a lot, then the better solution will be without nested select because .. in the first solution .. you first have to go through every records in table car (and take a group of id) and then you again have to go through every records in table model and compare ... with join you do it just ones, because first you "join" the records from both tables into one set straight with restriction

but if the table car has only a few records ... nested select will be quick and then it doesn't matter of it

i hope it'll help ... :)) ivana