Optimizing SQL - Union

Hello all,

I have a table with thousands of rows and is in this format:

id col1 col2 col3 col4
-- -- -- -- --
1 nm 78 xyz pir
2 bn 45 abc dir

I now want to get the data from this table in this format:

field val
--------
col1 nm
col1 bn
col2 78
col2 45
col3 xyz
col3 abc
col4 pir
col4 dir

In order to do this I am doing a union:

select * into #tempUpdate
(
select 'col1' as field, col1 as val from table1
union
select 'col2' as field, col2 as val from table1
union
select 'col3' as field, col3 as val from table1
)

the above example query is smaller - I have a much bigger table with
about 80 columns (Imagine the size of my union query :) and this takes
a lot of time to execute. Can someone please suggest a better way to do
this?

The results of this union query are selected into a temp table, which I
then use to update another table. I am using SQL Server 2000.

my main concern is performance. any ideas please?

thanksIf you have SQL 2005, you can use UNPIVOT. If you are using earlier
releases, try:

select
m.id
, x.col
, case x.col
when 1 then m.col1
when 2 then m.col2
when 3 then m.col3
when 4 then m.col4
end as val
from
MyTable m
cross join
(
select 'col1' union all
select 'col1' union all
select 'col1' union all
select 'col4'
) as x (col)

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"das" <Adityanad@.gmail.com> wrote in message
news:1143651738.146958.160520@.t31g2000cwb.googlegr oups.com...
Hello all,

I have a table with thousands of rows and is in this format:

id col1 col2 col3 col4
-- -- -- -- --
1 nm 78 xyz pir
2 bn 45 abc dir

I now want to get the data from this table in this format:

field val
--------
col1 nm
col1 bn
col2 78
col2 45
col3 xyz
col3 abc
col4 pir
col4 dir

In order to do this I am doing a union:

select * into #tempUpdate
(
select 'col1' as field, col1 as val from table1
union
select 'col2' as field, col2 as val from table1
union
select 'col3' as field, col3 as val from table1
)

the above example query is smaller - I have a much bigger table with
about 80 columns (Imagine the size of my union query :) and this takes
a lot of time to execute. Can someone please suggest a better way to do
this?

The results of this union query are selected into a temp table, which I
then use to update another table. I am using SQL Server 2000.

my main concern is performance. any ideas please?

thanks|||Ok, I will try this. I am a liitle bit confused about the cross join..

I will test and let you know. Thanks!|||On 29 Mar 2006 09:02:18 -0800, das wrote:

(snip)
>In order to do this I am doing a union:
>select * into #tempUpdate
>(
> select 'col1' as field, col1 as val from table1
> union
> select 'col2' as field, col2 as val from table1
> union
> select 'col3' as field, col3 as val from table1
>)
>the above example query is smaller - I have a much bigger table with
>about 80 columns (Imagine the size of my union query :) and this takes
>a lot of time to execute. Can someone please suggest a better way to do
>this?

Hi das,

Somewhat simpler than the suggestions Tom posted (and probably less
efficient, but still a major win over your present version) is the
following simple change:

select 'col1' as field, col1 as val from table1
union ALL
select 'col2' as field, col2 as val from table1
union ALL
select 'col3' as field, col3 as val from table1

UNION without ALL will attempt to remove duplicates; with large result
sets, checking for duplicates can be a major performance killer. With
UNION ALL, you say "don't attempt to remove duplicates" - either because
you want them or (in this case) because you're sure there aren't any.

--
Hugo Kornelis, SQL Server MVP|||that's a really good advice, didn't know what 'union all' meant all
these days.
I tried Thomas approach and it is much faster than before.
thanks a lot guys.|||Been away for a while. Here's a correction:

select
m.id
, x.col
, case x.col
when 1 then m.col1
when 2 then m.col2
when 3 then m.col3
when 4 then m.col4
end as val
from
MyTable m
cross join
(
select 'col1' union all
select 'col2' union all
select 'col3' union all
select 'col4'
) as x (col)

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com

"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:8izWf.1879$m35.157124@.news20.bellglobal.com.. .
If you have SQL 2005, you can use UNPIVOT. If you are using earlier
releases, try:

