EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login

SQL Operators

By Priya PedamkarPriya Pedamkar

Home » Data Science » Data Science Tutorials » SQL Tutorial » SQL Operators

sql operators

Introduction to SQL Operators

SQL Operators are defined as a symbol or special reserved keywords used to specify some action that is performed on the given expression which includes several clauses to tell the system how the operators behave. Operators do calculations between the data items or operand and execute the Query result. It provides a condition in SQL statements or also combines multiple conditions and executes them between the operands (filters the results). The operators which are used between the data can come with single or with binary operands. All the operators are specified with the conditional statements along with the WHERE clause of SQL.

Types of SQL Operators

SQL is the foundation of database management systems that deals with a variety of manipulation of data. therefore, SQL provides some SQL Operators to perform Operations and while Evaluating the Expression order of precedence is very important. Following are the various Operators used in SQls.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

  1. Arithmetic Operators
  2. Comparison Operators
  3. Logical Operators

Let’s see One by one in detail:

1. Arithmetic Operators

These operators are used to manipulate mathematical calibrations like addition, multiplication, division, subtraction and other modulus numeric values in the SQL query.

Syntax:

Select <expression 1> operator <expression 2>

1. Addition

This operator Helps in adding values on both sides of the operators. Below are the unary Operators. Example: M + N

Code:

SQL> select 11+ 20;

Output:

SQL Operator - 1

2. Subtraction

This operator implies subtracting values on both sides of the operator (right value from the left). Example: M – N

Code:

SQL> select 30- 20;

Output:

SQL Operator - 2

3. Multiplication

This SQL operator does multiplication operation between two operands. Example: M*N

Popular Course in this category
SQL Training Program (7 Courses, 8+ Projects)7 Online Courses | 8 Hands-on Projects | 73+ Hours | Verifiable Certificate of Completion | Lifetime Access
4.5 (5,619 ratings)
Course Price

View Course

Related Courses
JDBC Training (6 Courses, 7+ Projects)PHP Training (5 Courses, 3 Project)Windows 10 Training (4 Courses, 4+ Projects)PL SQL Training (4 Courses, 2+ Projects)Oracle Training (14 Courses, 8+ Projects)

Code:

SQL> select 12 * 6;

Output:

SQL Operator - 3

4. Division

This operator performs actions by dividing one expression by the other expressions. Example: M/N

Code:

SQL> select 120 / 5;

Output:

SQL Operator - 4

5. Modulus

This SQL operator returns the remainder of the division process between two operands. The following statement for this is:

Code:

SQL> select 12 / 7;

Output:

SQL Operator - 5

  • Below Query using Arithmetic Operation on a specific Database Table. Consider a table ‘Finance’ with four Columns.

SQL Operator - 6

  • Using Arithmetic Operators on the Specific fields On the Table. Here I have taken the Salary column to perform the Arithmetic operation.

Example #1

Code:

SELECT fid, FName, salary, salary + 50
AS "Revised Sal" FROM finance;

Output:

SQL Operator - 7

Example #2

Code:

SELECT fid, FName, salary, salary - 150
AS "Revised Sal" FROM finance;

Output:

SQL Operator - 8

  • That all about the Arithmetic operations of SQL. In the next section, we shall see Comparison operators

2. Comparison Operators or Relational Operators

Being conditional takes two expressions and does the valuable comparison and returns either True or False. It does the operations such as equal to, lesser than, greater than or greater than equal to and other advanced concepts too. This operator joins their hands with the ‘where’ clause to select the particular columns in the records. Here in Below Section, we describe different types of relational operators and few examples on them along with the syntax.

Syntax:

SELECT column FROM table WHERE condition1 Relational Operator condition2;

1. Equal to (=)

This operator checks the value of the two operands is the same or not. If it is equal then it returns true, if not returns false.

Code:

SELECT FName, salary FROM finance where salary =5000;

Output:

SQL Operator - 9

2. Not equal to or Inequality (! =), (<>)

It verifies whether the two operands’ values are equal or not. If they are not equal then the statement returns True.

Code:

SELECT FName, salary FROM finance where salary <>2500;

Output:

SQL Operator - 10

3. Greater than (>)

It is used in SQL to check for the greater than a value between two operands.

Code:

SELECT FName, salary FROM finance where salary>5000;

Output:

Greater than (>)

4. Greater than equal to (> =)

It is used in SQL to check for the greater than or equal to a value between two operands.

Code:

