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

SQL Operators

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.

Sample Table – Students

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');

students

roll_no	name	class	city
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

1. AND Operator

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.

students

roll_no	name	class	city
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 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.

Query Result

name	class	city
Aarav Sharma	10A	Delhi

2. OR Operator

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.

students

roll_no	name	class	city
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 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'.

Query Result

name	class
Aarav Sharma	10A
Rohit Menon	10A
Sneha Patil	8C
Aisha Khan	8C

3. NOT Operator

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.

students

roll_no	name	class	city
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 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.

Query Result

name	city
Diya Iyer	Chennai
Rohit Menon	Kochi
Sneha Patil	Pune
Aisha Khan	Hyderabad

4. IN Operator

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.

students

roll_no	name	class	city
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 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.

Query Result

name	city
Aarav Sharma	Delhi
Mehul Agarwal	Delhi
Rohit Menon	Kochi

5. BETWEEN Operator

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.

students

roll_no	name	class	city
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 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.

Query Result

roll_no	name
2	Diya Iyer
3	Rohit Menon
4	Sneha Patil
5	Mehul Agarwal

6. LIKE Operator

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.

students

roll_no	name	class	city
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 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'.

Query Result

name
Aarav Sharma
Aisha Khan

Combining Operators

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.

students

roll_no	name	class	city
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 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.

Query Result

name
Aarav Sharma
Mehul Agarwal

Best Practices

• 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.

Summary

SQL operators let you refine and sharpen your queries. Whether you’re checking for specific values, creating ranges, or matching patterns — AND, OR, NOT, IN, BETWEEN, and LIKE give you the precision to get just the data you need.