Exists and IF Exists

• parthi-1705

  SSCrazy Eights

  Points: 9277

  More actions

  January 10, 2011 at 6:41 am

  #232838

  Hi

  Which is better in the below query

  --- This eg is from HELP File

  USE AdventureWorks ;

  GO

  SELECT a.FirstName, a.LastName

  FROM Person.Contact AS a

  WHERE EXISTS

  (SELECT *

  FROM HumanResources.Employee AS b

  WHERE a.ContactId = b.ContactID

  AND a.LastName = 'Johnson');

  GO

  USE AdventureWorks ;

  GO

  SELECT a.FirstName, a.LastName

  FROM Person.Contact AS a

  WHERE EXISTS

  (SELECT 1

  FROM HumanResources.Employee AS b

  WHERE a.ContactId = b.ContactID

  AND a.LastName = 'Johnson');

  GO

  in the same way

  IF EXISTS (SELECT * FROM Tab_Name)

  Begin

  .....

  End

  OR

  IF EXISTS (SELECT 1 FROM Tab_Name)

  Begin

  .....

  End

  Which is better

  selecting all columns or single Column which is better.

  Thanks

  Parthi

  Thanks
  Parthi

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  January 10, 2011 at 7:31 am

  #1271385

  They're exactly the same. The columns are ignored in an EXISTS.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• nekonecny

  Right there with Babe

  Points: 720

  More actions

  January 10, 2011 at 7:47 am

  #1271396

  I prefer:

  IF EXISTS(SELECT TOP 1 1 FROM ...

• parthi-1705

  SSCrazy Eights

  Points: 9277

  More actions

  January 10, 2011 at 7:48 am

  #1271398

  GilaMonster (1/10/2011)

  ---

  They're exactly the same. The columns are ignored in an EXISTS.

  So if there is millions of records are there we can prefer either option ,is it is good???

  Now i am having only 1000+records for testing i need to Implement this on million records to check whether EXISTS condition works perfectly

  Thanks

  Parthi

  Thanks
  Parthi

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  January 10, 2011 at 8:01 am

  #1271409

  As I said, they're exactly the same. During the parsing of the query any reference to columns is stripped out of the exists.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  January 10, 2011 at 8:02 am

  #1271410

  nekonecny (1/10/2011)

  ---

  I prefer:

  IF EXISTS(SELECT TOP 1 1 FROM ...

  Why?

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• Latheesh NK

  SSCertifiable

  Points: 6559

  More actions

  January 10, 2011 at 9:55 am

  #1271492

  Those are exactly same. It does not matter, whether its * or 1 in the case of Exists.

• nekonecny

  Right there with Babe

  Points: 720

  More actions

  January 10, 2011 at 1:00 pm

  #1271579

  I need only one row (top 1), if i use "EXISTS". Is Optimizer so clever to eliminate other potential rows?:-)

• Elliott Whitlow

  SSC Guru

  Points: 102296

  More actions

  January 10, 2011 at 1:18 pm

  #1271592

  nekonecny (1/10/2011)

  ---

  I need only one row (top 1), if i use "EXISTS". Is Optimizer so clever to eliminate other potential rows?:-)

  Yes, it is.. An EXISTS check completes as soon as a single row is found, it doesn't need to look any further and it knows it. The logic is "short-circuited". In either case as soon as a single row is found matching the criteria it returns TRUE. It seems TOP might actually cost you performance..

  CEWII

• nekonecny

  Right there with Babe

  Points: 720

  More actions

  January 10, 2011 at 1:51 pm

  #1271617

  Elliott Whitlow (1/10/2011)

  ---

  nekonecny (1/10/2011)

  ---

  I need only one row (top 1), if i use "EXISTS". Is Optimizer so clever to eliminate other potential rows?:-)

  Yes, it is.. An EXISTS check completes as soon as a single row is found, it doesn't need to look any further and it knows it. The logic is "short-circuited". In either case as soon as a single row is found matching the criteria it returns TRUE. It seems TOP might actually cost you performance..

  CEWII

  Thanks! I am smarter now:-)

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  January 10, 2011 at 1:53 pm

  #1271619

  Elliott Whitlow (1/10/2011)

  ---

  nekonecny (1/10/2011)

  ---

  I need only one row (top 1), if i use "EXISTS". Is Optimizer so clever to eliminate other potential rows?:-)

  Yes, it is.. An EXISTS check completes as soon as a single row is found, it doesn't need to look any further and it knows it. The logic is "short-circuited". In either case as soon as a single row is found matching the criteria it returns TRUE. It seems TOP might actually cost you performance..

  I have a blog post planned on this (use of Top 1 with Exists)

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• anuraganu.tiwari

  SSC Enthusiast

  Points: 106

  More actions

  January 10, 2011 at 4:39 pm

  #1271664

  Cost Analysis Among EXISTS (SELECT 1), EXISTS (SELECT COUNT(1)), EXISTS (SELECT *) AND EXISTS (SELECT TOP 1)