• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Weird SQL UNION Results

    September 26, 2007 Hey, Ted

    I was intrigued by your article Have Your Cake and Eat It, Too. I decided to try your tip with some of my own data. The resulting data set had an anomaly in it. The system omitted a row of input, yet the total was still correct. Why?

    –Wyatt

    It’s my bad, Wyatt, and I should have known better. (Note to self: Leave instructions for my survivors to put those last five words on my tombstone. It seems to accurately describe my life so far! 😉

    The problem was that the example used the word UNION, but it should have used two words–UNION ALL. In my example, UNION gave me the right results. It didn’t occur to me that UNION ALL was the proper solution.

    First, allow me to give a little info about unions for the benefit of those who are not familiar with them. Unions combine two data sets into one. The intended purpose, according to set theory, is to combine two subsets of one superset. (See Thinking in Sets for an example of this usage.) But I seem to find unions more helpful in situations where two differing sets are being combined, as in the article you cited.

    The reason the system omitted a row from your query, Wyatt, is that you had two identical rows in your input data. Yet the summary figures were correct because summary queries do not eliminate duplicate rows. Therefore you got something like this.

    Name          Amt
    -------   -------    
    Jones       25.00
    Smith       10.00
    ZZ          45.00
    

    So here’s the rule of thumb that I forgot about when I wrote that tip. When you union two select queries, consider whether you are combining like data or unlike data. If you are combining like data (e.g., customers and customers) you probably want UNION. If you are combining unlike data (e.g., customers and vendors, or as in the referenced “Cake” article, customer details and summary totals), you probably want UNION ALL. My experience is that you will probably use UNION ALL more than UNION.

    This rule of thumb does not always hold true, but you and the other readers of this publication are smart enough to determine when to go against the rule.

    –Ted

    RELATED STORIES

    Have Your Cake and Eat It, Too

    Thinking in Sets



                         Post this story to del.icio.us
                   Post this story to Digg
        Post this story to Slashdot

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    ARCAD Software

    Embrace VS Code for IBM i Development

    The IBM i development landscape is evolving with modern tools that enhance efficiency and collaboration. Ready to make the move to VS Code for IBM i?

    Join us for this webinar where we’ll showcase how VS Code can serve as a powerful editor for native IBM i code and explore the essential extensions that make it possible.

    In this session, you’ll discover:

    • How ARCAD’s integration with VS Code provides deep metadata insights, allowing developers to assess the impact of their changes upfront.
    • The role of Git in enabling seamless collaboration between developers using tools like SEU, RDi, and VS Code.
    • Powerful extensions for code quality, security, impact analysis, smart build, and automated RPG conversion to Free Form.
    • How non-IBM i developers can now contribute to IBM i projects without prior knowledge of its specifics, while ensuring full control over their changes.

    The future of IBM i development is here. Let ARCAD be your guide!

    Watch Now

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    COMMON:  Join us at the annual 2008 conference, March 30 - April 3, in Nashville, Tennessee
    looksoftware:  Stop recoding with end-to-end modernization solutions
    NowWhatJobs.net:  NowWhatJobs.net is the resource for job transitions after age 40

    IT Jungle Store Top Book Picks

    The System i Pocket RPG & RPG IV Guide: List Price, $69.95
    The iSeries Pocket Database Guide: List Price, $59.00
    The iSeries Pocket Developers' Guide: List Price, $59.00
    The iSeries Pocket SQL Guide: List Price, $59.00
    The iSeries Pocket Query Guide: List Price, $49.00
    The iSeries Pocket WebFacing Primer: List Price, $39.00
    Migrating to WebSphere Express for iSeries: List Price, $49.00
    iSeries Express Web Implementer's Guide: List Price, $59.00
    Getting Started with WebSphere Development Studio for iSeries: List Price, $79.95
    Getting Started With WebSphere Development Studio Client for iSeries: List Price, $89.00
    Getting Started with WebSphere Express for iSeries: List Price, $49.00
    WebFacing Application Design and Development Guide: List Price, $55.00
    Can the AS/400 Survive IBM?: List Price, $49.00
    The All-Everything Machine: List Price, $29.95
    Chip Wars: List Price, $29.95

    B&L Releases Cost Estimating Wizard State of the System i: What’s Going On in the Market?

    Leave a Reply Cancel reply

Volume 7, Number 33 -- September 26, 2007
THIS ISSUE SPONSORED BY:

Help/Systems
WorksRight Software
COMMON

Table of Contents

  • Weird SQL UNION Results
  • A Snippet Above the Rest
  • Admin Alert: What Vendors Want to Know (and How to Get It)

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • Liam Allan Shares What’s Coming Next With Code For IBM i
  • From Stable To Scalable: Visual LANSA 16 Powers IBM i Growth – Launching July 8
  • VS Code Will Be The Heart Of The Modern IBM i Platform
  • The AS/400: A 37-Year-Old Dog That Loves To Learn New Tricks
  • IBM i PTF Guide, Volume 27, Number 25
  • Meet The Next Gen Of IBMers Helping To Build IBM i
  • Looks Like IBM Is Building A Linux-Like PASE For IBM i After All
  • Will Independent IBM i Clouds Survive PowerVS?
  • Now, IBM Is Jacking Up Hardware Maintenance Prices
  • IBM i PTF Guide, Volume 27, Number 24

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2025 IT Jungle