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

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 EXIS开发者_Python百科TS( 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

---

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.

---

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.

---

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

---

If there are order_details with an order_id equal to 11032, your first statement will run :

DELETE FROM DBO.ORDER_DETAILS WHERE ORDER_ID = 11032

If there are not order_details with an order_id equal to 11032, then your second statement will run. Note that this is an empty set since you just checked that there were not orders with that order_id.

It's actually going to be easier, in this example, to just run the DELETE - the IF EXISTS and IF NOT EXISTS are superfluous.

---

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

Which is better among these both?

The one that gives you the appropriate semantics.

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

When you need to check the non-emptiness or emptiness of a result set.

---

"EXISTS simply tests whether the inner query returns any row. If it does, then the outer query proceeds. If not, the outer query does not execute, and the entire SQL statement returns nothing." See here. NOT EXISTS is the negation of EXISTS of course.

What the first statement does is that it will issue a DELETE query if the order can be found. The second one does not have any sense as it will issue the query on the ORDER when it does not exist.

---

This is certainly one way to use an EXISTS. I'm not sure that the second one would do anything though.

However, you could just

DELETE FROM DBO.ORDER_DETAILS WHERE ORDER_ID = 11032

and remove the EXISTS altogether, unless you wanted to perform

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 what is shown.

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

As far as performance, in the context in which you use the EXISTS neither one has a better performance since it's really just evaluating whether the result set from the SELECT is NULL or not.

There is another use of EXISTS in which NOT EXISTS is much less efficient than EXISTS and can be effectively replaced with a better performing phrase. I'm referring to when you use NOT EXISTS in the WHERE clause of a statment. In this cause you would be better off performing a LEFT JOIN (instead of the INNER JOIN you likely have) and filter WHERE rightTable.SomeColumn IS NULL.