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.

Log into the Data Warehouse service as DWUser.
Go to the Virtual Warehouses tab, locate the Virtual Warehouse using which you want to run queries, and click HUE.
The Hue query editor opens in a new browser tab.
Select a database.
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;