LATEST 🚀 Welcome to Upgradix by Shrash Studio – Your destination for free technology learning and career growth.
🕘 Updated:
MS SQL Server

SQL Operators – Comparison, Logical, BETWEEN, IN and LIKE

SQL operators help us filter records and compare values. Comparison, logical, BETWEEN, IN and LIKE operators make SQL queries useful for real business requirements.

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:

  1. We retrieve records that may not match the customer's request.
  2. Manual filtering becomes difficult with thousands of bookings.
  3. Combining multiple business conditions takes additional effort.
  4. 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 Cinema ticket counter manager
checks five bookings
Booking Records 101, 102, 103, 104, 105
checks ticket prices
BETWEEN TicketPrice 300 to 500
checks combined conditions
AND + IN + LIKE Two cities · Names starting A
excludes Regular seats
Matching Bookings 101 Arjun 350 · 102 Ananya 450
Input records SQL processing Matching results Current animation stage

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.

Common comparison operators in SQL Server
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.

Three important logical operators
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:

LIKE 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

Choosing the right SQL operator
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.

Comments & Ratings (0)

Log in or create a student account to comment and rate this article.

Be the first to comment on this article.