BEWARE: Concealed Logic in Non-Correlated Subqueries I & II (2-3/7)


#1

Just a heads up, it’s especially bad in this one. Wondering if anyone could explain the missing pieces.

https://www.codecademy.com/courses/sql-table-transformation/lessons/subqueries/exercises/non-correlated-i?action=resume_content_item

The fundamental thing you’ll ask yourself is how these tables are magically producing results like they’re joined even though I didn’t code any joining.

SELECT *
FROM flights
WHERE origin in (
SELECT code
FROM airports
WHERE fac_type = ‘SEAPLANE_BASE’);
SELECT *
FROM flights
WHERE origin in (
SELECT code
FROM airports
WHERE elevation > 2000);

The results behave like airports.code = flights.origin , but you’ve got no reason to think that’s true. Are you crazy?
No, I guess the author just thought it would be amusing not to point that out.

I still don’t know how to define it any better than this, or give the why as to this pre-coded behavior, and if that’s how SQL works IRL.

Maybe someone else knows why we would have had to use JOIN in this exact situation in a previous exercise where columns in separate tables held shared data and unique column names, but not in this exercise?


#2

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.