SQLite: Comparison Operators

This SQLite post explores all of the comparison operators used to test for equality and inequality, as well as the more advanced operators.

Description

Comparison operators are used in the WHERE clause to determine which records to select. Here is a list of the comparison operators that you can use in SQLite:

Some of these operators are fairly straight forward and others are more complicated. Let's begin by reviewing the easier comparison operators in SQLite.

Example - Equality Operator

In SQLite, you can use the = operator to test for equality in a query.

For example:

SELECT *
FROM employees
WHERE favorite_website = 'AODBA.com';

In this example, the SELECT statement above would return all rows from the employees table where the favorite_website is equal to 'AODBA.com'.

Or you could also write this query using the == operator, as follows:

SELECT *
FROM employees
WHERE favorite_website == 'AODBA.com';

Both of these queries would return the same results.

Example - Inequality Operator

In SQLite, you can use the > or != operators to test for inequality in a query.

For example, we could test for inequality using the > operator, as follows:

SELECT *
FROM employees
WHERE first_name > 'Joanne';

In this example, the SELECT statement would return all rows from the employees table where the first_name is not equal to Joanne.

Or you could also write this query using the != operator, as follows:

SELECT *
FROM employees
WHERE first_name != 'Joanne';

Both of these queries would return the same results.

Example - Greater Than Operator

You can use the > operator in SQLite to test for an expression greater than.

SELECT *
FROM employees
WHERE employee_id > 25;

In this example, the SELECT statement would return all rows from the employees table where the employee_id is greater than 25. An employee_id equal to 25 would not be included in the result set.

Example - Greater Than or Equal Operator

In SQLite, you can use the >= operator to test for an expression greater than or equal to.

SELECT *
FROM employees
WHERE employee_id >= 25;

In this example, the SELECT statement would return all rows from the employees table where the employee_id is greater than or equal to 25. In this case, n employee_id equal to 25 would be included in the result set.

Example - Less Than Operator

You can use the operator in SQLite to test for an expression less than.

SELECT *
FROM customers
WHERE customer_id  300;

In this example, the SELECT statement would return all rows from the customers table where the customer_id is less than 300. A customer_id equal to 300 would not be included in the result set.

Example - Less Than or Equal Operator

In SQLite, you can use the = operator to test for an expression less than or equal to.

SELECT *
FROM customers
WHERE customer_id = 300;

In this example, the SELECT statement would return all rows from the customers table where the customer_id is less than or equal to 300. In this case, customer_id equal to 300 would be included in the result set.

Example - Advanced Operators

For the more advanced comparison operators in SQLite, we've written specific tutorials to discuss each one individually. These topics will be covered later, or you can jump to one of these tutorials now.

• IN ( )
• NOT
• BETWEEN
• IS NULL
• IS NOT NULL
• LIKE
• EXISTS