SQL IF EXISTS Decision Structure: Explained with Examples

The SQL IF EXISTS tool is great to know for managing the flow of control of your SQL code.

In this very brief tutorial, we’ll discuss everything you need to know about the IF EXISTS decision structure in SQL Server. We’ll discuss these topics:

1. What is the SQL IF EXISTS decision structure?
2. Examples of using IF EXISTS
3. Tips and tricks

Let’s take it from the top.

1. What is the SQL IF EXISTS decision structure?

The IF EXISTS decision structure will execute a block of SQL code only if an inner query returns one or more rows. If the inner query returns an empty result set, the block of code within the structure is skipped.

The inner query used with the IF EXISTS structure can be anything you need it to be. The point is if the inner query returns something, the block of code inside the structure will execute. Otherwise, it’s skipped.

The syntax

The syntax for the IF EXISTS structure is very simple:

IF EXISTS (<inner query>)
BEGIN
<block of code to execute if the inner query returned a result set>
END

Let’s look at some examples.

2. Examples of using IF EXISTS

Take a look at the following two tables called Products and Orders:

Here are the CREATE and INSERT statements for these tables if you want to create the data in your own environment and follow along:

CREATE TABLE Products(
ProductID int IDENTITY(20,2) NOT NULL,
ProductName varchar(20) NULL,
Price decimal(5, 2) NULL
)

CREATE TABLE Orders(
OrderID int IDENTITY(100,10) NOT NULL,
CustID int NULL,
ProdID int NULL,
Qty tinyint NULL,
Orderdate datetime NULL
)

INSERT Products (ProductName, Price) 
VALUES 
('Large Bench', 198.00),
('Small Bench', 169.40),
('Coffee Table', 220.00),
('Side Tables', 265.20),
('Coat Rack', 45.00)

INSERT Orders (CustID, ProdID, Qty, Orderdate) 
VALUES 
(55, 22, 1, '2021-06-01'),
(60, 28, 2, '2021-06-06'),
(75, 26, 1, '2021-06-13'),
(50, 20, 1, '2021-07-01'),
(55, 28, 1, '2021-07-06'),
(65, 24, 1, '2021-07-14'),
(55, 26, 1, '2021-07-18'),
(50, 26, 1, '2021-07-24'),
(70, 24, 1, '2021-08-06'),
(70, 26, 1, '2021-08-06'),
(70, 22, 3, '2021-09-01')

Let’s say we have the following View called vProductIncome that tells us a summary of income we’ve made from each product:

CREATE VIEW vProductIncome
AS
SELECT P.ProductName, SUM(O.Qty * P.Price) as 'Income'
FROM Products AS P
INNER JOIN Orders as O
ON P.ProductID = O.ProdID
GROUP BY P.ProductName

We would like to write a script that creates this View only if it doesn’t already exist in the current environment. The script will reach out to the sys.objects table to check if the View already exists in the database. If it does, then we don’t try to create it. But if it doesn’t, we create the View.

We can use the IF EXISTS structure to do it:

IF EXISTS ( SELECT * FROM sys.objects where name = 'vProductIncome' and type = 'V')
BEGIN
PRINT 'vProductIncome View already exists within database'
END
ELSE
BEGIN
EXECUTE(
'CREATE VIEW vProductIncome
AS
SELECT P.ProductName, SUM(O.Qty * P.Price) as ''Income''
FROM Products AS P
INNER JOIN Orders as O
ON P.ProductID = O.ProdID
GROUP BY P.ProductName'
)
END

So if I execute this code for the very first time, the View will be created because it’s not already in my system:

The View has been created, and here is what we get back if we query the View:

If we run the same script again, however, we get the message telling us that the View already exists:

IF EXISTS is great for creating (or not creating) objects in a database

A script like this can be very useful for creating objects within a database. Some clients may already have the View in their database, meaning we don’t need to create it. But, of course, other clients may not have the View in their environment.

The IF EXISTS decision structure allows us to run this script in either environment and not receive error messages. For example, if we didn’t have the IF EXISTS structure in place and simply always ran the CREATE VIEW statement for everyone, the folks who already have this View will receive an error message saying:

There is already an object named ‘vProductIncome’ in the database.

Not good! We do our best to avoid generating error messages in our code.

Using SQL IF EXISTS to DROP an object if it exists

We could also write a script that simply always creates the View. We can use IF EXISTS to check if the View already exists in the database, and if it does, we drop it. Then, we have a CREATE VIEW statement that runs right after. Here’s what I mean:

The View already existed in my database, so we dropped it. Then, there is the CREATE VIEW statement that is outside of the IF EXISTS decision structure that simply always runs. When we reach that point in the code, the View is guaranteed to not exist.

I can query the View to see that it was created successfully:

If we were to run the script again, the View would be dropped again and re-created again. This would happen every time we run the script.

Sometimes, this is exactly what we want. Sometimes it might be easier to simply drop what is currently out there an re-create the object from scratch.

3. Tips and tricks

Here are just a few tips and tricks you should know when working with the SQL IF EXISTS decision structure:

Tip # 1: You can use IF EXISTS for something other than checking if objects exist

The examples above outline how we would use IF EXISTS for checking object existence, but you can use any query you want as the inner query. Here’s an example that checks if a certain row exists within our Products table before performing an UPDATE on that row:

IF EXISTS is commonly used for checking object existence, but understand you can use it for anything!

Tip # 2: IF NOT EXISTS is the opposite of IF EXISTS

Folks, IF NOT EXISTS is just the opposite of IF EXISTS. If the inner query does not return something, we execute the structure’s block of code.

For example, we can reverse the logic in our example:

In my case, the View did exist, so the block to create the View did not execute.

Easy peasy.

Tip # 3: You don’t need a BEGIN…END block if the body is only one line

This is true for many decision structures. If the body of your structure is only one line big, you don’t need the BEGIN…END keywords. Here’s an example:

This is a great way to reduce the lines of code and improve the readability of your code.

Next Steps:

Leave a comment if you found this tutorial helpful!

You could almost think the SQL IF EXISTS structure as a mix of the IF decision structure and the EXISTS predicate. Do you know what those are and how they work? If not, read up on them here:

• The IF…ELSE IF….ELSE Statement: Everything You Should Know 

• SQL Server EXISTS: Explained for Beginners

Thank you very much for reading!

Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!

If you have any questions, leave a comment. Or better yet, send me an email!