Result Set Comparisons 1

[kermitforney]

I need to figure out if there is another way to perform this query without using Temp Tables.

I have a query that compares two result sets and INSERT's (INTO) those out into another table.

Ex.

Result Set #1

SELECT's all people WHO should have baseball equipment.
INTO #Baseball1

Result Set #2

SELECT's all people who DO have baseball equipment.
INTO #Baseball2

Comparison

LEFT OUTER JOIN WHERE RESULT SET #2 (#Baseball2) IS NULL
(Gimme those who should have equipment, but don't and place them into a table to correct later.)

INSERT INTO
DATA VALIDATION TABLE

I Hope this wasn't too confusing, I was trying to figure out how to word it without pasting TSQL. )

 

[SQLSister]

You could use derived tables instead.
psuedocode

insert mytable (field1, field2)
select a.field1, a.field2 from
(select field1, field2, idfield from anothertable where somefield = 'yes') a
left join 
(select  idfield from anothertable2 where somefield = 'no')
b
on a.idfield = b.idfield
where b.idfield is null

"NOTHING is more important in a database than integrity." ESquared

 

[kermitforney]

Am I correct in saying, Derived tables are quicker or more efficient in that they are not materialized? Unlike Virtual tables in the tempdb.

 

[kermitforney]

Using the derived tables, the query is now 83% faster than the exact same query using temp tables.

Thanks, SQL Sister!!

 

[JackVam]

More steps occur in using temp tables rather than derived table.
A derived table is a sort of Select statement within another Select statement and manages to complete it's tasks within memory,

The temp tables requires disk action

Jack Vamvas

All the IT jobs in one place -

http://www.ITjobfeed.com

 

[SQLSister]

Derived tables are one of my favorite techniques although they can be a little hard to understand at first becasue the query can look so long and convoluted. I find it is often easiest to start with temp tables to get the queries right and then replace the name of the temp table inthe final query with the query that populated the temp table (minus the into #temp or the insert statement). That way I can think in terms of what chunks of data do I need to have to build my final result before trying to combine them all at once in one giant query, sort of like what we did here.

"NOTHING is more important in a database than integrity." ESquared

 

[JackVam]

Also exploits the set based approach

Jack Vamvas

All the IT jobs in one place -

http://www.ITjobfeed.com