What is the difference between AND and OR?

AND requires both conditions to be true. OR requires at least one condition to be true. You can combine them, but use parentheses to clarify precedence. For example: WHERE age > 18 AND (name = "Alice" OR name = "Bob")

How does LIKE pattern matching work?

LIKE is used for pattern matching in WHERE clauses. The % wildcard matches zero or more characters, and _ matches exactly one character. For example: WHERE name LIKE "A%" matches names starting with A. WHERE name LIKE "_o%" matches names where the second character is o.

Filtering & Sorting

Narrow results and sort them with precision.

SELECT returns all rows by default. In practice, you almost always want to filter and sort. This stage covers the tools to do that.

Definition

The WHERE clause filters rows in a SQL query. Only rows that match the condition are returned. Common operators include =, <, >, BETWEEN, LIKE, IN, and IS NULL.

Required step: If you do not have the school database and students table yet, run this setup in the playground:

CREATE DATABASE IF NOT EXISTS school;
USE school;

CREATE TABLE IF NOT EXISTS students (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(100)
);

INSERT INTO students (name, email)
VALUES
  ('Alice', 'alice@example.com'),
  ('Bob', 'bob@example.com'),
  ('Charlie', 'charlie@example.com');

Troubleshooting: If you get "table doesn't exist" errors, re-run the setup above. It uses IF NOT EXISTS so it is safe to run multiple times.

Combining Conditions

Use AND to require multiple conditions. Use OR to accept either condition.

AND: both conditions must be true

SELECT * FROM students
WHERE name LIKE 'A%'
  AND email IS NOT NULL;

Both conditions must match. Only rows where name starts with A AND email is not null are returned.

OR: either condition can be true

SELECT * FROM students
WHERE name = 'Alice'
  OR name = 'Bob';

Either condition matches. Rows where name is Alice or Bob are both returned.

Parentheses control order

SELECT * FROM students
WHERE (name = 'Alice' OR name = 'Bob')
  AND email IS NOT NULL;

Parentheses group conditions. Without them, AND binds tighter than OR, so name = 'Alice' OR (name = 'Bob' AND email IS NOT NULL) would return Alice regardless of email. Parentheses make the intent clear.

IS NULL and IS NOT NULL

NULL means "absence of data". You cannot use = or != to compare with NULL. Use IS NULL or IS NOT NULL instead.

Find rows with missing data

SELECT * FROM students
WHERE email IS NULL;

Returns only rows where email is NULL. = NULL does not work in SQL.

Find rows with data present

SELECT * FROM students
WHERE email IS NOT NULL;

IS NOT NULL is the opposite. Returns rows where email has a value.

LIKE Patterns

LIKE matches text patterns. % matches any sequence of characters. _ matches exactly one character.

Starts with

SELECT * FROM students
WHERE name LIKE 'A%';

'A%' means: starts with A, followed by anything. Matches Alice, Andrew, etc.

Contains

SELECT * FROM students
WHERE name LIKE '%li%';

'%li%' means: anything, then li, then anything. Matches Alice, Olivia, etc.

Exact length pattern

SELECT * FROM students
WHERE name LIKE '___';

'___' (three underscores) matches exactly 3 characters. Matches Bob, Sam, etc.

BETWEEN and IN

BETWEEN matches a range of values. IN matches any value in a list.

Range with BETWEEN

SELECT * FROM students
WHERE id BETWEEN 1 AND 3;

BETWEEN includes both endpoints (the boundary values). This returns rows where id is 1, 2, or 3. Works with numbers, dates, and text.

List matching with IN

SELECT * FROM students
WHERE name IN ('Alice', 'Bob', 'Charlie');

IN matches any value in the parentheses. This is shorter than writing multiple OR conditions.

NOT IN to exclude

SELECT * FROM students
WHERE name NOT IN ('Alice', 'Bob');

NOT IN excludes the listed values. Returns all rows except Alice and Bob.

ORDER BY

ORDER BY sorts the result set (the rows returned by your query). ASC is the default (ascending), DESC reverses the order (descending). You can refer to columns by name, alias, or position number.

Sort alphabetically

SELECT * FROM students
ORDER BY name ASC;

ASC is the default, so ORDER BY name works the same. Names appear A to Z.

Sort descending

SELECT * FROM students
ORDER BY id DESC;

DESC reverses the order. The newest rows (highest id) appear first.

Sort by multiple columns

SELECT * FROM students
ORDER BY name ASC, id DESC;

If two rows have the same name, the one with the higher id comes first.

LIMIT and DISTINCT

LIMIT takes one or two arguments. With one argument, it returns that many rows. With two arguments (LIMIT offset, count), the offset starts at 0. You can also use LIMIT count OFFSET offset syntax. DISTINCT specifies removal of duplicate rows from the result set.

Top N results

SELECT * FROM students
ORDER BY id DESC
LIMIT 3;

LIMIT 3 returns only the first 3 rows after sorting. Combined with ORDER BY DESC, you get the 3 most recent.

Remove duplicates

SELECT DISTINCT name FROM students;

DISTINCT removes duplicate values from the result. If two students had the same name, it would appear only once.

What does % mean in a LIKE pattern?

What does BETWEEN 1 AND 5 include?

Key Takeaways

AND requires both conditions. OR requires either.
Use IS NULL and IS NOT NULL to check for missing values.
LIKE uses % for any characters and _ for one character.
BETWEEN matches a range. IN matches a list of values.
ORDER BY sorts. ASC is default, DESC reverses.
LIMIT caps the number of returned rows.
DISTINCT removes duplicate values.

Ready to test your knowledge?

Take a Quiz