When I add a new argument to the SELECT clause of a UNION, I get more records... how can this be? Isn't a UNION just mashing them together? Example:
EDIT: They're absolutely distinct. the code column is either "IN" or "OUT", and that's what I'm using to separate the two.
EDIT2: UNION ALL gives me 80 records, like it should, but it's odd because my two SELECT statements are absolutely distinct.
FINAL EDIT: Ultimate problem was records within one of my SELECT statements being not DISTINCT, not between the two SELECT statements. Thanks all.
-- Yields 76 records
SELECT
f.date
, f.code
, f.cost
FROM a.fact f
WHERE f.code = 'IN'
UNION
SELECT
f2.date
, f2.code
, f2.cost
FROM a.fact2 f2
WHERE f2.code = 'OUT'
;
-- Yields 80 records
SELECT
f.key
, f.date
, f.code
, f.cost
FROM a.fact f
WHERE f.code = 'IN'
UNION
SELECT
f2.key
, f2.date
, f2.code
, f2.cost
FROM a.fact2 f2
WHERE f2.code = 'OUT'
;
change UNION to UNION ALL and you should get the same results. UNION selects distinct rows, UNION ALL should select all.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With