SELECT FName, salary FROM finance where salary>=5000;

Output:

Greater than equal to (> =)

5. Lesser than (<)

This operator in SQL is used to check whether a left operand is lesser than the right operand. If it is true it results in the result.

Code:

SELECT FName, salary FROM finance where salary < 5000;

Output:

Lesser than (<)

6. Lesser than or Equal to (<=)

This operator in SQL is used to check whether a left operand is lesser than or equal to the right operand. If it is true it results in the result.

Code:

SELECT FName, salary FROM finance where salary <=4500;

Output:

SQL Operator - 14

Some advanced operator is Like, IS Not Null, Between which we shall see later.

3. Logical Operators

Logical operations in the SQL which is true or False are performed by the Logical Operators. Different operators used here are listed below:

  • AND
  • OR
  • NOT
  • BETWEEN
  • ANY

Syntax:

SELECT col name  * | expr [logical operator] [col name | * | expr..] FROM tablename
WHERE <expr> [ logical operator |
arithmetic operator | ...] <expressions>;

Considering the same database Finance to perform the Logical operators:

1. AND Operator

It gives preference to make use of multiple conditions with the WHERE clause.

Code:
SELECT  fid,FName,address,salary
FROM finance
WHERE  FName  =  'Leslia' AND  salary  =  2500;

Output:

AND Operator

2. OR

This makes a comparison between the expressions in the statement and returns true if either of the condition is True.

Code:

SELECT  fid,FName,address,salary
FROM finance
WHERE  FName  =  'Gruce TOM' OR salary  =  2500;

Output:

 OR

3. NOT

This operator takes an argument as a single Boolean and returns true if it is false and vice-versa.

Code:

SELECT Fid , address FROM finance WHERE NOT FName = "Dany";

Output:

NOT

4. ANY

A specific value is taken and it is compared with any of the values in the fields.

Code:

SELECT * FROM finance
WHERE salary > ANY (SELECT salary FROM finance WHERE salary > 5000);

Output:

ANY

5. BETWEEN

This operator is used when there is a limit range between the values.

Code:

SELECT * FROM finance
WHERE salary BETWEEN 2500 AND 5000;

Output:

BETWEEN

Conclusion

Therefore, in this article, we have learned how to use different operators in SQL Statements with an implementation. Also, we came across the filtering of data from the database using respective conditions as the operators allow in checking two expressions to be the same.

Recommended Articles

This is a guide to SQL Operators. Here we discuss the basic concept with 3 different types of SQL in detail with appropriate codes and outputs respectively. You can also go through our other related articles to learn more –

  1. SQL Set Operators
  2. SQL Clauses
  3. PostgreSQL ORDER BY
  4. SQL String Operators

SQL Training Program (7 Courses, 8+ Projects)

7 Online Courses

8 Hands-on Projects

73+ Hours

Verifiable Certificate of Completion

Lifetime Access

Learn More

