SQLite - WHERE Clause

SQLite - The WHERE Clause

The SQLite WHERE clause is the primary tool for filtering records. It is used to extract only those records that fulfill a specified condition, allowing you to selectively retrieve, update, or delete data.

If the condition specified in the WHERE clause evaluates to true for a given row, that row is included in the operation. This clause is not exclusive to SELECT statements; it is a crucial part of UPDATE and DELETE statements as well, ensuring that modifications are precise and intentional.

Syntax

The basic syntax of a SELECT statement with a WHERE clause is as follows:

SELECT column_list
FROM table_name
WHERE [condition];

The [condition] is an expression that typically involves comparison operators (=, >, <, >=, <=, <>), logical operators (AND, OR, NOT), and other specialized operators like LIKE, IN, BETWEEN, and IS NULL.

Setup: Creating Sample Data

To demonstrate the WHERE clause, let’s create and populate an EMPLOYEES table. This table will serve as the basis for our examples.

CREATE TABLE EMPLOYEES(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   DEPARTMENT     CHAR(50),
   SALARY         REAL
);

INSERT INTO EMPLOYEES (ID,NAME,AGE,DEPARTMENT,SALARY) VALUES (1, 'David', 27, 'IT', 85000.00 );
INSERT INTO EMPLOYEES (ID,NAME,AGE,DEPARTMENT,SALARY) VALUES (2, 'Allen', 25, 'HR', 15000.00 );
INSERT INTO EMPLOYEES (ID,NAME,AGE,DEPARTMENT,SALARY) VALUES (3, 'Martin', 23, 'Finance', 20000.00 );
INSERT INTO EMPLOYEES (ID,NAME,AGE,DEPARTMENT,SALARY) VALUES (4, 'Mark', 25, 'IT', 65000.00 );
INSERT INTO EMPLOYEES (ID,NAME,AGE,DEPARTMENT,SALARY) VALUES (5, 'Paul', 32, 'Finance', 21000.00 );
INSERT INTO EMPLOYEES (ID,NAME,AGE,DEPARTMENT,SALARY) VALUES (6, 'Kim', 22, 'HR', 45000.00 );
INSERT INTO EMPLOYEES (ID,NAME,AGE,DEPARTMENT,SALARY) VALUES (7, 'James', 24, 'Marketing', 10000.00 );
INSERT INTO EMPLOYEES (ID,NAME,AGE,DEPARTMENT,SALARY) VALUES (8, 'Chris', 45, 'IT', NULL);

Examples

Let’s explore various filtering conditions using the WHERE clause.

1. Find all employees in the ‘IT’ department:

sqlite> SELECT * FROM EMPLOYEES WHERE DEPARTMENT = 'IT';

ID  NAME   AGE  DEPARTMENT  SALARY
--  -----  ---  ----------  -------
1   David  27   IT          85000.0
4   Mark   25   IT          65000.0
8   Chris  45   IT

2. Find all employees older than 30 OR who earn more than 50,000:

sqlite> SELECT * FROM EMPLOYEES WHERE AGE > 30 OR SALARY > 50000;

ID  NAME   AGE  DEPARTMENT  SALARY
--  -----  ---  ----------  -------
1   David  27   IT          85000.0
4   Mark   25   IT          65000.0
5   Paul   32   Finance     21000.0
8   Chris  45   IT

3. Find all employees whose name starts with ‘M’: The LIKE operator is used for pattern matching in strings. The percent sign (%) is a wildcard that matches any sequence of characters.

sqlite> SELECT * FROM EMPLOYEES WHERE NAME LIKE 'M%';

ID  NAME    AGE  DEPARTMENT  SALARY
--  ------  ---  ----------  -------
3   Martin  23   Finance     20000.0
4   Mark    25   IT          65000.0

4. Find all employees who work in the ‘HR’ or ‘Finance’ departments: The IN operator provides a concise way to check if a value matches any value in a list.

sqlite> SELECT * FROM EMPLOYEES WHERE DEPARTMENT IN ('HR', 'Finance');

ID  NAME    AGE  DEPARTMENT  SALARY
--  ------  ---  ----------  -------
2   Allen   25   HR          15000.0
3   Martin  23   Finance     20000.0
5   Paul    32   Finance     21000.0
6   Kim     22   HR          45000.0

5. Find all employees whose salary is between 20,000 and 50,000: The BETWEEN operator is inclusive of the start and end values.

sqlite> SELECT * FROM EMPLOYEES WHERE SALARY BETWEEN 20000 AND 50000;

ID  NAME    AGE  DEPARTMENT  SALARY
--  ------  ---  ----------  -------
3   Martin  23   Finance     20000.0
5   Paul    32   Finance     21000.0
6   Kim     22   HR          45000.0

6. Find all employees whose salary has not been recorded: The IS NULL operator is used to check for NULL values. You cannot use = NULL.

sqlite> SELECT * FROM EMPLOYEES WHERE SALARY IS NULL;

ID  NAME   AGE  DEPARTMENT  SALARY
--  -----  ---  ----------  -------
8   Chris  45   IT

Common Pitfalls and Best Practices

1. NULL Handling: A common mistake is using column = NULL or column <> NULL. NULL represents an unknown value, so it cannot be compared with standard operators. Always use IS NULL or IS NOT NULL.

2. Operator Precedence: When combining AND and OR, be aware that AND has higher precedence. To ensure your logic is correct, use parentheses to group conditions explicitly. For example: WHERE (DEPARTMENT = 'IT' OR DEPARTMENT = 'HR') AND SALARY > 50000.

3. Performance: For large tables, queries can be slow. Columns that are frequently used in WHERE clauses are excellent candidates for database indexes, which can dramatically speed up data retrieval. We will cover indexes in a later chapter.

4. Data Types: Ensure that the data you compare matches the column’s data type. Comparing a string to a numeric column (e.g., WHERE AGE = '25') might work in SQLite due to its flexible typing, but it’s bad practice and can lead to unexpected results or poor performance.