Why is my SQL Query eliminating results

I am running a union query and it is eliminating results. If I run the query:

SELECT        c.ActDate AS inp_date, c.BegDate, c.ActSales, c.ActShip, b.order_no, COALESCE (b.line_type, 'order') AS line_type, COALESCE (b.tl_amount, 0) AS tl_amount, b.item, a.WorkDaySm, a.Week, d.SalesBud, a.NoDays,
                          d.ShipBud
FROM            backlogdates AS c LEFT OUTER JOIN
                         sp_Daily_Orders AS b ON c.ActDate = b.inp_date LEFT OUTER JOIN
                         WorkDayWeek AS a ON c.ActDate = a.Date LEFT OUTER JOIN
                         spbudget AS d ON c.BegDate = d.BegDate
where b.order_no = '170608'

my result set is:

inp_date      BegDate      ActSales      ActShip      order_no      line_type      tl_amount      item      WorkDaySm      Week      SalesBud      NoDays      ShipBud
2014-07-29      2014-07-27      0      0      170608      order      3252.9      18-463-375      23      5      NULL      5      NULL
2014-07-29      2014-07-27      0      0      170608      order      1294.8      18-464-373      23      5      NULL      5      NULL
2014-07-29      2014-07-27      0      0      170608      order      2938.8      18-461-366      23      5      NULL      5      NULL
2014-07-29      2014-07-27      0      0      170608      order      3252.9      18-463-375      23      5      NULL      5      NULL
2014-07-29      2014-07-27      0      0      170608      order      1294.8      18-464-373      23      5      NULL      5      NULL
2014-07-29      2014-07-27      0      0      170608      order      2938.8      18-461-366      23      5      NULL      5      NULL
2014-07-29      2014-07-27      0      0      170608      order      3252.9      18-463-375      23      5      NULL      5      NULL
2014-07-29      2014-07-27      0      0      170608      order      1294.8      18-464-373      23      5      NULL      5      NULL
2014-07-29      2014-07-27      0      0      170608      order      2938.8      18-461-366      23      5      NULL      5      NULL
2014-07-29      2014-07-27      0      0      170608      order      3252.9      18-463-375      23      5      NULL      5      NULL
2014-07-29      2014-07-27      0      0      170608      order      1294.8      18-464-373      23      5      NULL      5      NULL
2014-07-29      2014-07-27      0      0      170608      order      2938.8      18-461-366      23      5      NULL      5      NULL
2014-07-29      2014-07-27      0      0      170608      order      3252.9      18-463-375      23      5      NULL      5      NULL
2014-07-29      2014-07-27      0      0      170608      order      1294.8      18-464-373      23      5      NULL      5      NULL
2014-07-29      2014-07-27      0      0      170608      order      2938.8      18-461-366      23      5      NULL      5      NULL
2014-07-29      2014-07-27      0      0      170608      order      3252.9      18-463-375      23      5      NULL      5      NULL
2014-07-29      2014-07-27      0      0      170608      order      1294.8      18-464-373      23      5      NULL      5      NULL
2014-07-29      2014-07-27      0      0      170608      order      2938.8      18-461-366      23      5      NULL      5      NULL

which is correct, but once I union the deliveries, I lose all but three.

SELECT        c.ActDate AS inp_date, c.BegDate, c.ActSales, c.ActShip, b.order_no, COALESCE (b.line_type, 'order') AS line_type, COALESCE (b.tl_amount, 0) AS tl_amount, b.item, a.WorkDaySm, a.Week, d.SalesBud, a.NoDays,
                          d.ShipBud
FROM            backlogdates AS c LEFT OUTER JOIN
                         sp_Daily_Orders AS b ON c.ActDate = b.inp_date LEFT OUTER JOIN
                         WorkDayWeek AS a ON c.ActDate = a.Date LEFT OUTER JOIN
                         spbudget AS d ON c.BegDate = d.BegDate
where b.order_no = '170608'
UNION
SELECT        c.ActDate AS inp_date, c.BegDate, c.ActSales, c.ActShip, b.order_no, COALESCE (b.line_type, 'delivery') AS line_type, COALESCE (b.tl_amount, 0) AS tl_amount, b.item, a.WorkDaySm, a.Week, d.SalesBud,
                         a.NoDays, d.ShipBud
FROM            backlogdates AS c LEFT OUTER JOIN
                         sp_Daily_Deliveries AS b ON c.ActDate = b.inp_date LEFT OUTER JOIN
                         WorkDayWeek AS a ON c.ActDate = a.Date LEFT OUTER JOIN
                         spbudget AS d ON c.BegDate = d.BegDate
where b.order_no = '170608'

inp_date      BegDate      ActSales      ActShip      order_no      line_type      tl_amount      item      WorkDaySm      Week      SalesBud      NoDays      ShipBud
2014-07-29      2014-07-27      0      0      170608      order      1294.8      18-464-373      23      5      NULL      5      NULL
2014-07-29      2014-07-27      0      0      170608      order      2938.8      18-461-366      23      5      NULL      5      NULL
2014-07-29      2014-07-27      0      0      170608      order      3252.9      18-463-375      23      5      NULL      5      NULL

I cannot figure out why the query is only showing 3
jsgrosskopfIS ManagerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
UNION eliminates duplicates, and looking at your sample data above it looks like there's three unique rows, and a wompload of duplicates.

UNION ALL does not eliminate duplicates.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jsgrosskopfIS ManagerAuthor Commented:
GENIUS! Thanks. I never knew that....only in programming could a 3 letter word create such havoc.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Yep.

UNION ALL is used either when you need every row (your case), or the two sets are distinct enough that you don't have to worry about it (SELECT name FROM burger_joints UNION ALL SELECT name FROM whorehouses).  

UNION eliminates duplicates, so it takes longer to run.

Thanks for the grade.  Good luck with your project.  -Jim
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SSRS

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.