SQL EXISTS Explained

The SQL EXISTS predicate is used to specify a test for a non-empty set. It returns TRUE or FALSE, depending on the outcome of the test.

When we incorporate the EXISTS predicate operator into our SQL queries, we specify a subquery to test for the existence of rows. If there are any rows, then the subquery is TRUE. If there are no rows, then the subquery is FALSE.

Example

Here’s a quick example:

SELECT ArtistName 
FROM Artists ar
WHERE EXISTS (
    SELECT * FROM Albums al
    WHERE al.ArtistId = ar.ArtistId
    AND ReleaseDate < '2000-01-01'
);

Result:

+-------------+
| ArtistName  |
+-------------+
| Iron Maiden |
| AC/DC       |
| Jim Reeves  |
| Buddy Rich  |
| Tom Jones   |
+-------------+

Here, my query returns the names of all artists that released at least one album before the year 2000.

The subquery returns all albums from each artist that have a ReleaseDate prior to 2000-01-01. The outer query uses the SQL EXISTS operator to test for the existence of any rows that are returned by the subquery. Basically, we only get artists that have at least one row returned by the subquery. In this case, EXISTS returns TRUE and we get a row for that artist.

The above query is the equivalent of the following:

SELECT ArtistName 
FROM Artists ar
WHERE ar.ArtistId IN (
    SELECT ar.ArtistId FROM Albums al
    WHERE al.ArtistId = ar.ArtistId
    AND ReleaseDate < '2000-01-01'
);

Result:

+-------------+
| ArtistName  |
+-------------+
| Iron Maiden |
| AC/DC       |
| Jim Reeves  |
| Buddy Rich  |
| Tom Jones   |
+-------------+
5 rows in set (0.00 sec)

The difference is that the second query uses the SQL IN predicate instead of EXISTS. When we use IN, we specify a column name that should be returned by the subquery. When we use EXISTS, we don’t need to specify any such column.

A `NOT EXISTS` Example

We can use NOT EXISTS in order to return the opposite of EXISTS:

SELECT ArtistName 
FROM Artists ar
WHERE NOT EXISTS (
    SELECT * FROM Albums al
    WHERE al.ArtistId = ar.ArtistId
    AND ReleaseDate < '2000-01-01'
);

Result:

+------------------------+
| ArtistName             |
+------------------------+
| Allan Holdsworth       |
| Devin Townsend         |
| Maroon 5               |
| The Script             |
| Lit                    |
| Black Sabbath          |
| Michael Learns to Rock |
| Carabao                |
| Karnivool              |
| Birds of Tokyo         |
| Bodyjar                |
+------------------------+
11 rows in set (0.00 sec)

Note that this is not the same as changing the less than sign to a greater than sign in our subquery. Here’s what happens when we do that:

SELECT ArtistName 
FROM Artists ar
WHERE EXISTS (
    SELECT * FROM Albums al
    WHERE al.ArtistId = ar.ArtistId
    AND ReleaseDate > '2000-01-01'
);

Result:

+------------------------+
| ArtistName             |
+------------------------+
| Devin Townsend         |
| The Script             |
| Michael Learns to Rock |
| Tom Jones              |
| Allan Holdsworth       |
+------------------------+
5 rows in set (0.00 sec)

Only five rows are returned, compared to the 11 rows returned when using NOT EXISTS.

The logic between these two examples is different:

The first example (using NOT EXISTS) returns all artists that don’t have an album released prior to the year 2000. This could include artists that haven’t released any albums at any time.
The second example requires that the artist has released at least one album after the given date. This therefore excludes any artists who haven’t released any albums after that date, as well as artists that haven’t released any albums at all.

Oh in case you’re wondering, my database is just a sample database, and it in no way represents a complete list of the actual albums released by those artists.

The Purpose of the Subquery

When using the EXISTS operator, the purpose of the subquery is merely to return at least one row, or none at all. The contents of the subquery is not normally important. And the number of rows returned by the subquery is not important. What is important is whether at least one row is returned or not – regardless of its contents.

