SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,033
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    What is causing bizarre discrepancy between these 2 Selects?

    Hello,

    When I issue you this SELECT statement by itself:

    SELECT swi.email
    FROM swd, swi
    WHERE swd.category IN (147)
    AND swd.answer = 'yes'
    AND swi.id = swd.ix_id
    ORDER BY RAND()
    LIMIT 500;

    produces 500 results.

    But when I issue the same SELECT via a UNION, that is this:

    (SELECT email
    FROM users
    WHERE category IN (147)
    AND answer='yes'
    AND user_status = '1'
    ORDER BY RAND()
    LIMIT 250)
    UNION
    (SELECT swi.email
    FROM swd, swi
    WHERE swd.category IN (147)
    AND swd.answer = 'yes'
    AND swi.id = swd.ix_id
    ORDER BY RAND()
    LIMIT 500);

    Then I am getting 410 results!
    So I should be getting at least 500, which I am getting when 2nd SELECT is issued by itself, however when I put it in this UNION then I am getting
    less than what I was getting when the 2nd SELECT was issued by itself!

    What is going on???

    Regards,

    Anoox search engine volunteer

    www.anoox.com

  2. #2
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    a UNION deletes duplicate rows of the two results. Do you need a UNION ALL which would return all results from both SELECT statements?

  3. #3
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,033
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    guelphdad,

    The thing is that in the above example the 1st SELECT returns ZERO results by itself.
    2nd SELECT returns the LIMIT of 500 results by itself.
    But when you put them in that UNION, then the results returned is 410!
    How can that be?



    Quote Originally Posted by guelphdad View Post
    a UNION deletes duplicate rows of the two results. Do you need a UNION ALL which would return all results from both SELECT statements?

    Anoox search engine volunteer

    www.anoox.com

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by WorldNews View Post
    How can that be?
    the 2nd subquery returns 500 by itself, but these contain duplicates

    subjecting these to the effect of a UNION operation removes the dupes
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,033
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    r937, are you sure about that?

    I have not heard that or read that anywhere!
    In fact, I thought to get remove duplicate results in a UNION you need to specifically write: UNION DISTINCT

    Regards,

    Anoox search engine volunteer

    www.anoox.com

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by WorldNews View Post
    r937, are you sure about that?
    well, you could, you know, consult the manual

    Quote Originally Posted by WorldNews View Post
    In fact, I thought to get remove duplicate results in a UNION you need to specifically write: UNION DISTINCT
    if neither DISTINCT nor ALL is declared, DISTINCT is the default
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •