rewriting the code in SUb queries

Question

Post reply

rewriting the code in SUb queries

athatipamula

Grasshopper

Points: 11
More actions
May 24, 2010 at 2:43 pm

#386999

I have a code using except function
can any one rewrite the code using sub queries?
I am using 2 databases and 2 different tables
here is the code:
SELECT [MODE],[NUMBER],[DESCRIPTION],[UNITSIZE]
FROM [database1].[DBO].[table1]
EXCEPT
SELECT [_ZONE],[MODE],[NUMBER],[DESCRIPTION]
FROM [database2].[DBO].[table2]
WHERE [ENTRYDATE] = ( SELECT MAX (ENTRYDATE) FROM [database2] . [DBO] . [table2] )

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

What would be the reason to rewrite it?

Looks like a well-performing approach...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Lowell SSC Guru Points: 323547 More actions · Answer 2

I agree with Lutz;

changing it to say, an inner join doesn't make that much of a differenc:

SELECT [MODE],[NUMBER],[DESCRIPTION],[UNITSIZE]

FROM [database1].[DBO].[table1]

EXCEPT

SELECT [_ZONE],[MODE],[NUMBER],[DESCRIPTION]

FROM [database2].[DBO].[table2] t1

INNER JOIN( SELECT MAX (ENTRYDATE) AS ENTRYDATE FROM [database2] . [DBO] . [table2] ) MyAlias

ON t1.ENTRYDATE = MyAlias.ENTRYDATE

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

WayneS SSC Guru Points: 95461 More actions · Answer 3

athatipamula (5/24/2010)
I have a code using except function
can any one rewrite the code using sub queries?
I am using 2 databases and 2 different tables
here is the code:
SELECT [MODE],[NUMBER],[DESCRIPTION],[UNITSIZE]
FROM [database1].[DBO].[table1]
EXCEPT
SELECT [_ZONE],[MODE],[NUMBER],[DESCRIPTION]
FROM [database2].[DBO].[table2]
WHERE [ENTRYDATE] = ( SELECT MAX (ENTRYDATE) FROM [database2] . [DBO] . [table2] )

One thing that I immediately see that you may not be using this correctly. The except operator compares the data from the first select column-by-column with the data returned by the second select. This means that you are comparing:

database1.dbo.table1.mode with database2.dbo.table2.[_zone]

database1.dbo.table1.number with database2.dbo.table2.mode

database1.dbo.table1.description with database2.dbo.table2.number

database1.dbo.table1.unitsize with database2.dbo.table2.description

seems to me that you should be comparing:

database1.dbo.table1.mode with database2.dbo.table2.mode

database1.dbo.table1.number with database2.dbo.table2.number

database1.dbo.table1.description with database2.dbo.table2.description

and you should not be including:

database1.dbo.table1.unitsize

database2.dbo.table2.[_zone]

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Paul White SSC Guru Points: 150468 More actions · Answer 4

This example shows an exactly equivalent logical representation.

Notice that exactly the same physical execution plan is produced.

Also notice that EXCEPT does an implicit DISTINCT on the first table.

DECLARE @A TABLE (A INT NULL);

DECLARE @B TABLE (B INT NULL);

DECLARE @C TABLE (C INT NULL);

-- Original query

SELECT A.A

FROM @A A

EXCEPT

SELECT B.B

FROM @B B

WHERE B.B = (SELECT MAX(C.C) FROM @C C);

-- Exact equivalent

SELECT A.A

FROM (

-- EXCEPT applies a DISTINCT / GROUP BY to the first table

SELECT A.A

FROM @A A

GROUP BY A.A

) A

WHERE NOT EXISTS

(

SELECT *

FROM @B B

WHERE B.B = A.A

AND B.B =

(

SELECT MAX(C.C)

FROM @C C

)

);

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi