How to compare two tables

Hello, I don't know how to do this and I'm sure its dead simple.
I have two tables that I'll use on a WHERE condition, and I need to know
that the rows in one are exactly the same rows in the other. I don't want
to return results in my query unless there is an exact match between those
two single column tables...
I've been using a two select ... except two solve my problem, but I know
there must be a simpler way, and obviously I'm stuck and unable to think..
An example:
CREATE TABLE #a(c int)
CREATE TABLE #b(c int)
CREATE TABLE #c(c int)
If I have this:
INSERT INTO #a SELECT 1 UNION ALL SELECT 2
INSERT INTO #b SELECT 1 UNION ALL SELECT 2
INSERT INTO #c SELECT 3
Then I want a condition that will make this (which will return a '3')
simpler:
SELECT *
FROM #c
WHERE
NOT EXISTS (
SELECT c
FROM #a
EXCEPT
SELECT c
FROM #b
)
AND NOT EXISTS (
SELECT c
FROM #b
EXCEPT
SELECT c
FROM #a
)
If instead of the above rows I had this:
INSERT INTO #a SELECT 1
INSERT INTO #b SELECT 1 UNION ALL SELECT 2
I shouldn't have a 3 as an answer...
Please tell me there's a better way!!
Regards,
Pablo
--
:whizzy: adj. (alt. `wizzy') [Sun] Describes a {cuspy} program;
one that is feature-rich and well presented.
-- from The on-line Hacker Jargon File V423
Pablo Montilla
www.odyssey.com.uyThe SQL 2005 operator INTERSECT will compare two result sets and only return
rows that are identical in both sets. NOte that BLOB data types cannot be
compared using UNION, INTERSECT, or EXCEPT.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Pablo Montilla" <melkor@.odyssey.com.uy> wrote in message
news:op.tyyy7xoscj6shk@.chimera.odyssey.com.uy...
> Hello, I don't know how to do this and I'm sure its dead simple.
> I have two tables that I'll use on a WHERE condition, and I need to know
> that the rows in one are exactly the same rows in the other. I don't want
> to return results in my query unless there is an exact match between those
> two single column tables...
> I've been using a two select ... except two solve my problem, but I know
> there must be a simpler way, and obviously I'm stuck and unable to think..
> An example:
> CREATE TABLE #a(c int)
> CREATE TABLE #b(c int)
> CREATE TABLE #c(c int)
> If I have this:
> INSERT INTO #a SELECT 1 UNION ALL SELECT 2
> INSERT INTO #b SELECT 1 UNION ALL SELECT 2
> INSERT INTO #c SELECT 3
> Then I want a condition that will make this (which will return a '3')
> simpler:
>
> SELECT *
> FROM #c
> WHERE
> NOT EXISTS (
> SELECT c
> FROM #a
> EXCEPT
> SELECT c
> FROM #b
> )
> AND NOT EXISTS (
> SELECT c
> FROM #b
> EXCEPT
> SELECT c
> FROM #a
> )
> If instead of the above rows I had this:
> INSERT INTO #a SELECT 1
> INSERT INTO #b SELECT 1 UNION ALL SELECT 2
> I shouldn't have a 3 as an answer...
> Please tell me there's a better way!!
> Regards,
> Pablo
> --
>
> :whizzy: adj. (alt. `wizzy') [Sun] Describes a {cuspy} program;
> one that is feature-rich and well presented.
> -- from The on-line Hacker Jargon File V423
> Pablo Montilla
> www.odyssey.com.uy|||On Thu, 20 Sep 2007 17:36:38 -0300, Geoff N. Hiten
<SQLCraftsman@.gmail.com> wrote:
> The SQL 2005 operator INTERSECT will compare two result sets and only
> return rows that are identical in both sets. NOte that BLOB data types
> cannot be compared using UNION, INTERSECT, or EXCEPT.
>
OK, but I need to know if the two sets of rows are identical. If I
interesct, how do I know if the intersection its formed by all the rows in
both tables?
Thanks,
Pablo
:whizzy: adj. (alt. `wizzy') [Sun] Describes a {cuspy} program;
one that is feature-rich and well presented.
-- from The on-line Hacker Jargon File V423
Pablo Montilla
www.odyssey.com.uy|||OK, use EXCEPT. Think of the three operators this war:
UNION is logical OR
INTERSECT is logical AND
EXCEPT is logical NOT
Applied to sets, not variables.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Pablo Montilla" <melkor@.odyssey.com.uy> wrote in message
news:op.tyy0okgvcj6shk@.chimera.odyssey.com.uy...
> On Thu, 20 Sep 2007 17:36:38 -0300, Geoff N. Hiten
> <SQLCraftsman@.gmail.com> wrote:
>> The SQL 2005 operator INTERSECT will compare two result sets and only
>> return rows that are identical in both sets. NOte that BLOB data types
>> cannot be compared using UNION, INTERSECT, or EXCEPT.
> OK, but I need to know if the two sets of rows are identical. If I
> interesct, how do I know if the intersection its formed by all the rows in
> both tables?
> Thanks,
> Pablo
>
> --
>
> :whizzy: adj. (alt. `wizzy') [Sun] Describes a {cuspy} program;
> one that is feature-rich and well presented.
> -- from The on-line Hacker Jargon File V423
> Pablo Montilla
> www.odyssey.com.uy|||Many thanks for your response. So there is no easier, simpler way than to
do two except checks, to see if any element is on one table but not in the
other?
Pity...;o)
Regards,
Pablo
His mind is so open that the wind whistles through it.
-- Heywood Braun
Pablo Montilla
www.odyssey.com.uy