Would UNION ALL give the correct results?
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]]
Sent: 10 September 2002 14:36
To: [EMAIL PROTECTED]
Subject: Re: [ cf-dev ] SQL UNION
hmm. First question, no Order By, it joined the two selects together and
ordered them alphabetically, regardless of which table they came from.
Second question. Can someone explain this to me. Here's the queries and
record counts (just using some test data here):
getNames (Records=2489, Time=94ms)
SQL =
SELECT Name
FROM Guestbook
getIPs (Records=178, Time=16ms)
SQL =
SELECT Name
FROM Guestbook
WHERE IPAddress = "212.250.157.251"
getStuff (Records=993, Time=78ms)
SQL =
SELECT Name
FROM Guestbook
UNION
SELECT Name
FROM Guestbook
WHERE IPAddress = "212.250.157.251"
er, some kind of discrepancy. Query 1 gives 2489 rows. Query 2 gives 178
rows. Unioned together, I get 993 rows. What does that mean? I'm sure
there's a simple logical explanation that I can't see. I would have
expected to get probably 2489 rows back, or possibly 2667, but I can't work
out where 993 comes from at all.
Duncan Cumming
IT Manager
http://www.alienationdesign.co.uk
mailto:[EMAIL PROTECTED]
Tel: 0141 575 9700
Fax: 0141 575 9600
Creative solutions in a technical world
----------------------------------------------------------------------
Get your domain names online from:
http://www.alienationdomains.co.uk
Reseller options available!
----------------------------------------------------------------------
----------------------------------------------------------------------
duncan.cumming@alienationde
sign.co.uk To:
[EMAIL PROTECTED]
cc:
09/10/02 02:07 PM Subject: [ cf-dev
] SQL UNION
Please respond to dev
If I have
SELECT a, b
FROM tableA
WHERE Dept = 1
UNION
SELECT a, b
FROM tableB
without any OrderBy clause, what order will the results be in? tableA rows
followed by tableB rows, or could they be in any order at all?
Second question. If I have UNION on two selects from the same table, eg:
SELECT a
FROM tableA
WHERE Dept = 1
UNION
SELECT a
FROM tableA
WHERE foo = 'bar'
The two selects don't return mutually exclusive rows, i.e. there is an
overlap. Assume my table looks like
ID Dept foo
1 1 bar
2 1 notbar
3 2 bar
4 3 notbar
first select would return rows with ID 1 and 2
second select would return rows with ID 1 and 3
Overall would the Union give me 1, 2, 3, or would it give me 1, 2, 1, 3?
Duncan Cumming
IT Manager
http://www.alienationdesign.co.uk
mailto:[EMAIL PROTECTED]
Tel: 0141 575 9700
Fax: 0141 575 9600
Creative solutions in a technical world
----------------------------------------------------------------------
Get your domain names online from:
http://www.alienationdomains.co.uk
Reseller options available!
----------------------------------------------------------------------
----------------------------------------------------------------------
--
** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
For human help, e-mail: [EMAIL PROTECTED]
--
** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
For human help, e-mail: [EMAIL PROTECTED]
--
** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
For human help, e-mail: [EMAIL PROTECTED]
