SQL SELECT and WHERE Clauses

Retrieving and filtering data are the most fundamental skills in working with databases. Whether you’re analyzing business trends, preparing datasets for machine learning, or generating reports, your journey begins with crafting precise SELECT and WHERE statements. These clauses form the bedrock of querying, allowing you to extract exactly the slice of information you need from potentially vast and complex tables.

The Foundation: The SELECT Statement

The SELECT statement is the command used to retrieve data from a database. Its most basic form specifies which columns you want to see. You write SELECT followed by a comma-separated list of column names. To retrieve every column without listing them, you use the asterisk (*) wildcard.

Consider a table named employees with columns: employee_id, first_name, last_name, department, salary, and hire_date.

-- Select specific columns
SELECT first_name, last_name, department
FROM employees;

-- Select all columns
SELECT *
FROM employees;

While SELECT * is convenient for exploration, in production data science workflows, explicitly listing columns is better practice. It makes your code clearer, more efficient by transferring only necessary data, and more resilient to future changes in the table's structure.

Introducing Filtering with the WHERE Clause

The WHERE clause is used to filter records and return only those that satisfy a specified condition. It follows the FROM clause in a statement. Without WHERE, a SELECT query returns every row from the table, which is rarely useful for analysis.

The core of the WHERE clause is a condition that evaluates to true, false, or unknown for each row. Only rows where the condition is true are 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).

-- Find all employees in the 'Sales' department
SELECT first_name, last_name, department
FROM employees
WHERE department = 'Sales';

-- Find employees with a salary greater than 75000
SELECT *
FROM employees
WHERE salary > 75000;

In data science, filtering is essential for creating focused datasets for analysis, such as selecting transactions from a specific time period or customers from a particular region.

Combining Conditions with AND, OR, and NOT

Real-world filtering often requires multiple criteria. You combine conditions using the logical operators AND, OR, and NOT.

• AND: All conditions must be true.
• OR: At least one condition must be true.
• NOT: Negates a condition, selecting rows where the condition is false.

Parentheses are crucial for controlling the order of evaluation, as AND is processed before OR.

-- Employees in Sales with a salary over 70000 (AND)
SELECT first_name, last_name, department, salary
FROM employees
WHERE department = 'Sales' AND salary > 70000;

-- Employees in either Sales OR Marketing (OR)
SELECT first_name, last_name, department
FROM employees
WHERE department = 'Sales' OR department = 'Marketing';

-- Employees in Sales who are NOT in the 'West' region
SELECT first_name, last_name, department, region
FROM employees
WHERE department = 'Sales' AND NOT region = 'West';

-- Complex logic: (In Sales AND salary > 70000) OR (In Marketing AND salary > 60000)
SELECT first_name, last_name, department, salary
FROM employees
WHERE (department = 'Sales' AND salary > 70000)
   OR (department = 'Marketing' AND salary > 60000);

Mastering these logical operators allows you to perform sophisticated segmentations of your data.

Advanced Filtering: IN, BETWEEN, LIKE, and IS NULL

Beyond simple comparisons, SQL provides specialized operators for common filtering patterns.

The IN operator allows you to specify multiple possible values for a column, checking for set membership. It is a cleaner and often more efficient alternative to multiple OR conditions.

-- Employees in Sales, Marketing, or Engineering
SELECT first_name, last_name, department
FROM employees
WHERE department IN ('Sales', 'Marketing', 'Engineering');

The BETWEEN operator selects values within a given inclusive range. It is used with numbers, dates, and text.

-- Employees with a salary between 50000 and 80000
SELECT first_name, last_name, salary
FROM employees
WHERE salary BETWEEN 50000 AND 80000;

-- Employees hired in the first half of 2023
SELECT first_name, last_name, hire_date
FROM employees
WHERE hire_date BETWEEN '2023-01-01' AND '2023-06-30';

The LIKE operator is used for pattern matching on text strings. It employs two wildcards:

• % : Matches any sequence of zero or more characters.
• _ : Matches any single character.

-- Employees whose last name starts with 'Sm'
SELECT first_name, last_name
FROM employees
WHERE last_name LIKE 'Sm%';

-- Employees whose first name is 5 letters long and ends with 'n'
SELECT first_name
FROM employees
WHERE first_name LIKE '____n';

In data science, LIKE is invaluable for cleaning messy text data or finding records with common patterns.

The IS NULL operator is used to test for missing or unknown values (NULL). A common mistake is using = NULL, which will never evaluate to true because NULL represents an unknown value. You must use IS NULL or IS NOT NULL.

-- Find employees where the department information is missing
SELECT first_name, last_name
FROM employees
WHERE department IS NULL;

-- Find employees with a recorded department
SELECT first_name, last_name, department
FROM employees
WHERE department IS NOT NULL;

Handling NULL values correctly is critical, as they can skew calculations (like AVG or COUNT) and must be addressed before analysis.

Common Pitfalls

1. Misunderstanding NULL with Comparison Operators: Using WHERE column = NULL or WHERE column <> NULL will not work. NULL is not a value but a marker for the absence of a value. Always use IS NULL or IS NOT NULL.

1. Ignoring Operator Precedence with AND/OR: Forgetting parentheses in complex WHERE clauses leads to logical errors. SQL evaluates AND before OR. The query WHERE department = 'Sales' AND salary > 70000 OR department = 'Marketing' is interpreted as (department = 'Sales' AND salary > 70000) OR department = 'Marketing', which is likely not the intended logic. Always use parentheses to make your logic explicit.

1. Case Sensitivity in String Comparisons: In many database systems (e.g., PostgreSQL, MySQL with certain collations), string comparisons are case-sensitive. WHERE department = 'sales' will not match 'Sales'. Use functions like UPPER() or LOWER() for case-insensitive checks: WHERE UPPER(department) = 'SALES'.

1. Incorrect Use of BETWEEN with Dates: Remember BETWEEN is inclusive. BETWEEN '2023-01-01' AND '2023-01-31' includes all moments on January 31st. If your hire_date includes timestamps, this might include unwanted records from the very end of the day. For date-only ranges, ensure your data type is appropriate or cast accordingly.

Summary

• The SELECT statement specifies the columns you want to retrieve from a table, forming the basis of all data queries.
• The WHERE clause filters rows based on one or more conditions, using comparison operators (=, >, <, etc.) to define the criteria.
• Logical operators AND, OR, and NOT allow you to build complex filtering logic, with parentheses essential for controlling evaluation order.
• Specialized operators streamline common tasks: IN for set membership, BETWEEN for inclusive ranges, LIKE with % and _ wildcards for text patterns, and IS NULL for identifying missing data.
• Avoiding common mistakes—such as improper NULL handling, overlooking operator precedence, and case sensitivity in strings—is key to writing accurate and reliable SQL for data science.