How to Query for Specific Values in an Integer Column in SQL

1. Overview

In SQL, we often encounter situations where we need to filter data based on specific values within columns. To filter for specific values in an integer column, we can use basic queries, whereas other times we may want more flexibility. For instance, we may need a dynamic query that filters for a specific value but allows any value in that column at other times.

In this tutorial, we’ll use basic and dynamic queries when querying for specific values in an integer column. To illustrate, we’ll utilize the database structure of Baeldung University.

2. Basic Querying for Specific Values

Here, we use queries that work across MySQL, PostgreSQL, and SQL Server.

So, we can filter results based on a specific integer value using the WHERE clause. For example, we can query for a student whose national_id is 123455:

SELECT * FROM Student WHERE national_id = 123455;

This query returns a student whose national_id is 123455.

Now, let’s use this clause to query for NULL values, non-NULL values, and multiple values. This makes it easy to filter specific values in an integer column.

2.1. Querying for NULL or Non-NULL Values

At times, we might encounter columns where some values are NULL. We can query for NULL values using the IS NULL operator:

SELECT * FROM Student WHERE national_id IS NULL;

This query retrieves a student whose national_id is not in the records.

Additionally, we can query for non-NULL values with the help of the IS NOT NULL operator:

SELECT * FROM Student WHERE national_id IS NOT NULL;

This query retrieves a student whose national_id is in the records.

2.2. Querying for Multiple Values

In SQL, we can also query for more than one specific value such as a range of values. To achieve this, we can use the BETWEEN operator or comparison operators.

The BETWEEN operator enables us to find rows whose integer falls within the specified range:

SELECT * FROM Student WHERE national_id BETWEEN 123455 AND 123457;

This query retrieves students whose national_id is between 123455 and 123457. The range values (123455 and 123457) are also included in the query.

Alternatively, we can query for ranges with the help of comparison operators:

SELECT * FROM Student WHERE national_id >= 123455 AND national_id <= 123457;

The query above utilizes >= and <= operators to achieve a similar result as the BETWEEN example.

Similarly, we can utilize the IN operator to filter for multiple values at once:

SELECT * FROM Student WHERE national_id IN (12345, 123455, 123456, 1234567);

For instance, the example above filters for students with a national_id of either 12345, 123455, 123456, or 1234567.

3. Dynamic Querying for Specific Values

In the previous section, we queried for rows with a specific value in the national_id integer column. However, we may also want to query for all the rows if no specific value is specified. This approach enables us to create a single query that changes depending on whether a filter value exists.

3.1. In MySQL

To demonstrate, we’ll define the variable @national_id_filter to store the filter value:

SET @national_id_filter = 4;

Now, let’s define a query that dynamically filters based on the value of @national_id_filter.

First, let’s use the COALESCE function for dynamic filtering:

SELECT * FROM Student WHERE national_id = COALESCE(@national_id_filter, national_id);

The COALESCE function returns the first non-NULL value in the list of arguments. If @national_id_filter is provided, the function returns @national_id_filter, which creates the condition national_id = @national_id_filter. Thus, the query returns rows where national_id matches @national_id_filter.

Meanwhile, if @national_id_filter is NULL, the function returns national_id which creates the condition national_id=national_id. Therefore, the query returns all other rows except the ones whose national_id is NULL. This happens because the condition national_id = national_id is not true for rows where national_id is NULL.

This query can return all rows in a scenario where the integer column, in this case, national_id, contains no NULL value. However, we can modify the query to ensure that rows with NULL values in the national_id column are also returned when @national_id_filter is NULL:

SELECT * FROM Student WHERE (@national_id_filter IS NULL OR national_id = @national_id_filter);

Here’s the breakdown:

• @national_id_filter IS NULL – this part helps to retrieve all rows if @national_id_filter is NULL
• national_id = @national_id_filter – this part helps to retrieve rows where national_id matches the specific value provided by @national_id_filter

Above, the query returns only rows whose national_id value is 4 since @national_id_filter is 4. However, if @national_id_filter is NULL, the query returns all rows.

3.2. In PostgreSQL

In PostgreSQL, we can still apply the COALESCE function for dynamic filtering:

SELECT * FROM Student WHERE national_id = COALESCE(4, national_id);

Both PostgreSQL and MySQL can use this query in the same way.

Additionally, we can use a conditional CASE expression:

SELECT * FROM Student 
WHERE national_id = CASE
    WHEN 4 IS NOT NULL THEN 4  -- Replace 4 with the desired filter value
    ELSE national_id
END;

Above, we use the CASE statement to control the logic for filtering. So, WHEN 4 IS NOT NULL THEN 4 checks if the filter value, in this case, 4, is not NULL. As a result, the query returns rows where national_id is 4. Now, let’s see what happens when we replace the filter value 4 with NULL:

SELECT * FROM Student 
WHERE national_id = CASE
    WHEN NULL IS NOT NULL THEN NULL  -- Replace NULL with the desired filter value
    ELSE national_id
END;

The condition national_id=national_id takes effect if we substitute NULL for 4. As a result, the query returns all other rows except the ones whose national_id is NULL. To clarify, the condition national_id = national_id is not true for rows where national_id is NULL.

3.3. In SQL Server

SQL Server also supports the use of the COALESCE function:

SELECT * FROM Student WHERE national_id = COALESCE(@national_id_filter, national_id);

This query operates similarly to the one found in MySQL and PostgreSQL.

Alternatively, we can use the ISNULL function:

SELECT * FROM Student WHERE national_id = ISNULL(@national_id_filter, national_id);

This ISNULL function displays the same behavior and output as COALESCE. To clarify, if @national_id_filter is NULL, the query doesn’t return rows where national_id is NULL because NULL = NULL evaluates to UNKNOWN in SQL.

4. Conclusion

In this article, we discussed various ways to utilize the WHERE clause to query for specific values in an integer column in SQL. In the case of basic queries, we queried for NULL, non-NULL, and multiple values. Meanwhile, in the case of dynamic queries, we discussed filtering for specific values or all other values based on the filter value.

In SQL, we can now perform both basic and dynamic filtering by understanding these queries. As a result, maintaining and obtaining data from SQL data sources is more flexible.