SQL Operators

SQL Operators perform arithmetic, comparison, and logical operations to manipulate and retrieve data from databases.

In this article, we will discuss Operators in SQL with examples, and understand how they work in SQL.

Operators in SQL

Operators in SQL are symbols that help us to perform specific mathematical and logical computations on operands. An operator can either be unary or binary.

The unary operator operates on one operand, and the binary operator operates on two operands.

Types of Operators in SQL

Different types of operators in SQL are:

• Arithmetic operator

• Comparison operator

• Logical operator

• Bitwise Operators

• Compound Operators

SQL Arithmetic Operators

Arithmetic operators in SQL are used to perform mathematical operations on numeric values in queries. Some common arithmetic operators are:

Operator	Description
+	The addition operator is used to perform an addition operation on data values.
–	This operator is used for the subtraction of data values.
/	This operator works with the ‘ALL’ keyword and calculates division operations.
*	This operator is used for multiplying data values.
%	Modulus is used to get the remainder when data is divided by another.

SQL Arithmetic Operators Example

In this example, we will retrieve all records from the “employee” table where the “emp_city” column does not start with the letter ‘A’.

Query:

SELECT * FROM employee WHERE emp_city NOT LIKE 'A%';

Output:

SQL Comparison Operators

Comparison Operators in SQL are used to compare one expression’s value to other expressions. SQL supports different types of comparison operator, which are described below:

Operator	Description
=	Equal to.
>	Greater than.
<	Less than.
>=	Greater than or equal to.
<=	Less than or equal to.
<>	Not equal to.

SQL Comparison Operators Example

In this example, we will retrieve all records from the “MATHS” table where the value in the “MARKS” column is equal to 50.

Query:

SELECT * FROM MATHS WHERE MARKS=50;

Output:

SQL Logical Operators

Logical Operators in SQL are used to combine or manipulate conditions in SQL queries to retrieve or manipulate data based on specified criteria.

Operator	Description
AND	Logical AND compares two Booleans as expressions and returns true when both expressions are true.
OR	Logical OR compares two Booleans as expressions and returns true when at least one expression is true.
NOT	NOT takes a single Boolean as an argument and changes its value from false to true, or from true to false.

SQL Logical Operators Example

In this example, retrieve all records from the “employee” table where the “emp_city” column is equal to ‘Allahabad’ and the “emp_country” column is equal to ‘India’.

Query:

SELECT * FROM employee WHERE emp_city = 'Allahabad' AND emp_country = 'India';

Output:

SQL Bitwise Operators

Bitwise operators in SQL are used to perform bitwise operations on binary values in SQL queries, manipulating individual bits to perform logical operations at the bit level. Some SQL Bitwise Operators are:

Operator	Description
&	Bitwise AND operator
**	**
^	Bitwise XOR (exclusive OR) operator
~	Bitwise NOT (complement) operator
<<	Left shift operator
>>	Right shift operator

SQL Compound Operators

Compound operator in SQL are used to perform an operation and assign the result to the original value in a single line. Some Compound operators are:

Operator	Description
+=	Add and assign
-=	Subtract and assign
*=	Multiply and assign
/=	Divide and assign
%=	Modulo and assign
&=	Bitwise AND and assign
^=	Bitwise XOR and assign
**	=**

SQL Special Operators

Special operators are used in SQL queries to perform specific operations like comparing values, checking for existence, and filtering data based on certain conditions.

Operator	Description
ALL	Selects all records in a SELECT statement; compares a value to every value in a list of query results. Evaluates as TRUE if no rows are returned in the query.
ANY	Compares a value to each value in a list of query results, evaluating to TRUE if at least one row matches.
BETWEEN	Tests if a value lies within a specific range. Written as BETWEEN <start> AND <end>.
IN	Checks if a value exists within a specified set of values. Retrieves rows matching any of these values.
EXISTS	Tests if a subquery returns any rows. Returns TRUE if the subquery fetches at least one row; FALSE otherwise.
SOME	Evaluates a condition across tables, returning TRUE if at least one row matches the condition in the inner query; otherwise returns FALSE.
UNIQUE	Retrieves rows with unique values for specified columns in a table, avoiding duplicates.

SQL Special Operator Example

In this example, we will retrieve all records from the “employee” table where the “emp_id” column has a value that falls within the range of 101 to 104 (inclusive).

Query:

SELECT * FROM employee WHERE emp_id BETWEEN 101 AND 104;

Output:

Conclusion

SQL Operators are used to perform various operations on the data using SQL queries. These operators simplify arithmetic, comparison , logical, and bitwise operations on the data.

In this tutorial, we have explained SQL operators in detail. We have explained different types of operators in SQL along with their definition and examples. Using SQL operators you can efficiently perform operations on data.