Thanks Albert. On point 2 however, surely if it removed duplicate rows the record count on the query containing the Union would be (2489 -178) => 2311? Instead of the 993 rows it did return. This is using an Access db, if that makes any difference.
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! ---------------------------------------------------------------------- ---------------------------------------------------------------------- "Albert Popkov" To: <[EMAIL PROTECTED]> <[EMAIL PROTECTED] cc: om> Subject: RE: [ cf-dev ] SQL UNION 09/10/02 03:59 PM Please respond to dev 1. order will definitely depend on what you have as a clustered index 2. by default union removes duplicated rows, you have to use UNION ALL ------------------------ Albert Popkov i-CD Publishing (UK) Ltd www.192.com [EMAIL PROTECTED] -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Tuesday, September 10, 2002 2:36 PM 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] -- ** 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]
