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.

Click > Open Hue on the Virtual Warehouse tile.
Select a database.
For example, select TBD.
In Hue, select either the Impala or Hive editor.

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;

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;