Because of the above, it’s unimportant which columns are included in the SELECT list. Therefore, in the above examples we could replace SELECT * with SELECT al.ArtistId, or even SELECT 1 if we wanted. The result would be the same:

SELECT ArtistName 
FROM Artists ar
WHERE EXISTS (
    SELECT 1 FROM Albums al
    WHERE al.ArtistId = ar.ArtistId
    AND ReleaseDate < '2000-01-01'
);

Result:

+-------------+
| ArtistName  |
+-------------+
| Iron Maiden |
| AC/DC       |
| Jim Reeves  |
| Buddy Rich  |
| Tom Jones   |
+-------------+

Also, it pays to bear in mind that the subquery may only be executed long enough to determine whether at least one row is returned, and not all the way to completion. In other words, the subquery may not necessarily return all rows that satisfy its criteria. Therefore, you may want to avoid using a subquery that relies on all rows being returned (such as a subquery that calls sequence functions, etc).

SQL `IF EXISTS`

Depending on your RDBMSs you may be able to use the following IF EXISTS syntax to check whether an object exists before performing some action against it.

Here’s an example:

IF EXISTS 
  (SELECT object_id FROM sys.tables
  WHERE name = 'Artists'
  AND SCHEMA_NAME(schema_id) = 'dbo')
  PRINT 'The table exists'
ELSE 
  PRINT 'The table does not exist';

Result:

The table exists

I ran this in SQL Server.

The query could be modified to perform some other, more meaningful action. For example, it could drop a table only if it exists:

IF EXISTS (
    SELECT * FROM information_schema.tables
    WHERE table_schema = 'dbo' AND table_name = 't1') 	
    DROP TABLE dbo.t1;

We might want to do this in order to avoid any errors that might occur from trying to drop a table that doesn’t actually exist.

However, many RDBMSs support the DROP TABLE IF EXISTS syntax, which is a more concise way to do it.

SQL `IF NOT EXISTS`

Similar to the SQL IF EXISTS statement, we can negate it by using NOT. This can be handy if we want to create an object instead of drop it. For example, we could use IF NOT EXISTS to create a table only if it doesn’t already exist. This will help us avoid any errors that would occur from trying to create a table with the same name of an existing one.

Example:

IF NOT EXISTS (
    SELECT * FROM sys.tables t 
    JOIN sys.schemas s ON (t.schema_id = s.schema_id) 
    WHERE s.name = 'dbo' AND t.name = 't1') 	
    CREATE TABLE dbo.t1 (
        c1 int,
        c2 varchar(10)
        );

Here, the subquery checks the sys.tables view for the existence of a table of the given name (t1) under the schema of the given name (dbo). We could alternatively check the information_schema.tables view, or other views/tables that the RDBMS uses for its metadata.

SQL `CREATE ... IF NOT EXISTS`

Many RDBMSs support the IF NOT EXISTS syntax when creating objects, such as tables. This saves us from having to write out more complex code like in the previous example. Here’s how we can replace the previous example using this syntax:

CREATE TABLE IF NOT EXISTS t1 (
        c1 int,
        c2 varchar(10)
        );

This works in many RDBMSs, but not in SQL Server or Oracle pre-23c at the time of writing (Oracle introduced the IF NOT EXISTS syntax as a new feature in Oracle Database 23c).

SQL `DROP ... IF EXISTS`

Similarly, we can use IF EXISTS when dropping an object from the database:

DROP TABLE IF EXISTS t1;

Again, this provides us with a more concise way of checking for the existence of the object before dropping it.

Example

A NOT EXISTS Example

The Purpose of the Subquery

SQL IF EXISTS

SQL IF NOT EXISTS

SQL CREATE ... IF NOT EXISTS

SQL DROP ... IF EXISTS

A `NOT EXISTS` Example

SQL `IF EXISTS`

SQL `IF NOT EXISTS`

SQL `CREATE ... IF NOT EXISTS`

SQL `DROP ... IF EXISTS`