SQL Topics
SQL Operators
title: SQL Operators
SQL operators are special symbols and keywords used to perform operations on data. They help compare values, combine conditions, perform calculations, and filter records.
Without operators, SQL would only be able to retrieve entire tables. Operators make SQL powerful by allowing developers to search, analyze, and manipulate data efficiently.
Imagine a database containing thousands of employee records. Operators allow you to answer questions such as:
- Which employees earn more than ₹50,000?
- Which students are older than 18?
- Which products cost between ₹500 and ₹1000?
- Which users are active and verified?
In this lesson, you will learn the different types of SQL operators, how they work, and when to use them.
What Are SQL Operators?
An operator is a symbol or keyword that performs an action on one or more values.
Example:
SELECT *
FROM Students
WHERE Age > 18;Here:
>is an operator.
It compares the value in the Age column with 18.
The database returns only records where the condition is true.
Why Are SQL Operators Important?
Operators make SQL queries dynamic and useful.
Without operators:
SELECT * FROM Students;returns every record.
With operators:
SELECT *
FROM Students
WHERE Age > 18;returns only specific records.
Benefits include:
- Data filtering
- Calculations
- Searching
- Reporting
- Decision making
- Query optimization
Categories of SQL Operators
SQL operators can be divided into several categories:
- Arithmetic Operators
- Comparison Operators
- Logical Operators
- Special Operators
- Bitwise Operators (Database Specific)
Most developers primarily use the first four categories.
Arithmetic Operators
Arithmetic operators perform mathematical calculations.
They work similarly to operators in mathematics.
Addition Operator (+)
Adds two values.
Example:
SELECT 10 + 5;Result:
15Practical example:
SELECT Salary + Bonus
FROM Employees;This calculates total employee compensation.
Subtraction Operator (-)
Subtracts one value from another.
Example:
SELECT 20 - 5;Result:
15Used in financial and reporting calculations.
Multiplication Operator (*)
Multiplies values.
Example:
SELECT 10 * 5;Result:
50Practical example:
SELECT Quantity * Price
FROM Orders;Calculates total order value.
Division Operator (/)
Divides values.
Example:
SELECT 20 / 5;Result:
4Commonly used in averages and percentage calculations.
Modulus Operator (%)
Returns the remainder after division.
Example:
SELECT 10 % 3;Result:
1Useful for checking odd and even numbers.
Comparison Operators
Comparison operators compare two values and return either TRUE or FALSE.
These operators are frequently used with the WHERE clause.
Equal To (=)
Checks whether two values are equal.
Example:
SELECT *
FROM Students
WHERE Age = 20;Returns students whose age is exactly 20.
Not Equal To (!=)
Checks whether values are different.
Example:
SELECT *
FROM Students
WHERE Age != 20;Returns all students except those aged 20.
Some database systems also support:
<>which serves the same purpose.
Greater Than (>)
Checks whether the left value is larger.
Example:
SELECT *
FROM Employees
WHERE Salary > 50000;Returns employees earning more than ₹50,000.
Less Than (<)
Checks whether the left value is smaller.
Example:
SELECT *
FROM Students
WHERE Age < 18;Returns students younger than 18.
Greater Than or Equal To (>=)
Example:
SELECT *
FROM Employees
WHERE Salary >= 50000;Returns employees earning ₹50,000 or more.
Less Than or Equal To (<=)
Example:
SELECT *
FROM Products
WHERE Price <= 1000;Returns products priced ₹1000 or below.
Logical Operators
Logical operators combine multiple conditions.
They are extremely important when filtering data.
AND Operator
AND requires all conditions to be true.
Example:
SELECT *
FROM Employees
WHERE Salary > 50000
AND Department = 'IT';This query returns employees who:
- Earn more than ₹50,000
- Work in the IT department
Both conditions must be satisfied.
OR Operator
OR requires at least one condition to be true.
Example:
SELECT *
FROM Students
WHERE Age = 18
OR Age = 19;Returns students who are either 18 or 19 years old.
NOT Operator
NOT reverses a condition.
Example:
SELECT *
FROM Employees
WHERE NOT Department = 'HR';Returns employees who are not in the HR department.
Special SQL Operators
SQL includes several specialized operators designed for common database tasks.
BETWEEN Operator
Checks whether a value falls within a range.
Example:
SELECT *
FROM Products
WHERE Price BETWEEN 500 AND 1000;Returns products priced between ₹500 and ₹1000.
IN Operator
Checks whether a value exists in a list.
Example:
SELECT *
FROM Students
WHERE Age IN (18, 19, 20);Returns students whose age matches any value in the list.
LIKE Operator
Used for pattern matching.
Example:
SELECT *
FROM Students
WHERE Name LIKE 'R%';Returns names starting with the letter R.
Examples:
Rahul
Rohan
RitikaIS NULL Operator
Checks for NULL values.
Example:
SELECT *
FROM Employees
WHERE Bonus IS NULL;Returns employees whose bonus value is missing.
EXISTS Operator
Checks whether a subquery returns records.
Example:
SELECT *
FROM Students
WHERE EXISTS (
SELECT *
FROM Enrollments
);Often used in advanced SQL queries.
Operator Precedence
SQL follows a specific order when evaluating operators.
General precedence:
1. Parentheses ()
2. Arithmetic Operators
3. Comparison Operators
4. NOT
5. AND
6. ORExample:
SELECT *
FROM Students
WHERE Age > 18
AND Class = 10
OR Class = 12;Without understanding precedence, results may be unexpected.
Using parentheses improves clarity:
SELECT *
FROM Students
WHERE Age > 18
AND (Class = 10 OR Class = 12);This is easier to understand and maintain.
Real-World Example
Consider an employee database.
You need employees who:
- Work in IT
- Earn more than ₹60,000
- Are currently active
Query:
SELECT *
FROM Employees
WHERE Department = 'IT'
AND Salary > 60000
AND IsActive = TRUE;Operators help transform business requirements into database queries.
Common Beginner Mistakes
Using = Instead of IS NULL
Wrong:
WHERE Bonus = NULLCorrect:
WHERE Bonus IS NULLConfusing AND and OR
Example:
WHERE Age > 18
AND Age < 25is very different from:
WHERE Age > 18
OR Age < 25Always verify the intended logic.
Ignoring Parentheses
Complex conditions may produce unexpected results if parentheses are omitted.
Use parentheses to make logic clear.
Comparing Text Incorrectly
Wrong:
WHERE Name = RahulCorrect:
WHERE Name = 'Rahul'Text values require quotation marks.
Best Practices
Use Parentheses for Complex Conditions
Improve readability and avoid logical mistakes.
Use Meaningful Conditions
Write queries that clearly express business requirements.
Test Conditions Carefully
Verify that results match expectations.
Keep Queries Readable
Format conditions across multiple lines when necessary.
Understand NULL Behavior
NULL handling is one of the most common sources of SQL errors.
Summary
SQL operators are essential tools that allow databases to compare values, perform calculations, combine conditions, and filter records efficiently.
In this lesson, you learned:
- What SQL operators are
- Arithmetic operators
- Comparison operators
- Logical operators
- Special operators
- Operator precedence
- Common mistakes
- Best practices
Understanding operators is critical because they are used in almost every SQL query, from simple searches to advanced analytical reports.
Next Step
Continue to the next lesson:
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for SQL Operators.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this SQL topic.
Search Terms
sql, sql complete guide, sql tutorial, sql notes, complete, guide, basics, operators
Related SQL Topics