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. On the Overview page under Virtual Warehouses, click the options menu in the upper right corner, and select Open Hue.
  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;