JOIN Clause

SELECT route.airlineid, airline.name, route.sourceairport, route.destinationairport
FROM route
INNER JOIN airline
ON route.airlineid = META(airline).id
WHERE route.destinationairport = "SFO"
ORDER BY route.sourceairport;

[
  {
    "airlineid": "airline_5209",
    "destinationairport": "SFO",
    "name": "United Airlines",
    "sourceairport": "ABQ"
  },
  {
    "airlineid": "airline_5209",
    "destinationairport": "SFO",
    "name": "United Airlines",
    "sourceairport": "ACV"
  },
  {
    "airlineid": "airline_5209",
    "destinationairport": "SFO",
    "name": "United Airlines",
    "sourceairport": "AKL"
  },
// ...

SELECT DISTINCT  MIN(aport.airportname) AS Airport__Name,
                 MIN(aport.tz) AS Airport__Time,
                 MIN(lmark.name) AS Landmark_Name
FROM airport aport (1)
LEFT JOIN landmark lmark (2)
  ON aport.city = lmark.city
  AND lmark.country = "United States"
GROUP BY aport.airportname
ORDER BY aport.airportname
LIMIT 4;

[
  {
    "Airport__Name": "Abbeville",
    "Airport__Time": "Europe/Paris",
    "Landmark_Name": null (1)
  },
  {
    "Airport__Name": "Aberdeen Regional Airport",
    "Airport__Time": "America/Chicago",
    "Landmark_Name": null
  },
  {
    "Airport__Name": "Abilene Rgnl",
    "Airport__Time": "America/Chicago",
    "Landmark_Name": null
  },
  {
    "Airport__Name": "Abraham Lincoln Capital",
    "Airport__Time": "America/Chicago",
    "Landmark_Name": null
  }
]

SELECT DISTINCT MIN(aport.airportname) AS Airport__Name,
                MIN(aport.tz) AS Airport__Time,
                MIN(lmark.name) AS Landmark_Name,
FROM airport aport (1)
RIGHT JOIN landmark lmark (2)
  ON aport.city = lmark.city
  AND aport.country = "United States"
GROUP BY lmark.name
ORDER BY lmark.name
LIMIT 4;

[
  {
    "Airport__Name": "San Francisco Intl",
    "Airport__Time": "America/Los_Angeles",
    "Landmark_Name": ""Hippie Temptation" house"
  },
  {
    "Airport__Name": null, (1)
    "Airport__Time": null,
    "Landmark_Name": "'The Argyll Arms Hotel"
  },
  {
    "Airport__Name": null,
    "Airport__Time": null,
    "Landmark_Name": "'Visit the Hut of the Shadows and other End of the Road sculptures"
  },
  {
    "Airport__Name": "London-Corbin Airport-MaGee Field",
    "Airport__Time": "America/New_York",
    "Landmark_Name": "02 Shepherd's Bush Empire"
  }
]

CREATE INDEX def_inventory_route_sourceairport
ON route (sourceairport);

SELECT META(route).id route_id, route.airline, route.destinationairport
FROM airport JOIN route ON route.sourceairport = airport.faa
WHERE airport.icao = "EGLL"
ORDER BY route_id;

[
  {
    "airline": "AH",
    "destinationairport": "ALG",
    "route_id": "route_10186"
  },
  {
    "airline": "AI",
    "destinationairport": "BOM",
    "route_id": "route_10570"
  },
// ...

CREATE INDEX idx_route_src_dst_airline
ON route (sourceairport, destinationairport, airline);

SELECT DISTINCT subquery.destinationairport
FROM airport
JOIN (
  SELECT destinationairport, sourceairport
  FROM route
) AS subquery
ON airport.faa = subquery.sourceairport
WHERE airport.city = "San Francisco";

[
  {
    "destinationairport": "HKG"
  },
  {
    "destinationairport": "ICN"
  },
  {
    "destinationairport": "ATL"
  },
  {
    "destinationairport": "BJX"
  },
  {
    "destinationairport": "GDL"
  },
// ...

SELECT DISTINCT expression.destinationairport
FROM airport JOIN [
  {"destinationairport": "KEF", "sourceairport": "SFO", "type": "route"},
  {"destinationairport": "KEF", "sourceairport": "LHR", "type": "route"}
] AS expression
ON airport.faa = expression.sourceairport
WHERE airport.city = "San Francisco";

[
  {
    "destinationairport": "KEF"
  }
]

SELECT COUNT(1) AS Total_Count
FROM route rte
INNER JOIN airline aline
USE HASH (PROBE)
ON rte.airlineid = META(aline).id;

[
  {
    "Total_Count": 17629
  }
]

SELECT COUNT(1) AS Total_Count
FROM airline aline
INNER JOIN route rte
USE HASH (BUILD)
ON (rte.airlineid = META(aline).id);

[
  {
    "Total_Count": 17629
  }
]

SELECT a.airportname AS airport, r.id AS route
FROM route AS r
JOIN airport AS a
USE NL
ON a.faa = r.sourceairport
WHERE r.sourceairport = "SFO"
LIMIT 4;

SELECT COUNT(1) AS Total_Count
FROM route rte
INNER JOIN airline aline
USE INDEX (idx_destinations) HASH (PROBE)
ON (rte.airlineid = META(aline).id);

SELECT COUNT(1) AS Total_Count
FROM route rte
INNER JOIN airline aline
USE HASH (PROBE) KEYS ["airline_10", "airline_21", "airline_22"]
ON (rte.airlineid = META(aline).id);

SELECT DISTINCT route.destinationairport, route.stops, route.airline,
  airline.name, airline.callsign
FROM route
  JOIN airline
  ON KEYS route.airlineid
WHERE route.sourceairport = "SFO"
AND route.stops = 0
LIMIT 4;

[
  {
    "airline": "B6",
    "callsign": "JETBLUE",
    "destinationairport": "AUS",
    "name": "JetBlue Airways",
    "stops": 0
  },
  {
    "airline": "B6",
    "callsign": "JETBLUE",
    "destinationairport": "BOS",
    "name": "JetBlue Airways",
    "stops": 0
  },
  {
    "airline": "B6",
    "callsign": "JETBLUE",
    "destinationairport": "DXB",
    "name": "JetBlue Airways",
    "stops": 0
  },
  {
    "airline": "B6",
    "callsign": "JETBLUE",
    "destinationairport": "FLL",
    "name": "JetBlue Airways",
    "stops": 0
  }
]

SELECT route.airline, route.sourceairport, route.destinationairport,
  airline.callsign
FROM route
  LEFT JOIN airline
  ON KEYS route.airlineid
WHERE route.destinationairport = "ATL"
  AND route.sourceairport = "SEA";

[
  {
    "airline": "AF",
    "callsign": "AIRFRANS",
    "destinationairport": "ATL",
    "sourceairport": "SEA"
  },
  {
    "airline": "AM",
    "destinationairport": "ATL",
    "sourceairport": "SEA"
  },
  {
    "airline": "AS",
    "destinationairport": "ATL",
    "sourceairport": "SEA"
  },
  {
    "airline": "AZ",
    "destinationairport": "ATL",
    "sourceairport": "SEA"
  },
  {
    "airline": "DL",
    "callsign": "DELTA",
    "destinationairport": "ATL",
    "sourceairport": "SEA"
  },
  {
    "airline": "KL",
    "destinationairport": "ATL",
    "sourceairport": "SEA"
  }
]

SELECT DISTINCT route.destinationairport, route.stops, route.airline,
  airline.name, airline.callsign
FROM route
  JOIN airline
  ON KEYS route.airlineid
WHERE airline.icao = "SWA"
LIMIT 4;

CREATE INDEX route_airlineid ON route(airlineid);

CREATE INDEX airline_icao ON airline(icao);

SELECT * FROM airline
  JOIN route
  ON KEY route.airlineid FOR airline
WHERE airline.icao = "SWA";

[
  {
    "name": "JetBlue Airways",
    "schedule": [
      {
        "day": 0,
        "flight": "B6076",
        "utc": "10:15:00"
      },
      {
        "day": 0,
        "flight": "B6321",
        "utc": "00:06:00"
      },
// ...
    ]
  }
]

CREATE INDEX on-key-for-index-name rhs-expression (lhs-expression-key);

SELECT projection-list
FROM lhs-expression
JOIN rhs-expression
  ON KEY rhs-expression.lhs-expression-key FOR lhs-expression
[ WHERE predicates ] ;

CREATE INDEX route_airlineid ON route(airlineid);

SELECT COUNT(DISTINCT route.sourceairport) AS DistinctAirports
FROM airline
  JOIN route
  ON KEY route.airlineid FOR airline
WHERE airline.iata = "AA";

[
  {
    "DistinctAirports": 429
  }
]