Difference between "IF EXISTS" and "IF NOT EXISTS" in SQL?

Question

I am very new to SQL. I want to know what happens when I use "IF EXISTS" or "IF NOT EXISTS". For example: what is the difference between the following two statements:

Statement 1: (EXISTS)

IF EXISTS( SELECT ORDER_ID FROM DBO.ORDER_DETAILS WHERE ORDER_ID = 11032 ) BEGIN DELETE FROM DBO.ORDER_DETAILS WHERE ORDER_ID = 11032 END 

Statement 2: (DOES NOT EXIST)

 IF NOT EXISTS( SELECT ORDER_ID FROM DBO.ORDER_DETAILS WHERE ORDER_ID = 11032 ) BEGIN DELETE FROM DBO.ORDER_DETAILS WHERE ORDER_ID = 11032 END 

What will return IF EXISTS or IF NOT EXISTS ? Which is better among both of them? When to use IF EXISTS and when to use IF NOT EXISTS

Answer 1

IF EXISTS verifies that the result set is not empty, and IF NOT EXISTS verifies that the result set is empty.

Which is better among both?

One that gives you relevant semantics.

When to use IF EXISTS and when to use IF NOT EXISTS

If you need to check the nonemptiness or emptiness of the result set.

Answer 2

You need the first statement. Basically, "IF EXISTS" returns true if the query returns 1 or more rows, so in your example it will return a single row (containing a field with a value of 1), therefore it will execute the delete operator as you wish.

Answer 3

Both operators return the boolean result true / false.

EXISTS returns true if the result set is NOT empty.

NOT EXISTS is a negative operation, so returns true if the result set IS empty

Answer 4

If there is order_details with ordinal_id equal to 11032, your first statement will run:

 DELETE FROM DBO.ORDER_DETAILS WHERE ORDER_ID = 11032 

If there is no order_details with ordinal_id equal to 11032, then your second statement will be launched. Note that this is an empty set, as you just checked that there were no orders with this order_id.

In this example, it will actually be easier to just run DELETE - IF EXISTS and IF NOT EXISTS are redundant.

Answer 5

Here are 4 examples that illustrate when you will use IF EXISTS and when you will use IF NOT EXISTS:

A) Delete related records from more than one table:

 IF EXISTS (SELECT TOP(1) 1 FROM Table 1 WHERE ORDER_ID = 11032) BEGIN DELETE FROM Table 1 WHERE ORDER_ID = 11032 DELETE FROM Table 2 WHERE ORDER_ID = 11032 -- possibly more statements following here ... END 

B) Update a record in more than 1 table, if it exists:

 IF EXISTS (SELECT TOP(1) 1 FROM Table 1 WHERE ORDER_ID = 11032) BEGIN UPDATE Table 1 SET Field1='X' WHERE ORDER_ID = 11032 UPDATE Table 2 SET Field2='Y' WHERE ORDER_ID = 11032 -- possibly more statements following here ... END 

C) Insert a record into more than 1 table if it does not exist:

 IF NOT EXISTS (SELECT TOP(1) 1 FROM Table 1 WHERE ORDER_ID = 11032) BEGIN INSERT INTO Table 1(Field1, Field2, ORDER_ID) VALUES ('A', 'B', 11032) INSERT INTO Table 2(Field3, Field4, ORDER_ID) VALUES ('X', 'Y', 11032) -- possibly more statements following here ... END 

D) Upsert (= insert or update) entry, depending on availability:

 IF EXISTS (SELECT TOP(1) 1 FROM Table 1 WHERE ORDER_ID = 11032) BEGIN UPDATE Table 1 SET Field1='X' WHERE ORDER_ID = 11032 -- possibly more statements following here ... END ELSE BEGIN INSERT INTO Table 1(Field1, Field2, ORDER_ID) VALUES ('X', 'B', 11032) -- possibly more statements following here ... END 

Instead of the above statement (case D), you can also use the new MERGE operator , but I think it is a little difficult to use.

NOTES:

• If only one table is affected, you will not use EXIST in any of the above examples except for the upsert D example.
• SELECT TOP (1) 1 FROM ... is more efficient, because it is interrupted after the 1st match is found, then it returns only number 1 (which is more efficient for selecting, for example, the NVARCHAR (max) field)
• You can see that only in example C) you are forced to use IF NOT EXISTS(...) , all other examples use IF EXISTS(...) , which is more efficient.

Answer 6

This is definitely one way to use EXISTS . I'm not sure the second will do anything, though.

However you could just

 DELETE FROM DBO.ORDER_DETAILS WHERE ORDER_ID = 11032 

and completely remove EXISTS if you do not want to execute

 IF EXISTS ( SELECT ORDER_ID FROM ORDERS WHERE ORDER_ID = 11032 ) BEGIN DELETE FROM DBO.ORDER_DETAILS WHERE ORDER_ID = 11032 DELETE FROM DBO.ORDERS WHERE ORDER_ID = 11032 END 

or your actual code was more complex than the one shown.

Your second statement will never delete anything, because if the lines , it will evaluate to FALSE and does not execute DELETE , and if not rows, it will evaluate to TRUE and execute DELETE , which will do nothing because there are no rows.

In terms of performance, in the context in which you use EXISTS , none of them have better performance, since it really just evaluates whether the result set is SELECT NULL or not.

There is another use of EXISTS in which NOT EXISTS much less effective than EXISTS and can be effectively replaced with a more effective phrase. I mean when you use NOT EXISTS in a WHERE statute. In this case, you'd better do a LEFT JOIN (instead of the INNER JOIN that you have) and the WHERE rightTable.SomeColumn IS NULL filter WHERE rightTable.SomeColumn IS NULL .

Answer 7

"EXISTS just checks to see if an internal query returns a row. If so, then the external query continues. If not, the external query is not executed and the entire SQL statement returns nothing." See here . NOT EXISTS is, of course, the negation of EXISTS.

What the first statement does is that it issues a DELETE request if the order can be found. The second one makes no sense, since it will issue a request for ORDER when it does not exist.