SQL Operators – Comparison, Logical, BETWEEN, IN and LIKE
Quick Answer: SQL operators help us filter records and compare values. Comparison, logical, BETWEEN, IN and LIKE operators make SQL queries useful for real business requirements.
1. Why Do We Need SQL Operators?
Imagine Arjun manages a cinema ticket counter with 5,000 bookings. Customers ask for premium seats, affordable tickets and specific movie bookings.
Every booking contains a customer name, city, movie and ticket price. Arjun needs to find matching bookings without checking every record manually.
How can he retrieve only the bookings that customers need?
The problem without SQL operators
Arjun could retrieve every booking and check each record manually. This works for a few bookings, but becomes difficult with thousands.
Example 1: Retrieve all cinema bookings
This script creates sample bookings and retrieves every record.
CREATE TABLE #MovieTickets
(
TicketId INT,
CustomerName VARCHAR(50),
City VARCHAR(50),
SeatType VARCHAR(30),
TicketPrice INT
);
INSERT INTO #MovieTickets
VALUES
(101, 'Arjun', 'Bengaluru', 'Premium', 350),
(102, 'Ananya', 'Mysuru', 'Premium', 450),
(103, 'Ravi', 'Bengaluru', 'Regular', 250),
(104, 'Asha', 'Hyderabad', 'Premium', 550),
(105, 'Aman', 'Mysuru', 'Regular', 300);
SELECT *
FROM #MovieTickets;
Expected output
TicketId CustomerName City SeatType TicketPrice 101 Arjun Bengaluru Premium 350 102 Ananya Mysuru Premium 450 103 Ravi Bengaluru Regular 250 104 Asha Hyderabad Premium 550 105 Aman Mysuru Regular 300
All five bookings are returned. Arjun must now inspect each record.
The design has some problems:
- We retrieve records that may not match the customer's request.
- Manual filtering becomes difficult with thousands of bookings.
- Combining multiple business conditions takes additional effort.
- Finding records by partial names becomes inconvenient.
Now imagine Arjun handling thousands of bookings every day. He needs accurate filtering directly inside SQL Server.
The solution: SQL Operators
SQL Operator = a symbol or keyword used to perform an operation.
Comparison Operator = checks whether two values satisfy a condition.
Logical Operator = combines or reverses multiple conditions.
BETWEEN = checks whether a value falls within a range.
IN = checks whether a value matches any listed value.
LIKE = searches text using a specified pattern.
We can combine these operators to retrieve exactly the bookings Arjun needs.
Animation 1: How SQL operators filter cinema bookings
Follow Arjun's booking records through comparison and filtering stages.
Arjun filters five bookings using price, city, name and seat conditions. Two records match every condition.
2. Implement SQL Operators in SQL Server
Let's understand each operator using Arjun's cinema bookings.
Comparison Operators
Comparison operators = symbols that compare two values.
We use these operators inside the WHERE clause to filter records.
| Operator | Meaning | Example |
|---|---|---|
= |
Equal to | TicketPrice = 350 |
<> |
Not equal to | SeatType <> 'Regular' |
> |
Greater than | TicketPrice > 300 |
< |
Less than | TicketPrice < 300 |
>= |
Greater than or equal to | TicketPrice >= 350 |
<= |
Less than or equal to | TicketPrice <= 350 |
For example, Arjun wants tickets costing more than 300.
Example: Find tickets costing more than 300
Assumes the #MovieTickets table created in Example 1.
SELECT CustomerName, TicketPrice
FROM #MovieTickets
WHERE TicketPrice > 300;
The > operator checks each ticket price. SQL Server returns only matching records.
Expected output
CustomerName TicketPrice Arjun 350 Ananya 450 Asha 550
Logical Operators
Logical Operators = keywords that combine or reverse conditions.
| Operator | Meaning |
|---|---|
AND |
Every condition must be true. |
OR |
At least one condition must be true. |
NOT |
Reverses a condition. |
AND example: Find premium tickets costing at least 350.
SELECT *
FROM #MovieTickets
WHERE SeatType = 'Premium'
AND TicketPrice >= 350;
Both conditions must be true. This returns Arjun, Ananya and Asha.
OR example: Find bookings from Bengaluru or Hyderabad.
SELECT *
FROM #MovieTickets
WHERE City = 'Bengaluru'
OR City = 'Hyderabad';
Either condition can be true. This returns Arjun, Ravi and Asha.
NOT example: Exclude Regular seat bookings.
SELECT *
FROM #MovieTickets
WHERE NOT (SeatType = 'Regular');
NOT reverses the condition. Only Premium seat bookings are returned.
BETWEEN Operator
BETWEEN = checks whether a value falls within two boundary values.
Arjun wants tickets priced between 300 and 500. We can use one simple condition.
Example: Find tickets within a price range
SELECT CustomerName, TicketPrice
FROM #MovieTickets
WHERE TicketPrice BETWEEN 300 AND 500;
Both boundary values are included. This returns tickets priced at 300, 350 and 450.
Expected output
CustomerName TicketPrice Arjun 350 Ananya 450 Aman 300
Important: BETWEEN includes both starting and ending values. For date ranges, remember that SQL Server compares the complete date and time values.
IN Operator
IN = checks whether a value matches any item in a list.
Arjun wants bookings from Bengaluru and Mysuru. We can avoid writing multiple OR conditions.
Example: Find bookings from selected cities
SELECT *
FROM #MovieTickets
WHERE City IN
(
'Bengaluru',
'Mysuru'
);
The IN operator checks both city values. It returns four matching bookings.
We can also use NOT IN to exclude selected values.
LIKE Operator
LIKE = searches text using a specified pattern.
Arjun wants to find customers whose names start with the letter A.
SQL Server supports two common wildcard characters:
| Wildcard | Meaning | Example |
|---|---|---|
% |
Matches zero or more characters. | 'A%' |
_ |
Matches exactly one character. | 'A_' |
Example: Find customer names starting with A
SELECT *
FROM #MovieTickets
WHERE CustomerName LIKE 'A%';
The percentage symbol allows any characters after A. This returns Arjun, Ananya, Asha and Aman.
Other useful patterns include:
'%an%'– contains the characters an.'%a'– ends with the letter a.'A_'– starts with A and has exactly two characters.'_r%'– has r as its second character.
Combine Multiple SQL Operators
Now Arjun wants affordable Premium bookings from selected cities. Customer names must also start with A.
Example: Combine comparison, logical, BETWEEN, IN and LIKE
This query uses all five operator categories together.
SELECT TicketId, CustomerName, City, TicketPrice
FROM #MovieTickets
WHERE TicketPrice > 0
AND TicketPrice BETWEEN 300 AND 500
AND City IN ('Bengaluru', 'Mysuru')
AND CustomerName LIKE 'A%'
AND NOT (SeatType = 'Regular');
Notice how every condition works together. SQL Server returns only records satisfying all conditions.
Expected output
TicketId CustomerName City TicketPrice 101 Arjun Bengaluru 350 102 Ananya Mysuru 450
Design tip: Use parentheses when combining complex AND and OR conditions. This makes your query easier to read and helps avoid unexpected results.
3. Quick Summary
Comparison and logical operators vs range and pattern operators
| Operator | Purpose | Example |
|---|---|---|
| Comparison | Compare values | Price > 300 |
| AND / OR / NOT | Combine conditions | City = 'Mysuru' AND Price > 300 |
| BETWEEN | Check a range | Price BETWEEN 300 AND 500 |
| IN | Match listed values | City IN ('Mysuru','Bengaluru') |
| LIKE | Match text patterns | Name LIKE 'A%' |
The important terms to remember
- Comparison: Checks equality and value differences.
- Logical: Combines or reverses conditions.
- BETWEEN: Includes both range boundaries.
- IN: Checks values against a list.
- LIKE: Searches text using wildcard patterns.
4. Common Mistakes and Best Practices
1. Forgetting BETWEEN includes both boundaries
Developers sometimes expect only values between the two boundaries. SQL Server includes both boundary values.
Better approach: Use > and < when exclusive boundaries are needed.
2. Using OR instead of AND
Using OR can return more records than expected. At least one condition only needs to match.
Better approach: Check whether every condition or only one condition must match.
3. Using wildcards incorrectly
'A%' and '%A' search different patterns. Their positions change the matching result.
Better approach: Choose wildcard positions based on the actual search requirement.
Important: SQL comparisons involving NULL need special handling. Use IS NULL or IS NOT NULL, not = NULL.
5. How SQL Operators Come Up in Interviews
Question 1: What is the difference between AND and OR?
What the interviewer expects: Explain how multiple conditions are evaluated.
Strong answer: "AND returns records when every condition matches. OR returns records when at least one condition matches. For example, Arjun can use AND for price and seat conditions."
Question 2: What is the difference between BETWEEN and IN?
What the interviewer expects: Explain range matching versus list matching.
Strong answer: "BETWEEN checks whether a value falls within a range. IN checks whether a value matches one of several listed values."
Question 3: What is the difference between LIKE and equality?
What the interviewer expects: Explain exact matching and pattern matching.
Strong answer: "Equality checks an exact value. LIKE checks a text pattern. For example, LIKE 'A%' finds names starting with A."
What interviewers expect at different levels
- Junior developer: Understand comparison, logical and LIKE operators.
- Senior developer: Combine operators and explain query results.
- Technical architect: Consider NULL handling, operator precedence and query performance.
6. Hands-On Practice
Beginner: School student records
Find students whose marks are greater than 75. Then find students from Bengaluru using IN.
Expected result: Retrieve only matching student records.
Intermediate: Bank transactions
Find transactions between 5,000 and 50,000. Exclude cancelled transactions using logical operators.
Hint: Combine BETWEEN, AND and NOT.
Advanced: Hospital appointments
Find appointments for selected cities and doctors whose names start with a particular letter.
- Filter appointments within a date range.
- Use IN to select multiple cities.
- Use LIKE to search doctor names.
Challenge: Combine the conditions without returning unwanted records.
7. Next Steps and Community
Previous article: WHERE, ORDER BY, DISTINCT and TOP in SQL Server
Next article: SQL Joins – INNER, LEFT, RIGHT, FULL and CROSS JOIN
For more details, read the official Microsoft documentation:
Try Arjun's cinema booking examples with your own conditions. Share your queries and discuss different filtering approaches with other learners.