Non-Correlated Subqueries II


#1

In the example below I do not understand how returning ‘code(s)’ assist. There is no link. Code is a unique field to the airports table. Can someone assist?

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


#2

Hi @methodblaster32242,

The main note is that these type of databases are known as Relation Databases. (aka RDBMS) .
Now the key here is this means that each table in SQL is RELATED to each other.
It is worth mentioning that each SQL table also represents an entity in real life; like a Flight is a table, that represents flights, and Airports is a table that represents airport. But I digress.

What matters here is that they are related.
That relationship doesn’t have to always be on a matching field name, CODE doesn’t have to equal CODE.
What does matter is that you can build any ad-hoc relationship between any two, or more SQL tables, by finding values that match between those two tables.
In this case then, CODE in the Airports table, holds values that match ORIGIN in the flights table.
You can see this by putting the following SQL snippet in the lesson query window and running it.

SELECT * 
FROM Flights 
WHERE Origin in ('SNA' ) LIMIT 5;

SELECT * FROM Airports WHERE Code = 'SNA' LIMIT 5;

This returns up to 5 rows from the FLIGHTS table where ORIGIN is IN ‘SNA’.
The second snippet returns up to 5 rows from the AIRPORTS table, where CODE = ‘SNA’, and that’s how this works.

First SQL reads the subquery from Airports and gets the list of Codes. Then for every CODE it finds that matches the exact word in Flights’ Origin, it shows you the results;

SELECT * 
FROM flights --second for every match show me flight info here
WHERE origin IN (SELECT Code --Go get this list first
                 FROM Airports 
                 WHERE Code = 'SNA' LIMIT 5) LIMIT 5;