Using EXISTS

By Bill Graziano on 12 October 2003 | Tags: INSERT


Many times you're required to write query to determine if a record exists. Typically you use this to determine whether to insert or update a records. Using the EXISTS keyword is a great way to accomplish this.

Here's a simple example from the pubs database using EXISTS:
if EXISTS (select *
	from authors
	where au_id = '172-32-1176')
  Print 'Record exits - Update'
ELSE
  Print 'Record doesn''t exist - Insert'

The EXISTS function takes one parameter which is a SQL statement. If any records exist that match the criteria it returns true, otherwise it returns false. This gives you a clean, efficient way to write a stored procedure that does either an insert or update.

The other benefit of EXISTS is that once it finds a single record that matches it stops processing. This doesn't have a huge impact if you're checking on a primary key. It does have a big impact if you're checking for existance based on another field. Consider the following two queries:

if exists (select *
	from authors
	where state = 'ca')
  Print 'Record exits'
ELSE
  Print 'Record doesn''t exist'

if (select count(*)
	from authors
	where state = '172-32-1176') > 0
  Print 'Record exits'
ELSE
  Print 'Record doesn''t exist'

In the pubs database there are only 23 records in the authors table. Even with that small number of records, the IF EXISTS version runs 4 times faster than selecting a count. This is because it stops as soon as it finds a single record that matches the criteria. The second statement must process all the rows that match.

So there's a quick way to determine if a row exists matching specific criteria.


Related Articles

Fast CSV Import in PowerShell to SQL Server (18 March 2014)

How to Insert Values into an Identity Column in SQL Server (6 August 2007)

Using Views to Enforce Business Rules (9 April 2007)

Using the OUTPUT Clause to Capture Identity Values on Multi-Row Inserts (14 August 2006)

Anticipating Primary Key Violations (23 November 2003)

Using SELECT to INSERT records (15 August 2000)

Other Recent Forum Posts

Checkmark for guaranteed SR = WR (76m)

How to connect to git in SQL Server 2016/2017 without using any third party tool (84m)

Sql restart (6h)

Excel column wise data save in rows (1d)

Date timzone conversion (1d)

Object cannot be cast from DBNULL to other types coming randomly in SSIS Package-Migrated from VS 2008 to 2015,SQL 2008R2 to SQL2016 on 1st run only (2d)

Error in sp procedure- Msg 50000, Level 16, State 1, Procedure spCheckDBInfo, Line 193 [Batch Start Line 0 (2d)

Two records into a single record? (2d)

- Advertisement -