SQL: Basic Queries and Filtering

Retrieving the precise data you need from a database is the first and most fundamental skill in working with SQL. Whether you're generating a report, feeding an application, or analyzing trends, everything begins with crafting an effective SELECT statement. The core mechanics of data retrieval involve selecting specific columns, filtering rows with precision, and ordering results logically.

The Foundation: The SELECT Statement

At its heart, a SELECT query instructs the database to retrieve data. The most basic form selects all columns from a table. For example, SELECT * FROM employees; returns every row and column from the employees table. However, best practice is to explicitly name the columns you need. This reduces the amount of data transferred and makes your query's intent clear. You simply list the column names after SELECT: SELECT first_name, last_name, department FROM employees;.

The database query engine processes your request in a specific logical order, though not necessarily in the physical sequence you write it. For a basic SELECT...FROM...WHERE query, it first identifies the table (FROM), then applies filters (WHERE), and finally returns the specified columns (SELECT). Understanding this conceptual flow helps you debug queries and anticipate their results. Selecting only the columns you need is also a primary form of optimization, as it minimizes memory and network overhead.

Filtering Rows with the WHERE Clause

The real power of retrieval lies in the WHERE clause, which acts as a filter for your rows. It allows you to specify conditions that each row must meet to be included in the result set. Conditions are built using comparison operators: = (equal), <> or != (not equal), > (greater than), < (less than), >= (greater than or equal to), and <= (less than or equal to). For instance, to find all employees in the 'Sales' department, you would write:

SELECT first_name, last_name
FROM employees
WHERE department = 'Sales';

To combine multiple conditions, you use logical operators. The AND operator requires all conditions to be true, while OR requires at least one to be true. You can control evaluation order with parentheses. For example, WHERE department = 'Sales' AND (salary > 50000 OR hire_date > '2023-01-01') finds Sales employees who either earn over 50,000 or were hired after the start of 2023.

Advanced Filtering: Ranges, Lists, and Patterns

SQL provides specialized operators to make common filtering tasks more concise. The BETWEEN operator is used to filter for a range of values, inclusive of the endpoints. It is more readable than using AND with two comparisons. For example, WHERE salary BETWEEN 45000 AND 65000 is equivalent to WHERE salary >= 45000 AND salary <= 65000.

When you need to check if a value matches any item in a specific list, the IN operator is ideal. Instead of writing WHERE department = 'Sales' OR department = 'Marketing' OR department = 'HR', you can write WHERE department IN ('Sales', 'Marketing', 'HR'). This is easier to read and maintain.

For matching text patterns, the LIKE operator is used with wildcard characters. The percent sign % represents zero, one, or multiple characters, while the underscore _ represents a single character. For example, WHERE last_name LIKE 'Sm%' finds names starting with "Sm" (Smith, Smyth), and WHERE phone LIKE '___-___-____' finds phone numbers formatted with three dashes. A critical concept is handling missing data. In SQL, a missing or inapplicable value is represented by NULL. You cannot use = NULL to check for it; you must use the IS NULL operator (or IS NOT NULL). This is because NULL represents an unknown state, so any comparison with it yields an unknown result.

Organizing Results with ORDER BY

By default, the order of rows returned by a query is not guaranteed. To impose a meaningful order, you use the ORDER BY clause. You specify one or more columns to sort by. The default sort order is ascending (ASC), but you can explicitly request descending order with DESC. For example, ORDER BY salary DESC, last_name ASC; will list employees from highest to lowest salary, and alphabetically by last name when salaries are tied. This clause is always processed last in the logical flow of a basic query, ensuring you see the final, filtered results in your chosen sequence.

Common Pitfalls

1. Misunderstanding NULL with Comparison Operators: Attempting to filter for NULL values with = NULL will never return any rows, as the equality comparison with NULL is not TRUE. Always use IS NULL or IS NOT NULL. For example, WHERE commission IS NULL is correct, while WHERE commission = NULL is not.

1. Incorrect Operator Precedence with AND/OR: Logical AND is evaluated before OR. The query WHERE department = 'Sales' OR department = 'Marketing' AND salary > 70000 might not do what you intend. It finds all Sales employees plus Marketing employees who earn over 70k. To find employees in either department who also earn over 70k, you must use parentheses: WHERE (department = 'Sales' OR department = 'Marketing') AND salary > 70000.

1. Overusing SELECT * in Production Code: While SELECT * is useful for exploratory queries, it is inefficient and fragile in application code or saved views. It retrieves all columns, including ones your application may not need, and will break if the table's column structure changes. Explicitly listing columns makes your code more robust, performant, and self-documenting.

1. Case-Sensitivity and Data Types in Comparisons: Depending on your database system and its configuration, string comparisons might be case-sensitive. WHERE department = 'sales' may not match 'Sales'. Also, ensure you match data types: comparing a string column to a number, like WHERE employee_id = '1001', may cause an implicit conversion error or performance issue.

Summary

• The SELECT statement retrieves data from tables. Explicitly naming columns is more efficient and clear than using SELECT *.
• The WHERE clause filters rows based on conditions built from comparison operators (=, >, <, etc.) and logical operators (AND, OR).
• Specialized operators like BETWEEN (for ranges), IN (for lists), and LIKE (for text patterns) provide concise ways to write common filters.
• Always use IS NULL or IS NOT NULL to check for missing values, never = NULL.
• Use the ORDER BY clause to sort your final result set in ascending (ASC) or descending (DESC) order.