0 Shares
Share
Tweet
Share
Primary Sidebar
SQL Tutorial
  • Operators
    • SQL Operators
    • SQL Arithmetic Operators
    • SQL Logical Operators
    • SQL String Operators
    • Ternary Operator in SQL
  • Basic
    • What is SQL
    • Careers in SQL
    • Careers in SQL Server
    • IS SQL Microsoft?
    • SQL Management Tools
    • What is SQL Developer
    • Uses of SQL
    • How to Install SQL Server
    • What is SQL Server
    • Database in SQL
    • SQL Data Types
    • SQL Keywords
    • Composite Key in SQL
    • SQL Constraints
    • Transactions in SQL
    • First Normal Form
    • SQL Server Data Types
    • SQL Administration
    • SQL Variables
    • SQL Enum
    • Cheat sheet SQL
  • Commands
    • SQL Commands
    • SQL Alter Command
    • SQL Commands Update
    • SQL DML Commands
    • SQL DDL Commands
    • FETCH in SQL
  • Clause
    • SQL Clauses
    • SQL IN Operator
    • SQL LIKE Clause
    • SQL NOT Operator
    • SQL Minus
    • SQL WHERE Clause
    • SQL with Clause
    • SQL HAVING Clause
    • GROUP BY clause in SQL
    • SQL GROUP BY DAY
    • ORDER BY Clause in SQL
    • SQL ORDER BY CASE
    • SQL ORDER BY DATE
    • SQL ORDER BY Alphabetical
    • SQL ORDER BY Ascending
    • SQL Order by Count
    • SQL GROUP BY Month
    • SQL GROUP BY Multiple Columns
    • SQL GROUPING SETS
  • Queries
    • SQL Insert Query
    • SQL SELECT Query
    • SQL SELECT RANDOM
    • SQL Except Select
    • SQL Subquery
    • SQL SELECT DISTINCT
    • SQL WITH AS Statement
  • Keys
    • SQL Keys
    • Primary Key in SQL
    • Foreign Key in SQL
    • Unique Key in SQL
    • Alternate Key in SQL
    • SQL Super Key
  • Functions
    • SQL Date Function
    • SQL String Functions
    • SQL Compare String
    • SQL Window Functions
    • SQL Timestamp
    • SQL TO_DATE()
    • SQL DATEADD()
    • SQL DATEDIFF()
    • ANY in SQL
    • LIKE Query in SQL
    • BETWEEN in SQL
    • LTRIM() in SQL
    • TOP in SQL
    • SQL Select Top
    • Merge SQL
    • SQL TRUNCATE()
    • SQL UNION
    • SQL ALL
    • SQL INTERSECT
    • SQL Alias
    • SQL Server Substring
    • CUBE in SQL
    • SQL RANK()
    • SQL MOD()
    • SQL CTE
    • SQL LAG()
    • SQL MID
    • SQL avg()
    • SQL WEEK
    • SQL DELETE
    • SQL DATEPART()
    • SQL DECODE()
    • SQL DENSE_RANK()
    • SQL NTILE()
    • SQL NULLIF()
    • SQL Stuff
    • SQL Ceiling
    • SQL EXISTS
    • SQL LEAD()
    • SQL COALESCE
    • SQL BLOB
    • SQL ROW_NUMBER
    • SQL Server Replace
    • SQL Server Permission
    • T-SQL INSERT
    • SQL Ranking Function
  • Joins
    • Join Query in SQL
    • Types of Joins in SQL
    • Types of Joins in SQL Server
    • SQL Inner Join
    • SQL Join Two Tables
    • SQL Delete Join
    • SQL Left Join
    • SQL Right Join
    • SQL Cross Join
    • SQL Outer Join
    • SQL Full Join
    • SQL Self Join
    • Natural Join SQL
    • SQL Multiple Join
  • Advanced
    • SQL Formatter
    • SQL Injection Attack
    • Aggregate Functions in SQL
    • IF ELSE Statement in SQL
    • SQL CASE Statement
    • SQL While Loop
    • SQL INSTR()
    • What is Procedure in SQL
    • Stored Procedure in SQL?
    • SQL Server Constraints
    • SQL DELETE ROW
    • Column in SQL
    • Table in SQL
    • SQL Virtual Table
    • SQL Merge Two Tables
    • SQL Table Partitioning
    • SQL Temporary Table
    • SQL Clone Table
    • SQL Rename Table
    • SQL LOCK TABLE
    • SQL Clear Table
    • SQL DESCRIBE TABLE
    • SQL Mapping
    • Cursors in SQL
    • AND in SQL
    • Wildcard in SQL
    • SQL FETCH NEXT
    • SQL Views
    • SQL Delete View
    • Triggers in SQL
    • SQL UPDATE Trigger
    • SQL AFTER UPDATE Trigger
    • SQL Update Statement
    • SQL DROP TRIGGER
    • Types of SQL Views
    • SQL Port
    • SQL Clustered Index
    • SQL COMMIT
    • Distinct Keyword in SQL
    • PARTITION BY in SQL
    • SQL Set Operators
    • SQL UNION ALL
    • Metadata in SQL
    • SQL Bulk Insert
    • Array in SQL
    • SQL REGEXP
    • JSON in SQL
    • SQL For loop
    • EXPLAIN in SQL
    • SQL Cluster
    • SQL Backup
    • SQL Pattern Matching
    • SQL Users
    • ISNULL SQL Server
    • SQL pivot
    • SQL Import CSV
  • Interview Questions
    • SQL Interview Questions
    • Advance SQL Interview Questions
    • SQL Joins Interview Questions
    • SQL Server Interview Questions

Related Courses

JDBC Training Course

PHP course

Windows 10 Training

SQL Course Training

PL/SQL Certification Courses

Oracle Certification Courses

Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

© 2020 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA Login

Forgot Password?

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you
Book Your One Instructor : One Learner Free Class

Let’s Get Started

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

Special Offer - SQL Training Program (7 Courses, 8+ Projects) Learn More