Using set operations

When you want to combine separate query results into a single result, you need to know about the INTERSECT and EXCEPT clauses in addition to UNION.

In this task, you run a query to find the number of Boeing planes leaving from DFW in 2005. The query filters data using an intersect on the tail number, tailnum, with the unique identification of a plane, planes.manufacturer.

You created a Unified Analytics VW that loads the sample data to run queries of airline data.
  1. Click > Open Hue on the Virtual Warehouse tile.
  2. Select a database.
  3. In Hue, select either the Impala or Hive editor.
  4. Enter a query that intersects the tail number and type of plane.
    SELECT COUNT(*)
    FROM
      SELECT tailnum
      FROM flights
      WHERE
        flights.origin = 'DFW' AND flights.`year`= 2005
      INTERSECT
      SELECT tailnum
      FROM planes
      WHERE 
        planes.manufacturer = 'BOEING'
    ) subq;   
  5. Run a query that returns the planes not manufactured by Boeing leaving from DFW.
    SELECT COUNT(*)
    FROM
      SELECT tailnum
      FROM flights
      WHERE
        flights.origin = 'DFW' AND flights.`year`= 2005
      EXCEPT
      SELECT tailnum
      FROM planes
      WHERE 
        planes.manufacturer = 'BOEING'
    ) subq;