How to Check if a Record Exists in SQL

1. Overview

SQL is a widely used language to manage relational databases and also it is well known for storing and fetching large dataset sets efficiently. Additionally, it uses structured query language to manage the data in the database. Verifying the existence of the records in the database table is a crucial aspect of the system.

In this tutorial, we’ll look into different techniques to check the existence of a record in an SQL database table.

2. Using SELECT Statement

Before we move forward to check the record in the table. As an example, we will create a table program using the SQL statements contained in the Baeldung University schema. Now, to check if a record exists, we have to make a SELECT query targeting the relevant table and conditions. If the query returns any data (row) available in the table, it shows the existence of the desired record.

To illustrate, let’s take a look at the SELECT query:

SELECT * FROM Program WHERE department_id = 11;
+-----+------------------------------+---------------------------------------+------------+----------+-------+---------------+
| id  | name                         | description                           | start_date | end_date | type  | department_id |
+-----+------------------------------+---------------------------------------+------------+----------+-------+---------------+
| 131 | Theoretical Computer Science | Major in Theoretical Computer Science | 2010-01-01 | NULL     | Major |            11 |
+-----+------------------------------+---------------------------------------+------------+----------+-------+---------------+

After examining the above output, we can see the full row for the department_id 1 in the program table. Moreover, this result confirms the existence of the row associated with department_id 1.

Let’s take another example of a select query:

SELECT * FROM Program WHERE department_id = 16;
Empty set (0.01 sec)

In the above SELECT query, we executed a query for department_id 11, which isn’t available in our program table. In this case, the output returns nothing because it’s not in our table.

3. Using COUNT Function

The COUNT function in SQL is a tool to find the number of rows that match our conditions in the table. It only prints non-null values. If the count is greater than zero, our desired record is available in the table.

To demonstrate, let’s take a look at the COUNT function with the SELECT query:

SELECT COUNT(*) as count FROM Program WHERE department_id = 1;
|count|
|1|

Non-zero counts represent the availability of records in the database table.

4. Using EXISTS Operator

The EXISTS clause is one of the powerful and effective ways to check the existence of a record in a subquery. It is particularly effective as it stops processing when it finds the first matching record in the table, unlike count, which evaluates all the rows. This makes it ideal for scenarios where we need to validate the existence of a record:

if exists (SELECT 1 FROM Program WHERE department_id = 7)
    print 'Program found for department_id 7';
else
    print 'No program found for department_id 7';

The above query prints ‘Program found for department_id 7’ when found or ‘No program found for department_id 7′, otherwise.

5. Using TOP Clause

The TOP clause enables us to limit the result set to a specified number of rows. It is used to retrieve only the first few records. Notably, the TOP clause is specific to MSSQL server. Furthermore, by combining the TOP clause with a SELECT query, we can verify the existence of a record by examining the returned result set. If we get any rows returned in our output, then it means that the record exists; otherwise, it doesn’t.

Let’s take a look at the query:

SELECT TOP 1 * FROM Program WHERE department_id=11;
output: (131, 'Theoretical Computer Science', 'Major in Theoretical Computer Science', '2010-01-01', Null, 'Major', 1)

In the above query, TOP returned the department_id whose ID number equals to 11. If the result is not null, it indicates that the record exists in the table.

6. Conclusion

In this article, we explored different methods for checking the existence of a record in a SQL table.

SQL provides diverse techniques for conducting existence checks, including the SELECT statement, COUNT function, EXISTS operator, and TOP clause.