SQL Comparison Operators are used to compare two values and filter the result set based on a specific condition.
- Used in the WHERE clause to filter records based on conditions
- Help check for equality, range, or inequality
- Make SQL queries powerful and precise
- Examples:
=,>,<,>=,<=,<>
Common SQL Comparison Operators
The below table shows all comparison operators in SQL :
| Operator | Description |
|---|---|
| = | The SQL Equal Operator checks if the values of two operands are equal. |
| != | The SQL Not Equal Operator checks if the values of two operands are not equal. |
| >= | The SQL Greater Than Equals to Operator checks if the value of the left operand is greater than or equal to the value of the right operand. |
| < | The SQL Less Than Operator checks if the value of the left operand is less than the value of the right operand. |
| > | The SQL Greater Than Operator checks if the value of the left operand is greater than the value of the right operand. |
| <= | The SQL Less Than Equals to Operator checks if the value of the left operand is less than or equal to the value of the right operand. |
Syntax
SELECT * FROM TABLE_NAME WHERE
ATTRIBUTE CONDITION_OPERATOR GIVEN_VALUE;
Create a Sample Table
Let's look at examples of comparison operators in SQL. We will understand different SQL comparison operators with example by using them in SQL query. First, we will create a demo database and table.
Query:
CREATE DATABASE GeeksForGeeks;
USE GeeksForGeeks;
CREATE TABLE MATHS(
ROLL_NUMBER INT,
S_NAME VARCHAR(10),
MARKS INT);
INSERT INTO MATHS (id, name, marks) VALUES
(1, 'ABHI', 70),
(2, 'RAVI', 80),
(3, 'ARJUN', 90),
(4, 'SAM', 100),
(5, 'MOHAN', 50),
(6, 'ROHAN', 10),
(7, 'ROCKY', 20),
(8, 'AYUSH', 40),
(9, 'NEHA', 30),
(10, 'KRITI', 60);
SELECT * FROM MATHS;Output:
SQL Comparison Operator Examples
Let's look at different comparison operators in SQL, and look at their examples.
1. Equal to (=) Operator: It returns the rows/tuples which have the value of the attribute equal to the given value.
Query:
SELECT * FROM MATHS WHERE MARKS=50;Output:
2. Greater than (>) Operator: It returns the rows/tuples which have the value of the attribute greater than the given value.
Query:
SELECT * FROM MATHS WHERE MARKS>60;Output:
3. Less than (<) Operator: It returns the rows/tuples which have the value of the attribute lesser than the given value.
Query:
SELECT * FROM MATHS WHERE MARKS<40;Output:
4. Greater than or equal to (>=) Operator: It returns the rows/tuples which have the value of the attribute greater or equal to the given value.
Query:
SELECT * FROM MATHS WHERE MARKS>=80;Output:
5. Less than or equal to (<=) Operator: It returns the rows/tuples which have the value of the attribute less or equal to the given value.
Query:
SELECT * FROM MATHS WHERE MARKS<=30;Output:
6. Not equal to (<>) Operator: It returns the rows/tuples which have the value of the attribute that is not equal to the given value.
Query:
SELECT * FROM MATHS WHERE MARKS<>70;Output:
