SQL: Table Transformation 2/7


#1

I’m trying to fully understand this subquerys topic. I find it like another possible way to Join 2 tables.
So I tried to compare both tools, expecting to give me the same amount of rows in the query result.

I’m going to paste both codes:

SELECT *
FROM flights
LEFT JOIN airports
ON flights.origin = airports.code
WHERE airports.elevation < 2000
ORDER BY flights.origin;

SELECT *
FROM flights
WHERE origin in (
SELECT code
FROM airports
WHERE elevation < 2000)
ORDER BY origin;

The fun thing is that the subquery gives me exactly 10 rows less than the left join and I can’t understandy why.
I also did a origin, COUNT(*), grouped by origin. It gave me almost the same results except from some codes.
For example, the origin “HOU” on the join gave me 12 row but on the subquery it gave me 6 rows.
I find it odd because on the Airport table, it only appears “HOU” one time with an elevation less that 2000. So, if they are 12 rows on the flights table with “HOU” as origin, why on the subquery it gave me 6 rows?

I hope you guys can understand me and let me know what I’m not figuring out.
Thanks!


#2

UPDATE:

I figured it out that on 3 origin codes, on the join, it duplicated me the rows. It happens only in that code and 2 more. The rest (72 codes), it didn’t duplicate the information.
Why is that happening? I have no clue of why it shows me that (Only 3 of 75 codes, and haven’t done a CROSS JOIN to “duplicate” information).


#3

We will need to navigate to the page you are actually on to test and investigate. Please post a link to the exercise. Thanks.


#4

No problem, here is the link.

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


#5

Non-Correlated Subqueries I?


#6

Exactly.

Just a reminder, it’s not that i can’t go on with the course, It’s that I tried to test everything so I can understand it all.
So I tried to use both solutions for the problem. Because of the problem, I figured it out that I could solve it with both methods.


#7

That’s throwing me off, a bit, but SQL is not my strong suit. Granted we are working with two tables, but we are not joining tables, only issuing a selection of rows from one table, based on data in a column of the other. You can imagine my confusion…


#8

English it’s not my native language so maybe we are creating a confusion there.

When i say “on the join” I mean using the JOIN solution for the problem. But after hours of thinking this issue, i think I know why.
Could it be that I’m joining 2 tables with a non-primary key column?


#9

That sounds like a possibility, at least in explaining the different results. If they do not have a primary key in common, then it is difficult to create a join, as I understand it. But understanding of the SQL language is my problem, and not understanding of your language. But then again, …


#10

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