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:
LEFT JOIN airports
ON flights.origin = airports.code
WHERE airports.elevation < 2000
ORDER BY flights.origin;
WHERE origin in (
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.