select
m.id
, x.col
, case x.col
when 1 then m.col1
when 2 then m.col2
when 3 then m.col3
when 4 then m.col4
end as val
from
MyTable m
cross join
(
select 'col1' union all
select 'col1' union all
select 'col1' union all
select 'col4'
) as x (col)

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"das" <Adityanad@.gmail.com> wrote in message
news:1143651738.146958.160520@.t31g2000cwb.googlegr oups.com...
Hello all,

I have a table with thousands of rows and is in this format:

id col1 col2 col3 col4
-- -- -- -- --
1 nm 78 xyz pir
2 bn 45 abc dir

I now want to get the data from this table in this format:

field val
--------
col1 nm
col1 bn
col2 78
col2 45
col3 xyz
col3 abc
col4 pir
col4 dir

In order to do this I am doing a union:

select * into #tempUpdate
(
select 'col1' as field, col1 as val from table1
union
select 'col2' as field, col2 as val from table1
union
select 'col3' as field, col3 as val from table1
)

the above example query is smaller - I have a much bigger table with
about 80 columns (Imagine the size of my union query :) and this takes
a lot of time to execute. Can someone please suggest a better way to do
this?

The results of this union query are selected into a temp table, which I
then use to update another table. I am using SQL Server 2000.

my main concern is performance. any ideas please?

thanks|||THANKS THOMAS!sql

Optimizing daylight savings query by not using UNION

