SQL Operators — AND, OR, NOT, IN, BETWEEN, LIKE

The school wants high-scoring students from either of two classes. A misplaced OR could admit students who do not meet the mark requirement.

SQL operators compare values and combine conditions. Parentheses, NULL behavior and pattern matching determine which rows actually qualify.

AND requires both conditions to be true; OR accepts either condition when it yields true; NOT reverses true and false but preserves unknown. IN expresses membership, BETWEEN includes both endpoints, and LIKE uses pattern wildcards. Parentheses clarify how alternatives are combined with additional requirements.

Related concepts: Filter with WHERE and Missing Values and NULL.

SQL becomes truly powerful when you start combining conditions. Want students from Delhi and Class 10A? Students not from Chennai? Names that start with “S”? You’ll need logical operators — the core building blocks of intelligent filtering.

SQL · portable concepts; preserved examples identify their engine

CREATE TABLE students (
  roll_no INT PRIMARY KEY,
  name VARCHAR(50),
  class VARCHAR(10),
  city VARCHAR(30)
);

INSERT INTO students VALUES
(1, 'Aarav Sharma', '10A', 'Delhi'),
(2, 'Diya Iyer', '9B', 'Chennai'),
(3, 'Rohit Menon', '10A', 'Kochi'),
(4, 'Sneha Patil', '8C', 'Pune'),
(5, 'Mehul Agarwal', '9B', 'Delhi'),
(6, 'Aisha Khan', '8C', 'Hyderabad');

SQL · portable concepts; preserved examples identify their engine

Returns rows where both conditions are true.

Use case: A school administrator wants to retrieve a list of students who are enrolled in class '10A' and reside in the city of Delhi to plan city-specific communication for that class.

SQL query description: SQL query to select the name, class, and city columns from the students table where the class is '10A' and the city is 'Delhi'.

SELECT name, class, city FROM students
WHERE class = '10A' AND city = 'Delhi';

Result description: Result contains the names, class, and city information of students who are in class '10A' and live in Delhi.

The surrounding query explains the specific highlighted values. Highlights annotate this example; they do not change the stored table.

SQL · portable concepts; preserved examples identify their engine

Returns rows where at least one condition is true.

Use case: A school administrator wants to generate a combined list of students from classes '10A' and '8C' for scheduling inter-class activities.

SQL query description: SQL query to select the name and class columns from the students table where the class is either '10A' or '8C'.

SELECT name, class FROM students
WHERE class = '10A' OR class = '8C';

Result description: Result contains the names and class details of all students who are enrolled in class '10A' or class '8C'.

The surrounding query explains the specific highlighted values. Highlights annotate this example; they do not change the stored table.

SQL · portable concepts; preserved examples identify their engine

Negates the condition — filters out matches.

Use case: A school administrator wants to retrieve a list of students who are not from the city of Delhi to analyze regional diversity or organize regional events for non-Delhi students.

SQL query description: SQL query to select the name and city columns from the students table where the city is not equal to 'Delhi'.

SELECT name, city FROM students
WHERE NOT city = 'Delhi';

Result description: Result contains the names and cities of students who are from cities other than Delhi.

The surrounding query explains the specific highlighted values. Highlights annotate this example; they do not change the stored table.

SQL · portable concepts; preserved examples identify their engine

Tests if a value is in a list of values — useful for multiple ORs.

Use case: A school administrator wants to generate a list of students who are from the cities of Delhi or Kochi for city-specific communication or regional event planning.

SQL query description: SQL query to select the name and city columns from the students table where the city is either 'Delhi' or 'Kochi'.

SELECT name, city FROM students
WHERE city IN ('Delhi', 'Kochi');

Result description: Result contains the names and city information of students who reside in either Delhi or Kochi.

The surrounding query explains the specific highlighted values. Highlights annotate this example; they do not change the stored table.

SQL · portable concepts; preserved examples identify their engine

Filters results within a range (inclusive).

Use case: A school administrator wants to retrieve student records for roll numbers 2 to 5 in order to verify or update their details.

SQL query description: SQL query to select the roll_no and name columns from the students table where the roll_no falls between 2 and 5, inclusive.

SELECT roll_no, name FROM students
WHERE roll_no BETWEEN 2 AND 5;

Result description: Result contains the roll numbers and names of students whose roll numbers are between 2 and 5.

The surrounding query explains the specific highlighted values. Highlights annotate this example; they do not change the stored table.

SQL · portable concepts; preserved examples identify their engine

Used for pattern matching with wildcards:

• %: matches any number of characters
• _: matches a single character

Names starting with 'A':

Use case: A school administrator wants to generate a list of all students whose names start with the letter 'A' for organizing name-based grouping or sorting.

SQL query description: SQL query to select the name column from the students table where the name begins with the letter 'A' using the LIKE operator and wildcard pattern 'A%'.

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

Result description: Result contains the names of all students whose names start with the letter 'A'.

The surrounding query explains the specific highlighted values. Highlights annotate this example; they do not change the stored table.

SQL · portable concepts; preserved examples identify their engine

Complex conditions often require mixing AND, OR, NOT:

Use case: A school administrator wants to get the names of students from class '10A' or '9B' who are located in the city of Delhi for sending location-specific academic notifications.

SQL query description: SQL query to select the name column from the students table where the class is either '10A' or '9B' and the city is 'Delhi'.

SELECT name FROM students
WHERE (class = '10A' OR class = '9B')
AND city = 'Delhi';

Result description: Result contains the names of students who belong to class '10A' or '9B' and are residing in Delhi.

The surrounding query explains the specific highlighted values. Highlights annotate this example; they do not change the stored table.

• Use parentheses to control logical flow when combining AND/OR.
• Use IN instead of multiple ORs for cleaner queries.
• LIKE is case-insensitive in most SQL databases unless configured otherwise.

AND normally binds more tightly than OR. A NULL in a NOT IN subquery can turn an expected exclusion test into unknown; NOT EXISTS is often a clearer alternative. LIKE case sensitivity and escape rules depend on engine and collation.

Keep students with marks from 80 through 90 inclusive, in either 10A or 9B.

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');

SQL · this practice variation runs with SQLite 3; the preserved examples retain their stated dialect

SELECT name, marks FROM practice_students WHERE class IN ('10A','9B') AND marks BETWEEN 80 AND 90 ORDER BY roll_no;

The membership test and mark range must both succeed. BETWEEN accepts marks equal to 80 or 90 as well as the values between them.

SQL operators compare values and combine conditions. Parentheses, NULL behavior and pattern matching determine which rows actually qualify.

The membership test and mark range must both succeed. BETWEEN accepts marks equal to 80 or 90 as well as the values between them.

Continue your understanding with Filter with WHERE and Missing Values and NULL.

Source for the engine-specific behavior: MySQL 8.4 reference documentation.