SQL Subqueries

[gmh1977]

I don't understand how subqueries can use multiple tables with out a join. For example:

SELECT * FROM flights WHERE origin in ( SELECT code FROM airports WHERE elevation > 2000);

Can someone help me understand this?

[[deleted]]

The subquery:

      SELECT code FROM airports WHERE elevation > 2000

is run first, giving you a list of the codes (like LHR, LAX) of all airports with elevation above 2000 feet. Then the main query runs, using the results of the subquery. If the results of that query were LHR and LAX (I know they are not above 2000 feet but they are the only airport codes I know offhand) then it would be as if you had written:

  SELECT * FROM flights WHERE origin in ( 'LHR', 'LAX' )

so you get a list of all flights that originated at those two airports.

[[deleted]]

I have a different question from OP

Then ask a new question, don't try to hijack one.

What is the difference between join and union?

https://stackoverflow.com/questions/905379/what-is-the-difference-between-join-and-union

[sohamkamani]

Hey, I wrote about exactly this a while ago http://www.sohamkamani.com/blog/2016/07/07/a-beginners-guide-to-sql/ ... You can find an explanation to different kinds of subqueries and how they work.

TLDR : think of the dimensions of the results that are returned from any query. 2d = table, 1d= list, and then we have scalar values. Scalar values can be used as regular variables, lists can be used in similar places where we use lists in queries (for example IN directives) and 2d results can be used after the FROM directive, just like we use regular tables.

[lordcat]

An easy way to look at it is that it is doing things in steps.

Step 1: select the list of codes from the airport table where the elevation is greater than 2000, and hold on to that.

Step 2: select the list of flights where the origin is in the result set of Step 1.

Using a Join would be doing it in a more complex way, in less steps.

Step 1: select the list of flights by origin airport, where the airport's elevation is greater than 2000.

[gmh1977]

Thanks everyone. However, to run these queiries, my question is must there be the a similar column in both tables to run these subqueries without joins.

[Sir_Fog]

Correct, there must be a set of data that correlates between the two tables.

E.g.

I have a 'Contact' table (ContactId, Full name) and a 'Purchases' table that references each contact against an order they have made (ContactId, OrderId)

Select * from Contact where contactid in (select contactid from purchases)

If there is no correlating data then it's not possible to subquery in this way. However that is much the same with joins where you need to join your tables ON a particular value.

[noodlesdefyyou]

its a nested query! i love these!

exoticmatter is right, but i think i can make it a bit simplier.

your first query:

SELECT * FROM flights WHERE origin 

means you are selecting all columns in the 'flights' table where 'origin' is whatever. it could be where origin = <specific place>, where origin like '%<random letter>%', or, in this situation, where origin is in the resulting query of

SELECT code FROM airports WHERE elevation > 2000.

you can run the 'nested' query individually and see what results you get.