I was interested in optimizing a query I created in SQL Server 2000 for
adjusting time zones for daylight savings time (only USA DST for simplicity)
.
In essence I have one table "tblDaylightSavingsTime" where the PK is Year,
with fields Spring_Forward (ex: 4/3/2005 2:00:00 AM), and Fall_back (ex:
10/30/2005 2:00:00 AM).
I am joining the Daylight Savings Table to another database view, the logic
goes like this:
If the view's datetime falls inside of the DST range for that year, then
adjust for Daylight Savings.
*UNION*
If the view's datetime falls outside of the DST range for that year, then
adjust for Regular Time.
The volume of data pushed through this query is large (in the millions). Is
there any different way to optimize this query by not using UNION?
Thanks!
--
SELECT h.*, DATEADD(hh, h.regularTime, h.indatetime) AS indatetime_adj
FROM qryhsplit_formatted h INNER JOIN
tblDaylightSavingsTime dst ON year(h.indatetime) =
dst.inYear AND ((h.indatetime < dst.Spring_Forward) OR (h.indatetime >
dst.Fall_Back))
UNION ALL
SELECT h.*, DATEADD(hh, h.savingsTime, h.indatetime) AS indatetime_adj
FROM qryhsplit_formatted h INNER JOIN
tblDaylightSavingsTime dst ON year(h.indatetime) =
dst.inYear AND (h.indatetime BETWEEN dst.Spring_Forward AND dst.Fall_Back)How about using a calendar table? This specific example is treated:
http://www.aspfaq.com/2519
"br" <br@.discussions.microsoft.com> wrote in message
news:CA0D1166-D8D5-4C97-8DA1-2E06FDB212B5@.microsoft.com...
>I was interested in optimizing a query I created in SQL Server 2000 for
> adjusting time zones for daylight savings time (only USA DST for
> simplicity).
> In essence I have one table "tblDaylightSavingsTime" where the PK is Year,
> with fields Spring_Forward (ex: 4/3/2005 2:00:00 AM), and Fall_back (ex:
> 10/30/2005 2:00:00 AM).
> I am joining the Daylight Savings Table to another database view, the
> logic
> goes like this:
> If the view's datetime falls inside of the DST range for that year, then
> adjust for Daylight Savings.
> *UNION*
> If the view's datetime falls outside of the DST range for that year, then
> adjust for Regular Time.
> The volume of data pushed through this query is large (in the millions).
> Is
> there any different way to optimize this query by not using UNION?
> Thanks!
> --
> SELECT h.*, DATEADD(hh, h.regularTime, h.indatetime) AS indatetime_adj
> FROM qryhsplit_formatted h INNER JOIN
> tblDaylightSavingsTime dst ON year(h.indatetime) =
> dst.inYear AND ((h.indatetime < dst.Spring_Forward) OR (h.indatetime >
> dst.Fall_Back))
> UNION ALL
> SELECT h.*, DATEADD(hh, h.savingsTime, h.indatetime) AS indatetime_adj
> FROM qryhsplit_formatted h INNER JOIN
> tblDaylightSavingsTime dst ON year(h.indatetime) =
> dst.inYear AND (h.indatetime BETWEEN dst.Spring_Forward AND dst.Fall_Back)
>|||Try this instead; sorry I couldn't figure out all of your date fields, so I
wrote the below with my own column labels.
SELECT "adjusted_date" =
CASE
WHEN t1.[your datetime field] BETWEEN t2.Spring_Forward AND t2.Fall_Back
THEN DATEADD(hh, 1, t1.[your datetime field])
ELSE t1.[your datetime field]
END
FROM qryhsplit_formatted
INNER JOIN tblDaylightSavingsTime t2
ON YEAR(t1.[your datetime field] = t2.Year
Keep in mind that the dates for DST clock changes will shift starting in 200
7!
"br" wrote:

> I was interested in optimizing a query I created in SQL Server 2000 for
> adjusting time zones for daylight savings time (only USA DST for simplicit
y).
> In essence I have one table "tblDaylightSavingsTime" where the PK is Year
,
> with fields Spring_Forward (ex: 4/3/2005 2:00:00 AM), and Fall_back (ex:
> 10/30/2005 2:00:00 AM).
> I am joining the Daylight Savings Table to another database view, the logi
c
> goes like this:
> If the view's datetime falls inside of the DST range for that year, then
> adjust for Daylight Savings.
> *UNION*
> If the view's datetime falls outside of the DST range for that year, then
> adjust for Regular Time.
> The volume of data pushed through this query is large (in the millions).
Is
> there any different way to optimize this query by not using UNION?
> Thanks!
> --
> SELECT h.*, DATEADD(hh, h.regularTime, h.indatetime) AS indatetime_adj
> FROM qryhsplit_formatted h INNER JOIN
> tblDaylightSavingsTime dst ON year(h.indatetime) =
> dst.inYear AND ((h.indatetime < dst.Spring_Forward) OR (h.indatetime >
> dst.Fall_Back))
> UNION ALL
> SELECT h.*, DATEADD(hh, h.savingsTime, h.indatetime) AS indatetime_adj
> FROM qryhsplit_formatted h INNER JOIN
> tblDaylightSavingsTime dst ON year(h.indatetime) =
> dst.inYear AND (h.indatetime BETWEEN dst.Spring_Forward AND dst.Fall_Back)
>|||Forgot to put in t1 table alias for the qryhsplit_formatted view, and closin
g
) for YEAR function:
SELECT "adjusted_date" =
CASE
WHEN t1.[your datetime field] BETWEEN t2.Spring_Forward AND t2.Fall_Back
THEN DATEADD(hh, 1, t1.[your datetime field])
ELSE t1.[your datetime field]
END
FROM qryhsplit_formatted t1
INNER JOIN tblDaylightSavingsTime t2
ON YEAR(t1.[your datetime field]) = t2.Year
"Mark Williams" wrote:
> Try this instead; sorry I couldn't figure out all of your date fields, so
I
> wrote the below with my own column labels.
> SELECT "adjusted_date" =
> CASE
> WHEN t1.[your datetime field] BETWEEN t2.Spring_Forward AND t2.Fall_Back
> THEN DATEADD(hh, 1, t1.[your datetime field])
> ELSE t1.[your datetime field]
> END
> FROM qryhsplit_formatted
> INNER JOIN tblDaylightSavingsTime t2
> ON YEAR(t1.[your datetime field] = t2.Year
> Keep in mind that the dates for DST clock changes will shift starting in 2
007!
> --
>
> "br" wrote:
>|||Way faster!! Thanks very much. Sometimes I get bogged down in the syntax
for the Case / If statements, but this makes it much more clear.
Thanks for the heads up on the DST changes for 2007. The constant
historical changes for DST was one of the reasons why I chose to have a
tblDaylightSavingsTime that denotes the start times and end times of DST for
each year.
FYI - my adjustments "h.savingsTime", and "h.regularTime" are Time Zone
specific adjustments for bringing all data elements into Arizona Time - MST
(AZ).
"Mark Williams" wrote:
> Forgot to put in t1 table alias for the qryhsplit_formatted view, and clos
ing
> ) for YEAR function:
> SELECT "adjusted_date" =
> CASE
> WHEN t1.[your datetime field] BETWEEN t2.Spring_Forward AND t2.Fall_Back
> THEN DATEADD(hh, 1, t1.[your datetime field])
> ELSE t1.[your datetime field]
> END
> FROM qryhsplit_formatted t1
> INNER JOIN tblDaylightSavingsTime t2
> ON YEAR(t1.[your datetime field]) = t2.Year
>
> --
>
> "Mark Williams" wrote:
>

