Filtering Data in SQL: A Beginner's Guide to WHERE, IN, BETWEEN, NULL, and LIKE
Hi everyone! I am learning SQL, and this week we studied operators. Operators are the words and symbols we write after WHERE to tell SQL which rows we want. I practised on a practice hospital table called patients in PostgreSQL. It has columns like county , department , age , bill_amount , insurance and `email. In this post, I explain what I learned, using queries I wrote myself. 1. Comparison…
Welcome to SQL, where operators tell the database which rows you want to see. Querying a patients table in PostgreSQL, you'll learn comparison operators such as = , != (or !=), <, >, <=, and >=. For instance, finding patients with a bill over 100,000 is simple: SELECT first_name, last_name, department, bill_amount FROM patients WHERE bill_amount > 100000;
Text values need single quotes, while numbers require no quotes. For dates, follow the YYYY-MM-DD format. Next, logical operators AND, OR, and NOT help you combine conditions. AND requires both conditions to be true, while OR just needs one to be. Be mindful that AND takes precedence over OR, so use parentheses when needed.
BETWEEN selects values within a range, including the endpoints, while IN checks against a list of values, often more concise than multiple OR conditions. NULL isn't zero or an empty space; it means a missing or unknown value. Use IS NULL to find these rows. Finally, LIKE allows you to search for patterns using % (any characters) and _ (exactly one character). Remember, LIKE is case-sensitive in PostgreSQL, so use ILIKE for case-insensitive searches.
Common mistakes to avoid include using = NULL (which never returns anything), forgetting LIKE's case sensitivity, and neglecting to use parentheses with AND and OR. Keep an eye on your results and double-check your queries to master these operators. Happy querying!
Written by urgent.news from Dev.to's reporting — not their text. Machine-written — may contain errors; check the original before relying on it.
