Get those values of column A from table 1 that are not in column A in table 2 (SQL, BigQuery)

in_a_but_not_in_B_TWO_METHODS.sql

# Get those items in produce_big that are not in produce_small. 
-- Inputs:
---- items in produce_big: kale, orange, cabbage, apple
---- items in produce_small: kale
-- Output:
---- orange, cabbage, apple

## There are two ways of doing this.
## Use the first methods, if you have repeated rows in produce_small, e.g., kale, kale, and it is important to account for them.
## Use the second method, if you a) don't have repeated rows in produce_small or b) you have repeated rows in produce_small, but really only care about distinct from that table.

### First method
WITH produce_big AS
 (
 SELECT 'kale' as item, 23 as purchases, 'vegetable' as category
  UNION ALL SELECT 'orange', 2, 'fruit'
  UNION ALL SELECT 'cabbage', 9, 'vegetable'
  UNION ALL SELECT 'apple', 8, 'fruit'


-- +-------------------------------------+
-- | item      | category   | purchases  |
-- +-------------------------------------+
-- | kale      | vegetable  | 23         |
-- | orange    | fruit      | 2          |
-- | cabbage   | vegetable  | 9          |
-- | apple     | fruit      | 8          |
-- +-------------------------------------+
),

produce_small AS 
(
 SELECT 'kale' as item, 23 as purchases, 'vegetable' as category

 
-- +-------------------------------------+
-- | item      | category   | purchases  |
-- +-------------------------------------+
-- | kale      | vegetable  | 23         |
-- +-------------------------------------+
  )
SELECT 
produce_big.*

FROM produce_big LEFT JOIN produce_small on (produce_big.item = produce_small.item) 
WHERE produce_small.item is null

-- +-------------------------------------+
-- | item      | category   | purchases  |
-- +-------------------------------------+
-- | orange    | fruit      | 2          |
-- | cabbage   | vegetable  | 9          |
-- | apple     | fruit      | 8          |
-- +-------------------------------------+

#######################################################################################################
 
### Second method

WITH produce_big AS
 (
 SELECT 'kale' as item, 23 as purchases, 'vegetable' as category
  UNION ALL SELECT 'orange', 2, 'fruit'
  UNION ALL SELECT 'cabbage', 9, 'vegetable'
  UNION ALL SELECT 'apple', 8, 'fruit'


-- +-------------------------------------+
-- | item      | category   | purchases  |
-- +-------------------------------------+
-- | kale      | vegetable  | 23         |
-- | orange    | fruit      | 2          |
-- | cabbage   | vegetable  | 9          |
-- | apple     | fruit      | 8          |
-- +-------------------------------------+
),

produce_small AS 
(
 SELECT 'kale' as item, 23 as purchases, 'vegetable' as category

 
-- +-------------------------------------+
-- | item      | category   | purchases  |
-- +-------------------------------------+
-- | kale      | vegetable  | 23         |
-- +-------------------------------------+
  )
  
SELECT * FROM produce_big
EXCEPT DISTINCT SELECT * FROM produce_small  

-- +-------------------------------------+
-- | item      | category   | purchases  |
-- +-------------------------------------+
-- | orange    | fruit      | 2          |
-- | cabbage   | vegetable  | 9          |
-- | apple     | fruit      | 8          |
-- +-------------------------------------+