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 ex: 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: (NOT EXISTS)

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 the IF EXISTS or IF NOT EXISTS return? Which is better among these both? When to use IF EXISTS and when to use IF NOT EXISTS

Answer 1

Here are 4 examples illustrating when you would use IF EXISTS and when you would use IF NOT EXISTS:

A) Delete related records from more than 1 table:

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

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

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

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

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

D) Upsert (=insert or update) record, depending on existence:

IF EXISTS (SELECT TOP(1) 1 FROM Table1 WHERE ORDER_ID = 11032) BEGIN
    UPDATE Table1 SET Field1='X' WHERE ORDER_ID = 11032
    -- possibly more statements following here ...
END
ELSE BEGIN
    INSERT INTO Table1(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 statement, but I think it's a bit complicated to use.

NOTES:

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

Answer 2

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

Answer 3

Both statements will return a boolean true/false result.

EXISTS returns true if the result set IS NOT empty.

NOT EXISTS Is a negated operation, so it returns true if the result set IS empty