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,
 Email

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

Adam

Try using "UNION ALL" instead of "UNION"

Eduardo