SQL Comparison Operators

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 :

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:

Suggested Quiz

Edit Quiz

5 Questions

Which of the following is not the SQL Comparison Operator?

• A

  SQL Equal Operator (=)

• B

  SQL Less Than Operator (<)

• C

  SQL Greater Than Operator (>)

• D

  None of the above

Explanation:

SQL Equal Operator (=), SQL Less Than Operator (<), and SQL Greater Than Operator (>) are all SQL Comparison Operators.

Which of the following query is correct for using comparison operators in SQL?

• A

  SELECT name, course_name FROM student WHERE age>50 and WHERE age<80;

• B

  SELECT name, course_name FROM student WHERE age>50 and <80;

• C

  SELECT name, course_name FROM student WHERE age>50 and age <80;

• D

  None of these

Explanation:

Query in option C is in the correct format, the rest are not. 

What is the correct way to use the ‘not equal to’ operator in SQL?

• A

  !=

• B

  <> 

• C

  Both A & B

• D

  None of the above

Explanation:

!=, <> both refer to ‘not equal to’ operator in SQL

What is the purpose of comparison operators in a WHERE clause?

• A

  Rename columns

• B

  Filter rows based on specific conditions

• C

  Perform arithmetic calculations

• D

  Create new tables

Explanation:

The article states comparison operators help filter data by checking equality, range, or inequality.

Which query retrieves rows where MARKS are less than or equal to 30?

• A

  SELECT * FROM MATHS WHERE MARKS < 30;

• B

  SELECT * FROM MATHS WHERE MARKS >= 30;

• C

  SELECT * FROM MATHS WHERE MARKS <> 30;

• D

  SELECT * FROM MATHS WHERE MARKS <= 30;

Explanation:

The article’s example uses <= to fetch values less than or equal to the given number.

Quiz Completed Successfully

Your Score :   2/5

Accuracy :  0%

Login to View Explanation

1/5 1/5 < Previous Next >