Joining Techniques
📚 Schema reference · PNW flights 5 tables
airlines
- carrier
- name
airports
- faa
- name
- lat
- lon
- alt
- tz
- dst
- tzone
flights
- year
- month
- day
- dep_time
- sched_dep_time
- dep_delay
- arr_time
- sched_arr_time
- arr_delay
- carrier
- flight
- tailnum
- origin
- dest
- air_time
- distance
- hour
- minute
- time_hour
planes
- tailnum
- year
- type
- manufacturer
- model
- engines
- seats
- speed
- engine
weather
- origin
- year
- month
- day
- hour
- temp
- dewp
- humid
- wind_dir
- wind_speed
- wind_gust
- precip
- pressure
- visib
- time_hour
INNER JOIN
- Join flights (as
f) with weather (asw) onoriginandtime_hour(returning only the rows matching between both tables). Select theorigin,dest,temp, andhumidfields.
Hint 1
-- HINT: Use an INNER JOIN to combine rows from flights and weather tables where the origin in flights matches the origin in weather, and the time_hour in flights matches the time_hour in weather. Select the flight's origin, destination, and the weather's temperature and humidity.
SELECT f.origin, f.dest, w.temp, w.humid
FROM flights AS f
INNER JOIN weather AS w ON f.origin = w.origin AND f.time_hour = w.time_hour;Hint 2
-- HINT: Fill in the blanks to complete the INNER JOIN between flights and weather tables.
SELECT f.origin, f.dest, w.temp, w.humid
FROM flights AS f
INNER JOIN weather AS w ON f.___ = w.___ AND f.___ = w.___;LEFT JOIN
- Left join flights with planes on tailnum (returning all rows from flights and only the rows from planes that match). Select the
tailnum,origin,dest,manufacturer, andmodelfields.
Hint 1
-- HINT: Use a LEFT JOIN to combine rows from flights and planes tables where the tailnum in flights matches the tailnum in planes. Select the flight's tailnum, origin, destination, and the plane's manufacturer and model.
SELECT f.tailnum, f.origin, f.dest, p.manufacturer, p.model
FROM flights AS f
LEFT JOIN planes AS p ON f.tailnum = p.tailnum;Hint 2
-- HINT: Fill in the blanks to complete the LEFT JOIN between flights and planes tables.
SELECT f.tailnum, f.origin, f.dest, p.manufacturer, p.model
FROM flights AS f
LEFT JOIN planes AS p ON f.___ = p.___;Anti-Join
- Anti-join flights (as
f) with planes (asp) where plane’s tailnum is missing. Select thetailnum,origin, anddestfields.
Hint 1
-- HINT: Use a LEFT JOIN to combine rows from flights and planes tables where the tailnum in flights matches the tailnum in planes. Select flights' tailnum, origin, and destination where the corresponding tailnum in planes is missing.
SELECT ___, ___, ___
FROM flights AS ___
LEFT JOIN planes AS ___ ON ___ = ___
WHERE ___ IS NULL;Hint 2
-- HINT: Fill in the blanks to complete the LEFT JOIN between flights and planes tables, and select where the plane's tailnum is missing.
SELECT f.tailnum, f.origin, f.dest
FROM flights AS f
LEFT JOIN planes AS p ON f.___ = p.___
WHERE p.___ IS NULL;