SQL Comparison Operators
SQL Comparison Operators are used to compare two values and check if they meet the specific criteria. Some comparison operators are = Equal to, > Greater than , < Less than, etc.
Comparison Operators in SQL
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
SQL Comparison Operators syntax is:
SELECT * FROM TABLE_NAME WHERE
ATTRIBUTE CONDITION_OPERATOR GIVEN_VALUE;
SQL Comparison Operator Examples
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 w3wiki;
USE w3wiki;
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:
Using Comparison Operators in SQL Example
Let’s look at different comparison operators in SQL, and look at their examples.
Equal to (=) Operator: It returns the rows/tuples which have the value of the attribute equal to the given value.
= Equal to Operator Example:
SELECT * FROM MATHS WHERE MARKS=50;
Output:
Greater than (>) Operator: It returns the rows/tuples which have the value of the attribute greater than the given value.
Greater than (>) Operator Example:
SELECT * FROM MATHS WHERE MARKS>60;
Output:
) Operator in SQL" height="inherit" loading="lazy" src="/public/files/WhatsAppImage20211027at150928.jpeg" width="304">
Less than (<) Operator: It returns the rows/tuples which have the value of the attribute lesser than the given value.
Less than (<) Operator Example:
SELECT * FROM MATHS WHERE MARKS<40;
Output:
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.
>= Greater than or equal to Operator Example:
SELECT * FROM MATHS WHERE MARKS>=80;
Output:
=) Operator in SQL" height="inherit" loading="lazy" src="/public/files/WhatsAppImage20211027at151411.jpeg" width="315">
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.
<= Less than or equal to Operator Example:
SELECT * FROM MATHS WHERE MARKS<=30;
Output:
Not equal to (<>) Operator: It returns the rows/tuples which have the value of the attribute that is not equal to the given value.
<> Not equal to Operator Example:
SELECT * FROM MATHS WHERE MARKS<>70;
Output:
) Operator in SQL" height="inherit" loading="lazy" src="/public/files/WhatsAppImage20211027at151910.jpeg" width="308">
Conclusion
SQL comparison operators also knows as relational or boolean operators, are used to compare values in a database and find if they are equal to (=), not equal to (!=,<>) greater than (>), less than (<), less than or equal to (<=) and greater than or equal to (>=). They are used inside the WHERE clause.