Non Correlated sub quires 1 How are they linked?


#1

Hi
Should there not be a join in this exercise?
I don't understand if origin is not in the Airports table and code is not in the Flights table how are they comparing the results?
Please Help


#2

@kingeamo,
Could you give us some code you are using.....


#3

Hi Its the code from the lesson.
SELECT *
FROM flights
WHERE origin in (
SELECT code
FROM airports
WHERE elevation > 2000);


#4

@kingeamo,
To answer the question::
Non Correlated subqueries, How are they linked ??
The link is the Value

Try this::

select id,origin,destination from flights
where origin in
(select code from airports where code = "SNA");

First you create a =list= of code's fron the airports table

code
====
SNA

Then you scan the whole flights table
and for each ROW you =compare= the VALUE-of-origin to the code-list

=== a correlated version ========

select id,origin,destination from flights f
 where exists
(select 1 from airports 
 where code = "SNA"
and code=f.origin);

Both queries would return

Query Results

 id     origin   destination
17107     SNA       SJC 
17661     SNA       PHX

=== another correlated ===

select id,origin,destination from flights f
where exists
(select 1 from airports 
 where code = "SJC"
and code=f.origin);

Query Results

id     origin  destination
2205    SJC       SNA 
19447   SJC       SAN 
3095    SJC       LAS 
15798   SJC       LAX 
8614    SJC       PHX 
9924    SJC       SEA

another NON-correlated

select id,origin,destination from flights
where destination in
(select code from airports where code = "LAX")
order by 2  << ordered by destination
limit 5;    << of the total result only return the first 5 ROW's

Query Results

 id    origin   destination
19232    BNA       LAX 
8097     DFW       LAX 
12976    DFW       LAX 
694      DFW       LAX 
8886     FAT       LAX

#5

Ahh i see.
Thankyou makes sense now:)


#6

This is awesome!! Thanks for sharing!


#7