Optimize SQL

Hello All!
Is there a way to write this SQL without a UNION, because where i have to
set one field to null to match the number of field of the second sql :(
SELECT ('Caixa' + ' - ' + Nome) AS Nome, Caixa.CaixaID AS ID, NULL AS
AgenciaID, 'Caixa' AS Tipo
FROM Caixa
UNION
SELECT ('Banco' + ' - ' + Banco.Nome) AS Nome, Banco.ID AS ID,
Agencia.ID AS AgenciaID, 'Banco' AS Tipo
FROM Agencia JOIN
BANCO ON Banco.ID = Agencia.ID
Thank you all!!!
Bruno NThere probably are other solutions but if you want the same columns in
there then you will presumably have to populate the AgenciaID column
with NULLs anyway. I'm not sure what it is you want to do differently.
You should consider using UNION ALL instead of UNION unless it's
required to eliminate duplicates. UNION ALL will typically perform
better.
If you need more help, please post some more info as described here:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||Nope, unless you want to return 2 separate result sets. Then you can do
without the Tipo column as well.
There's nothing wrong really with setting one column to NULL if it doesn't
exist in that part of the unioned set.
Jacco Schalkwijk
SQL Server MVP
"Bruno N" <nylren@.hotmail.com> wrote in message
news:eHHIkgSNFHA.3788@.tk2msftngp13.phx.gbl...
> Hello All!
> Is there a way to write this SQL without a UNION, because where i have to
> set one field to null to match the number of field of the second sql :(
> SELECT ('Caixa' + ' - ' + Nome) AS Nome, Caixa.CaixaID AS ID, NULL AS
> AgenciaID, 'Caixa' AS Tipo
> FROM Caixa
> UNION
> SELECT ('Banco' + ' - ' + Banco.Nome) AS Nome, Banco.ID AS ID,
> Agencia.ID AS AgenciaID, 'Banco' AS Tipo
> FROM Agencia JOIN
> BANCO ON Banco.ID = Agencia.ID
> Thank you all!!!
> Bruno N
>|||Thanks guys!!
"Bruno N" <nylren@.hotmail.com> escreveu na mensagem
news:eHHIkgSNFHA.3788@.tk2msftngp13.phx.gbl...
> Hello All!
> Is there a way to write this SQL without a UNION, because where i have to
> set one field to null to match the number of field of the second sql :(
> SELECT ('Caixa' + ' - ' + Nome) AS Nome, Caixa.CaixaID AS ID, NULL AS
> AgenciaID, 'Caixa' AS Tipo
> FROM Caixa
> UNION
> SELECT ('Banco' + ' - ' + Banco.Nome) AS Nome, Banco.ID AS ID,
> Agencia.ID AS AgenciaID, 'Banco' AS Tipo
> FROM Agencia JOIN
> BANCO ON Banco.ID = Agencia.ID
> Thank you all!!!
> Bruno N
>

Optimization Required for User Defined Function

Hi,

I've an UDF which inside has two query joined by union and it 's similar to this

select * from Table1 ... (several conditions)
union
select * from Table2 ... (several conditions) (this could takes long time to run)

Since i can't write dynamic sql into UDF , i can't avoid to insert Table2 into the query but to improve permormance I've seen how costant can help me.
For Example if I change my UDF in

select * from Table1 ... (several conditions)
union
select * from Table2 Where 1=2 AND (several conditions)

Optimazer is able to skip completely the second execution, so i need to transform 1=2 into a dynamic condition for example test a field table existence.
select * from Table2 Where Exist (select * from Table3 where Field1=1)

That is why i try to write a single UDF can adapt itself to several situations using second condition only where is necessary and not always.
The problem is the dynamic condition for simple could be, wasn't recognize as costant.

For Example
select top 1 * from MyTable where (select 1)=2
select top 1 * from MyTable where 1=2

If you see the execution plan of these 2 queries you could see that the first takes more than 80% of execution time and in the second less than 20%.
Moreover the second plan use a costant scan unlike the first doesn't it.

Do anyone know a way to tell to optimizer to use a simple condition as constant ? This improve drastically my UDF performance.... :( :(

Thanks.1) why is it essential to make it a function and not procedure or view.
2) how do u find

..you could see that the first takes more than 80% of execution time and in the second less than 20%...

if u r referring to execution-plan these % values relative to the batch and does not represent an absolute value. and practically both are taking 0 sec in my machine.
3) if u use "select" in a "where" it is evaluated once for each row of the outer query hence is inefficient.

Opposite of union - Stupid question for a common problem

Hi there!.
I'm having a simple problem. I have a table with like 20 fields , this table
is always growing since it's a price table acumulator for a set of products
(with their features) , this is loaded from a text file.
When a new text file arrives , I upload it to a temp table prior to import
i to the real table.
What I need to do is to only insert in the main table only the NEW fields,
so basically any record that differs in any of the 20 fields from the one
inside the database.
It's a stupid thing when you think of it, but I cannot seem to find the solu
tion.
Any help will be appreciated!!!
VictorSorry.
Please provide DDL and sample data.
http://www.aspfaq.com/etiquette.asp?id=5006
AMB
"Victor Daicich" wrote:

> Hi there!.
> I'm having a simple problem. I have a table with like 20 fields , this tab
le
> is always growing since it's a price table acumulator for a set of product
s
> (with their features) , this is loaded from a text file.
> When a new text file arrives , I upload it to a temp table prior to import
> i to the real table.
> What I need to do is to only insert in the main table only the NEW fields,
> so basically any record that differs in any of the 20 fields from the one
> inside the database.
> It's a stupid thing when you think of it, but I cannot seem to find the so
lution.
> Any help will be appreciated!!!
> Victor
>
>|||As Alejandro said, the shortest path to a solution is to provide DDL, sample
data and expected results. Based on what you have given though, it sounds
like an INSERT INTO with a SELECT statement with a NOT EXISTS and one
*HUMONGOUS* WHERE clause. It's just a big WHERE clause with about 20 AND's
in it. Here's a sample that selects only the unique rows from table #b
(that don't currently exist in #a). I cut it down to just two columns, but
you can expand the SELECT subquery in the NOT EXISTS predicate to include as
many columns as you like:
CREATE TABLE #a (color1 VARCHAR(16),
color2 VARCHAR(16))
CREATE TABLE #b (color1 VARCHAR(16),
color2 VARCHAR(16))
INSERT INTO #a (color1, color2)
SELECT 'blue', 'red'
UNION SELECT 'red', 'green'
UNION SELECT 'black', 'yellow'
UNION SELECT 'black', 'blue'
INSERT INTO #b (color1, color2)
SELECT 'blue', 'green'
UNION SELECT 'black', 'yellow'
UNION SELECT 'yellow', 'purple'
UNION SELECT 'black', 'blue'
SELECT b.*
FROM #b b
WHERE NOT EXISTS
(
SELECT 1
FROM #a a
WHERE a.color1 = b.color1
AND a.color2 = b.color2
)
DROP TABLE #a
DROP TABLE #b
"Victor Daicich" <victordaicich@.hotmail.com> wrote in message
news:12613abe6ea18c85d1b76245e10@.msnews.microsoft.com...
> Hi there!.
> I'm having a simple problem. I have a table with like 20 fields , this
> table is always growing since it's a price table acumulator for a set of
> products (with their features) , this is loaded from a text file.
> When a new text file arrives , I upload it to a temp table prior to import
> i to the real table.
> What I need to do is to only insert in the main table only the NEW fields,
> so basically any record that differs in any of the 20 fields from the one
> inside the database.
> It's a stupid thing when you think of it, but I cannot seem to find the
> solution.
> Any help will be appreciated!!!
> Victor
>|||Hello Mike C#,
That's right Mike. It's like you said, a big where clause is what I need
then.
Thanks guys you've been very helpful with this issue. I can go on now and
finish it.
Thanks again,
Victor
> As Alejandro said, the shortest path to a solution is to provide DDL,
> sample data and expected results. Based on what you have given
> though, it sounds like an INSERT INTO with a SELECT statement with a
> NOT EXISTS and one *HUMONGOUS* WHERE clause. It's just a big WHERE
> clause with about 20 AND's in it. Here's a sample that selects only
> the unique rows from table #b (that don't currently exist in #a). I
> cut it down to just two columns, but you can expand the SELECT
> subquery in the NOT EXISTS predicate to include as many columns as you
> like:
> CREATE TABLE #a (color1 VARCHAR(16),
> color2 VARCHAR(16))
> CREATE TABLE #b (color1 VARCHAR(16),
> color2 VARCHAR(16))
> INSERT INTO #a (color1, color2)
> SELECT 'blue', 'red'
> UNION SELECT 'red', 'green'
> UNION SELECT 'black', 'yellow'
> UNION SELECT 'black', 'blue'
> INSERT INTO #b (color1, color2)
> SELECT 'blue', 'green'
> UNION SELECT 'black', 'yellow'
> UNION SELECT 'yellow', 'purple'
> UNION SELECT 'black', 'blue'
> SELECT b.*
> FROM #b b
> WHERE NOT EXISTS
> (
> SELECT 1
> FROM #a a
> WHERE a.color1 = b.color1
> AND a.color2 = b.color2
> )
> DROP TABLE #a
> DROP TABLE #b
> "Victor Daicich" <victordaicich@.hotmail.com> wrote in message
> news:12613abe6ea18c85d1b76245e10@.msnews.microsoft.com...
>|||If you are using 2005, then you have the "except" operator.
select... from A
except
Select ... from B
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/
"Victor Daicich" wrote:

