October 14, 2009 at 7:28 pm
Do you believe that these 2 sets of scripts offer the same result? 🙂
--FIRST SCRIPT
SELECT DISTINCT
UC_Letter.CTContactID,
CT_Contact.FirstName,
CT_Contact.FamilyName,
AD_MailingAddress.Email,
AD_MailingAddress.MobilePhone,
SD_Admission.SDAdmissionCD,
SD_Admission.AdmissionDesc
FROM UC_LetterQueue
INNER JOIN UC_Letter ON UC_LetterQueue.UCLetterID = UC_Letter.UCLetterID
INNER JOIN UC_LetterType ON UC_Letter.UCLetterTypeID = UC_LetterType.UCLetterTypeID
INNER JOIN CT_Contact ON UC_Letter.CTContactID = CT_Contact.CTContactID
INNER JOIN SD_StudentProgramme ON CT_Contact.CTContactID = SD_StudentProgramme.CTContactID
INNER JOIN SD_Admission ON SD_StudentProgramme.SDAdmissionCD = SD_Admission.SDAdmissionCD
LEFT JOIN AD_MailingAddress ON CT_Contact.CTContactID = AD_MailingAddress.CTContactID
WHERE UC_LetterQueue.NotifyAttemptCntr = 2
AND UC_LetterType.UCLetterTypeID = 1 -- select only Domestic OOP
AND DATEDIFF(dd,UC_Letter.CreatedDate,GETDATE()) >= 7 --after 7 days of letter creation
AND SD_StudentProgramme.YearAppliedFor = '2010'
AND SD_Admission.SDAdmissionCD NOT IN ('E','X','S','L','D','T')
AND AD_MailingAddress.Email NOT LIKE '%_@__%.__%' -- invalid or blank email
AND AD_MailingAddress.MobilePhone NOT LIKE '02%' -- invalid or blank mobile
ORDER BY UC_Letter.CTContactID
-- SECOND SCRIPT
SELECT DISTINCT
UC_Letter.CTContactID,
CT_Contact.FirstName,
CT_Contact.FamilyName,
ISNULL(AD_MailingAddress.Email,'') AS Email,
ISNULL(AD_MailingAddress.MobilePhone,'') AS MobilePhone,
SD_Admission.SDAdmissionCD,
SD_Admission.AdmissionDesc
INTO #result
FROM UC_LetterQueue
INNER JOIN UC_Letter ON UC_LetterQueue.UCLetterID = UC_Letter.UCLetterID
INNER JOIN UC_LetterType ON UC_Letter.UCLetterTypeID = UC_LetterType.UCLetterTypeID
INNER JOIN CT_Contact ON UC_Letter.CTContactID = CT_Contact.CTContactID
INNER JOIN SD_StudentProgramme ON CT_Contact.CTContactID = SD_StudentProgramme.CTContactID
INNER JOIN SD_Admission ON SD_StudentProgramme.SDAdmissionCD = SD_Admission.SDAdmissionCD
LEFT JOIN AD_MailingAddress ON CT_Contact.CTContactID = AD_MailingAddress.CTContactID
WHERE UC_LetterQueue.NotifyAttemptCntr = 2
AND UC_LetterType.UCLetterTypeID = 1 -- select only Domestic OOP
AND DATEDIFF(dd,UC_Letter.CreatedDate,GETDATE()) >= 7 --after 7 days of letter creation
AND SD_StudentProgramme.YearAppliedFor = '2010'
AND SD_Admission.SDAdmissionCD NOT IN ('E','X','S','L','D','T')
ORDER BY UC_Letter.CTContactID
--final select
SELECT * FROM #result
WHERE Email NOT LIKE '%_@__%.__%' -- invalid or blank email
AND MobilePhone NOT LIKE '02%' -- invalid or blank mobile
October 14, 2009 at 8:02 pm
no
October 15, 2009 at 6:49 am
Me neither.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 15, 2009 at 7:54 am
Depending on the data present in the tables, the only significant differences between the two are the IsNull operators in the second one. If there aren't any nulls in those columns, then why wouldn't they give the same results?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 15, 2009 at 7:05 pm
whatever size or type of data I use, they present the same result. You might disagree, but the first one is only a shortcut in my opinion.
Also, had a debate with a colleague that these 2 queries below are just the same:
select table1.column1, table2.column1
from table1 InnerJoin table2 ON table1.primKey = table2.foreKey
and table2.column2 = 'testvalue'
select table1.column1, table2.column1
from table1 InnerJoin table2 ON table1.primKey = table2.foreKey
WHERE table2.column2 = 'testvalue'
Would you agree?
October 15, 2009 at 10:04 pm
They are the same in as far as neither will run.. InnerJoin pretty much makes sure of that.. 😛
CEWII
October 16, 2009 at 5:45 am
Layne-812700 (10/15/2009)
whatever size or type of data I use, they present the same result. You might disagree, but the first one is only a shortcut in my opinion.Also, had a debate with a colleague that these 2 queries below are just the same:
select table1.column1, table2.column1
from table1 InnerJoin table2 ON table1.primKey = table2.foreKey
and table2.column2 = 'testvalue'
select table1.column1, table2.column1
from table1 InnerJoin table2 ON table1.primKey = table2.foreKey
WHERE table2.column2 = 'testvalue'
Would you agree?
For an inner join, and on simple queries, these are absolutely equivalent. On radically more complicated procedures, where you place the filter criteria can, in some cases, due to optimizer timeouts, actually affect the execution plan.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 19, 2009 at 7:58 am
AD_MailingAddress.MobilePhone,
SD_Admission.SDAdmissionCD,
vs
ISNULL(AD_MailingAddress.Email,'') AS Email,
ISNULL(AD_MailingAddress.MobilePhone,'') AS MobilePhone,
That means the results will only be the same if neither of these columns is ever null. Regardless of syntactical structure, this means the two are not "the same".
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 19, 2009 at 9:09 pm
Grant Fritchey (10/16/2009)
Layne-812700 (10/15/2009)
whatever size or type of data I use, they present the same result. You might disagree, but the first one is only a shortcut in my opinion.Also, had a debate with a colleague that these 2 queries below are just the same:
select table1.column1, table2.column1
from table1 InnerJoin table2 ON table1.primKey = table2.foreKey
and table2.column2 = 'testvalue'
select table1.column1, table2.column1
from table1 InnerJoin table2 ON table1.primKey = table2.foreKey
WHERE table2.column2 = 'testvalue'
Would you agree?
For an inner join, and on simple queries, these are absolutely equivalent. On radically more complicated procedures, where you place the filter criteria can, in some cases, due to optimizer timeouts, actually affect the execution plan.
For an inner join? So if I replace the inner join with left join, would they still be equivalent? Thanks
October 20, 2009 at 6:09 am
Layne-812700 (10/19/2009)
Grant Fritchey (10/16/2009)
Layne-812700 (10/15/2009)
whatever size or type of data I use, they present the same result. You might disagree, but the first one is only a shortcut in my opinion.Also, had a debate with a colleague that these 2 queries below are just the same:
select table1.column1, table2.column1
from table1 InnerJoin table2 ON table1.primKey = table2.foreKey
and table2.column2 = 'testvalue'
select table1.column1, table2.column1
from table1 InnerJoin table2 ON table1.primKey = table2.foreKey
WHERE table2.column2 = 'testvalue'
Would you agree?
For an inner join, and on simple queries, these are absolutely equivalent. On radically more complicated procedures, where you place the filter criteria can, in some cases, due to optimizer timeouts, actually affect the execution plan.
For an inner join? So if I replace the inner join with left join, would they still be equivalent? Thanks
Since you're predicating the JOIN in the values from the second table, no, it'll basically give you an innner join in that case.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 20, 2009 at 12:49 pm
With an OUTER join, it does indeed make a difference whether the criteria is in the ON clause or the WHERE clause. Let's construct an example with data to actually see the difference.
create table #table1 (primkey int, column1 char(4))
create table #table2 (forekey int, column1 char(4), column2 char(10))
insert #table1
select 1,'AAAA' union all
select 2,'BCDE' union all
select 3,'CDEF'
insert #table2
select 1,'Agg', 'ProdValue' union all
select 2,'Bgg', 'TestValue' union all
select 3,'Cggg', 'ProdValue' union all
select 4,'Dggg', 'TestValue'
--Q1
select #table1.column1, #table2.column1
from #table1 Inner Join #table2 ON #table1.primKey = #table2.foreKey
and #table2.column2 = 'testvalue'
--Q2
select #table1.column1, #table2.column1
from #table1 Inner Join #table2 ON #table1.primKey = #table2.foreKey
WHERE #table2.column2 = 'testvalue'
--Q3
select #table1.column1, #table2.column1
from #table1 Left Outer Join #table2 ON #table1.primKey = #table2.foreKey
and #table2.column2 = 'testvalue'
--Q4
select #table1.column1, #table2.column1
from #table1 Left Outer Join #table2 ON #table1.primKey = #table2.foreKey
WHERE #table2.column2 = 'testvalue'
The results for queries Q1, Q2, and Q4 are identical: a single row:column1 column1
------- -------
BCDE Bgg
But the results of query Q3 include all three rows from table1:column1 column1
------- -------
AAAA NULL
BCDE Bgg
CDEF NULL
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply