SQL Comparison operator
Comparison operator
A comparison (or relational) operator is a mathematical symbol which is used to compare two values.
Comparison operators are used in conditions that compares one expression with another. The result of a comparison can be TRUE, FALSE, or UNKNOWN (an operator that has one or two NULL expressions returns UNKNOWN).
The following table describes different types of comparison operators -
| Operator | Description | Operates on |
|---|---|---|
| = | Equal to. | Any compatible data types |
| > | Greater than. | Any compatible data types |
| < | Less than. | Any compatible data types |
| >= | Greater than equal to. | Any compatible data types |
| <= | Less than equal to. | Any compatible data types |
| <> | Not equal to. | Any compatible data types |
Syntax :
SELECT[column_name| * |expression]<comparison operator> [column_name | * | expression ] FROM <table_name> WHERE <expression>[comparison operator]<expression>;
Parameters:
| Name | Description |
|---|---|
| column_name | Name of the column of a table. |
| * | Indicates all the columns of a table. |
| expression | Expression made up of a single constant, variable, scalar function, or column name and can also be the pieces of a SQL query that compare values against other values or perform arithmetic calculations. |
| table_name | Name of the table. |
| comparison operator | Equal to (=), not equal to(<>), greater than(>), less than(<), greater than or equal to (>=), less than or equal to (<=). |
Contents:
Example: SQL Comparison operator
To get a comparison between two numbers from the DUAL table, the following SQL statement can be used :
SELECT 15>14 FROM dual;
SQL Equal to ( = ) operator
The equal to operator is used for equality test within two numbers or expressions.
Example:
Sample table: agents
To get data of all columns from the 'agents' table with the following condition -
1. 'commission' is equal to .15,
the following SQL statement can be used :
SQL Code:
SELECT *
FROM agents
WHERE commission = 0.15;
Output:
AGENT_ AGENT_NAME WORKING_AREA COMMISSION PHONE_NO COUNTRY ------ ------------------------------ ----------------- ---------- --------------- -------- A007 Ramasundar Bangalore .15 077-25814763 A011 Ravi Kumar Bangalore .15 077-45625874 A006 McDen London .15 078-22255588 A004 Ivan Torento .15 008-22544166
Relational Algebra Expression:
Relational Algebra Tree:
SQL Greater than ( > ) operator
The greater than operator is used to test whether an expression (or number) is greater than another one.
Example:
To get data of all columns from the 'agents' table with the following condition -
1. 'commission' is greater than .14,
the following SQL statement can be used :
SQL Code:
SELECT *
FROM agents
WHERE commission> 0.14;
Output:
AGENT_ AGENT_NAME WORKING_AREA COMMISSION PHONE_NO COUNTRY ------ -------------- ----------------------------------- ---------- --------------- ------- A007 Ramasundar Bangalore .15 077-25814763 A011 Ravi Kumar Bangalore .15 077-45625874 A006 McDen London .15 078-22255588 A004 Ivan Torento .15 008-22544166
Relational Algebra Expression:
Relational Algebra Tree:
SQL Less than ( < ) operator
The less than operator is used to test whether an expression (or number) is less than another one.
Example:To get data of all columns from the 'agents' table with the following condition -
1. 'commission' is less than .12,
the following SQL statement can be used :
SQL Code:
SELECT *
FROM agents
WHERE commission < 0.12;
Output:
AGENT_ AGENT_NAME WORKING_AREA COMMISSION PHONE_NO COUNTRY ------ -------------- ----------------------------------- ---------- --------------- --------- A009 Benjamin Hampshair .11 008-22536178 A002 Mukesh Mumbai .11 029-12358964
Relational Algebra Expression:
Relational Algebra Tree:
SQL Greater than or equal to ( >= ) operator
The greater than equal to operator is used to test whether an expression (or number) is either greater than or equal to another one.
Example:
To get data of all columns from the 'agents' table with the following condition -
1. 'commission' is greater than or equal to .14,
the following SQL statement can be used :
SQL Code:
SELECT *
FROM agents
WHERE commission >= 0.14;
Output:
AGENT_ AGENT_NAME WORKING_AREA COMMISSION PHONE_NO COUNTRY ------ --------------- ----------------------------------- ---------- --------------- -------- A001 Subbarao Bangalore .14 077-12346674 A007 Ramasundar Bangalore .15 077-25814763 A011 Ravi Kumar Bangalore .15 077-45625874 A010 Santakumar Chennai .14 007-22388644 A006 McDen London .15 078-22255588 A004 Ivan Torento .15 008-22544166
Relational Algebra Expression:
Relational Algebra Tree:
SQL Less than or equal to ( <= ) operator
The less than equal to operator is used to test whether an expression (or number) is either less than or equal to another one.
Example:
To get data of all columns from the 'agents' table with the following condition -
1. commission is less than or equal to .12,
the following SQL statement can be used :
SQL Code:
SELECT *
FROM agents
WHERE commission <= 0.12;
Output:
AGENT_ AGENT_NAME WORKING_AREA COMMISSION PHONE_NO COUNTRY ------ --------------- ---------------------------------- ---------- --------------- --------- A009 Benjamin Hampshair .11 008-22536178 A008 Alford New York .12 044-25874365 A012 Lucida San Jose .12 044-52981425 A002 Mukesh Mumbai .11 029-12358964
Relational Algebra Expression:
Relational Algebra Tree:
SQL Not equal to ( <> ) operator
The not equal to operator is used for inequality test between two numbers or expression.
Example:To get data of all columns from the 'agents' table with the following condition -
1. commission is not equal to .15,
the following SQL statement can be used :
SQL Code:
SELECT *
FROM agents
WHERE commission <> 0.15;
Output:
AGENT_ AGENT_NAME WORKING_AREA COMMISSION PHONE_NO COUNTRY ------ ------------- ----------------------------------- ---------- --------------- -------- A003 Alex London .13 075-12458969 A001 Subbarao Bangalore .14 077-12346674 A009 Benjamin Hampshair .11 008-22536178 A008 Alford New York .12 044-25874365 A010 Santakumar Chennai .14 007-22388644 A012 Lucida San Jose .12 044-52981425 A005 Anderson Brisban .13 045-21447739 A002 Mukesh Mumbai .11 029-12358964
Relational Algebra Expression:
Relational Algebra Tree:
Practice SQL Exercises
- SQL Exercises, Practice, Solution
- SQL Retrieve data from tables [33 Exercises]
- SQL Boolean and Relational operators [12 Exercises]
- SQL Wildcard and Special operators [22 Exercises]
- SQL Aggregate Functions [25 Exercises]
- SQL Formatting query output [10 Exercises]
- SQL Quering on Multiple Tables [8 Exercises]
- FILTERING and SORTING on HR Database [38 Exercises]
- SQL JOINS
- SQL SUBQUERIES
- SQL Union[9 Exercises]
- SQL View[16 Exercises]
- SQL User Account Management [16 Exercise]
- Movie Database
- BASIC queries on movie Database [10 Exercises]
- SUBQUERIES on movie Database [16 Exercises]
- JOINS on movie Database [24 Exercises]
- Soccer Database
- Introduction
- BASIC queries on soccer Database [29 Exercises]
- SUBQUERIES on soccer Database [33 Exercises]
- Hospital Database
- Employee Database
- More to come!
Want to improve the above article? Contribute your Notes/Comments/Examples through Disqus.
SQL: Tips of the Day
SQL Server SELECT into existing table.
INSERT INTO dbo.TABLETWO SELECT col1, col2 FROM dbo.TABLEONE WHERE col3 LIKE @search_key
This assumes there's only two columns in dbo.TABLETWO - you need to specify the columns otherwise:
INSERT INTO dbo.TABLETWO (col1, col2) SELECT col1, col2 FROM dbo.TABLEONE WHERE col3 LIKE @search_key
Database: SQL Server
Ref: https://bit.ly/3y6tpA3
- New Content published on w3resource:
- HTML-CSS Practical: Exercises, Practice, Solution
- Java Regular Expression: Exercises, Practice, Solution
- Scala Programming Exercises, Practice, Solution
- Python Itertools exercises
- Python Numpy exercises
- Python GeoPy Package exercises
- Python Pandas exercises
- Python nltk exercises
- Python BeautifulSoup exercises
- Form Template
- Composer - PHP Package Manager
- PHPUnit - PHP Testing
- Laravel - PHP Framework
- Angular - JavaScript Framework
- Vue - JavaScript Framework
- Jest - JavaScript Testing Framework