> Hello Mike C#,
> That's right Mike. It's like you said, a big where clause is what I need
> then.
> Thanks guys you've been very helpful with this issue. I can go on now and
> finish it.
> Thanks again,
> Victor
>
>
>|||Good point, I assumed SQL 2000 (I always do when the OP doesn't mention the
platform) :)
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:EFA7D756-F8CA-4F4B-A614-81A78981ADEA@.microsoft.com...
> If you are using 2005, then you have the "except" operator.
> select... from A
> except
> Select ... from B
>
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>
> "Victor Daicich" wrote:
>|||Oracle provides INTERSECT and MINUS operators (9i, maybe earlier).
SQL Server 2005 provides INTERSECT and EXCEPT.
Is there an ANSII SQL equivilant that anyone is aware of?
"Mike C#" <xyz@.xyz.com> wrote in message
news:OeBlwT$jGHA.4660@.TK2MSFTNGP05.phx.gbl...
> Good point, I assumed SQL 2000 (I always do when the OP doesn't mention
the
> platform) :)
> "Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
> news:EFA7D756-F8CA-4F4B-A614-81A78981ADEA@.microsoft.com...
need
and
you
>|||"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:eUyihMJkGHA.3512@.TK2MSFTNGP03.phx.gbl...
> Oracle provides INTERSECT and MINUS operators (9i, maybe earlier).
> SQL Server 2005 provides INTERSECT and EXCEPT.
> Is there an ANSII SQL equivilant that anyone is aware of?
ANSI SQL:1999 defines INTERSECT and EXCEPT. Unfortunately SQL 2000 is only
compliant up to ANSI SQL:1992.|||INTERSECT, UNION and EXCEPT are in ANSI SQL. MINUS is not.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:eUyihMJkGHA.3512@.TK2MSFTNGP03.phx.gbl...
> Oracle provides INTERSECT and MINUS operators (9i, maybe earlier).
> SQL Server 2005 provides INTERSECT and EXCEPT.
> Is there an ANSII SQL equivilant that anyone is aware of?
>|||Good to know. Thanks.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:exwUSUJkGHA.3844@.TK2MSFTNGP02.phx.gbl...
> INTERSECT, UNION and EXCEPT are in ANSI SQL. MINUS is not.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
> news:eUyihMJkGHA.3512@.TK2MSFTNGP03.phx.gbl...