Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Adding argument to SELECT statement with UNION changes record number

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'
;
like image 372
caleb.breckon Avatar asked Apr 02 '26 00:04

caleb.breckon


1 Answers

change UNION to UNION ALL and you should get the same results. UNION selects distinct rows, UNION ALL should select all.

like image 137
Z.D. Avatar answered Apr 04 '26 12:04

Z.D.



Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!