Why two different series of SQL queries which do the same thing just in a different order have a different number of rows in their outputs?

Question

I am working on a group project for the US Forest Service to create a dashboard on the US's arial firefighting operations and resources. This part is focused on creating a single data table for the third of the dashboard dedicated to data on the runways at the airports from which firefighting aircraft and can off when going to fight a fire and on which they may land afterwards.

The basic task is to join the three tables airports, airport_schedules, and runway while limiting the data stored in the table to only that on airports which are actively used for arial firefighting in the US. I already have a character string of Airport_IDs I scraped from the web elsewhere indicating which airports these are out of all airports on American soil. Because I have not used SQL in two years, as a sanity check, I did this task two different ways, in MySQL Workbench, which I think are equivalent except that they are in different orders. Method 1, Step 1: select only the rows based on those previously ascertrained Airport_IDs (I limited the airport IDs to the first 4 only so as not to waste space) and the fields/columns I need.

-- create the dash_runs table (without runways counts)
SELECT Dep_Airport_ID, Runway_ID, `Length`, Width, Surface_Type_Condition
FROM `runways`.`runway`
WHERE  Dep_Airport_ID IN ('09J','14A','18A','1V6');

-- create the dash_airports table
SELECT Dep_Airport_ID, ARP_Latitude, ARP_Longitude, Facility_Type, Wind_Indicator FROM airports 
WHERE  Dep_Airport_ID IN ('09J','14A','18A','1V6');

-- create the dash_air_scheds table
SELECT Dep_Airport_ID, Schedules FROM airport_schedules
WHERE  Dep_Airport_ID IN ('09J','14A','18A','1V6');

Method 1, Step 2: run all three possible inner joins of the previous 3 queries

-- inner join of dash_air_scheds on dash_airports as 'dasda'
SELECT dash_air_scheds.Dep_Airport_ID, dash_airports.ARP_Latitude, dash_airports.ARP_Longitude, dash_air_scheds.Schedules, dash_airports.Facility_Type, dash_airports.Wind_Indicator
FROM dash_air_scheds
INNER JOIN dash_airports ON dash_air_scheds.Dep_Airport_ID = dash_airports.Dep_Airport_ID;

-- inner join of dash_airports on dash_runs as 
SELECT dash_airports.Dep_Airport_ID, dash_airports.ARP_Latitude, dash_airports.ARP_Longitude, dash_airports.Facility_Type, dash_airports.Wind_Indicator, dash_runs.Runway_ID, dash_runs.Num_of_Runways, dash_runs.Length, dash_runs.Width, dash_runs.Surface_Type_Condition
FROM dash_airports
INNER JOIN dash_runs ON dash_airports.Dep_Airport_ID = dash_runs.Dep_Airport_ID;

-- inner join of dash_air_scheds on dash_runs as 'dasdr'
SELECT dash_air_scheds.Dep_Airport_ID, dash_air_scheds.Schedules, dash_runs.Runway_ID, dash_runs.Num_of_Runways, dash_runs.Length, dash_runs.Width, dash_runs.Surface_Type_Condition
FROM dash_air_scheds
INNER JOIN dash_runs ON dash_air_scheds.Dep_Airport_ID = dash_runs.Dep_Airport_ID;

IMPORTANT: Save the output tables of each of these queries!

Method 1, Step 3: run either possible version of the final inner join

-- Runway table query, version 1
SELECT dasda.Dep_Airport_ID, dasda.ARP_Latitude, dasda.ARP_Longitude, dasda.Schedules, dasda.Facility_Type, dasda.Wind_Indicator, drs.Num_of_Runways, drs.Length, drs.Width, drs.Surface_Type_Condition
FROM dash_air_scheds_on_dash_airports AS dasda
INNER JOIN dash_runways AS drs ON dasda.Dep_Airport_ID = drs.Dep_Airport_ID;
2330 row(s) returned    0.000 sec / 0.015 sec

-- Runway table query, version 2
SELECT dasdr.Dep_Airport_ID, dasdr.Schedules, dash_airports.ARP_Latitude, dash_airports.ARP_Longitude, dash_airports.Facility_Type, dash_airports.Wind_Indicator, dasdr.Runway_ID, dasdr.Num_of_Runways,dasdr.Length, dasdr.Width, dasdr.Surface_Type_Condition
FROM dash_air_scheds_on_dash_runs AS dasdr
INNER JOIN dash_airports ON dasdr.Dep_Airport_ID = dash_airports.Dep_Airport_ID;
2330 row(s) returned    0.000 sec / 0.016 sec

Both of these queries result in the same table with 2330 records. Now, on the other hand, if I do the same thing except just implement the WHERE clause at the end rather than at the beginning, I do not get the same output table.

Method 2, Step 1: an inner join of airport_schedules on airports

SELECT airport_schedules.Dep_Airport_ID, airport_schedules.Schedules, airports.ARP_Latitude, airports.ARP_Longitude, airports.Facility_Type, airports.Wind_Indicator
FROM airport_schedules
INNER JOIN airports ON airport_schedules.Dep_Airport_ID = airports.Dep_Airport_ID;

Method 2, Step 2:

-- Runway table query, version 3
SELECT sch.Dep_Airport_ID, sch.Schedules, sch.ARP_Latitude, sch.ARP.Longitude, sch.Facility_Type, sch.Wind_Indicator, run.Runway_ID, run.Num_of_Runways, run.Length, run.Width, run.Surface_Type_Condition
FROM airport_schedules_on_airports AS sch
INNER JOIN runway AS run ON sch.Dep_Airport_ID = run.Dep_Airport_ID
WHERE sch.Dep_Airport_ID IN ('09J','14A','18A','1V6');
1453 row(s) returned    0.015 sec / 0.203 sec

This is one of my first times asking about SQL queries, not exactly sure how to share the tables returned yet.

Answer 1

Regarding the difference in the output tables between Method 1 and Method 2, it is most likely due to the WHERE clause's placement. In Method 1, you filtered the data to only include the airports used for aerial firefighting in the US before joining the tables, while in Method 2, you filtered the data after joining the tables. This difference can result in different numbers of records being included in the output table, as some records may not meet the WHERE clause's condition.

Therefore, to ensure consistency in the output tables, it is best to filter the data before joining the tables, as you did in Method 1. This approach ensures that only the relevant data is included in the output table and avoids potential discrepancies.