Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 2 of 2 total |
Subtle difference in result set |
Mon, Mar 15 2010 10:31 AM | Permanent Link |
AdamBrett Fullwell Mill | I am sure this is documented copiously somewhere ... but I just found out about it & thought I would share it as it could surprise people.
2 SQL statements: FIRST ONE: SELECT P.OrganisationsID, E.Email FROM People P LEFT JOIN EmailAddresses E ON (E.LinkID=P.ID) WHERE UPPER(Email) = UPPER('xxxx@fullwellmill.co.uk') AND E.LinkTable= 'People' SECOND ONE: SELECT LinkID, FROM EmailAddresses WHERE UPPER(Email) = UPPER('adam@fullwellmill.co.uk') AND LinkTable = 'Organisations' UNION SELECT P.OrganisationsID, E.Email FROM People P LEFT JOIN EmailAddresses E ON (E.LinkID=P.ID) WHERE UPPER(Email) = UPPER('adam@fullwellmill.co.uk') AND E.LinkTable= 'People' -- The first statement returns 2 records in the result set, The second only returns 1, even though the first part of statement 2 is the same as the whole of statement 1. The reason is that the results are identical. i.e. The UNION seems to be inserting a hidden "DISTINCT" keyword somewhere in the process, so that multiple repetitions of identical results are winnowed. -- Usually this would cause no problems, but it might if one was actively seeking to check for the existence of duplicates (or something). Is it true of all UNION SQL Statements in DBISAM?? Adam Brett |
Mon, Mar 15 2010 12:06 PM | Permanent Link |
Jose Eduardo Helminsky HPro Informatica | Adam
Try using "UNION ALL" instead of "UNION" Eduardo |
This web page was last updated on Tuesday, April 30, 2024 at 03:55 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |