SQL Operators

What do you mean by SQL Operators?

SQL queries are used by every database administrator and user to manipulate and access the data in database tables and views.
Reserved words and characters are utilised to conduct arithmetic calculations, logical operations, comparison operations, compound operations, and other data manipulation and retrieval operations.

Operators are SQL reserved words and characters that are used with a WHERE clause in a SQL query. An operator in SQL can be either a unary or binary operator. The unary operator performs the unary operation with only one operand, whereas the binary operator does the binary operation with two operands.

Syntax of Unary SQL Operator

SQL_Operator Operand  

Syntax of Binary SQL Operator

Operand1 SQL_Operator Operand2  

What is the Precedence of SQL Operator?

The order in which SQL evaluates the different operators in the same expression is known as SQL operator precedence. The operators with the highest precedence are evaluated first in Structured Query Language.

The operators at the top of the table have a high precedence, whereas the operators at the bottom have a low precedence.

Operator Symbols	Operator Name
**	It denotes the Exponentiation operator
+, -	It denotes the Identity operator, Negation operator
*, /	It denotes the Multiplication operator, Division operator
+, -, ||	It denotes the Addition (plus) operator, subtraction (minus) operator, String Concatenation operator
=, !=, <, >, <=, >=, IS NULL, LIKE, BETWEEN, IN	It denotes the Comparison Operators
NOT	It denotes the Logical negation operator
&& or AND	It denotes the Conjunction operator
OR	It denotes the Inclusion operator

Example :

UPDATE person_info  
SET salary = 20 - 3 * 5 WHERE Person_Id = 10;  

Because the * (Multiplication) Operator takes precedence over the - (Subtraction) operator in the following SQL example, salary is allocated 10, not 85, because it is multiplied with 3*5 first and then subtracted from 20.

Types of SQL Operator?

The following are the different types of SQL operators:

🚀 SQL Arithmetic Operators
🚀 SQL Comparison Operators
🚀 SQL Logical Operators

SQL Arithmetic Operators

Let's take that variable x = 30 and y = 10

Operator	Description	Example
+ (Addition)	Values are added on both sides of the operator.	x+y will gives 40 as a result
- (Subtraction)	Takes the right hand operand and subtracts it from the left hand operand.	x-y will gives 20 as a result
* (Multiplication)	Values on both sides of the operator are multiplied.	x*y will gives 300 as a result
/ (Division)	Divides the left and right hand operands.	x/y will gives 3 as a result
% (Modulus)	Returns the remainder after dividing the left hand operand by the right hand operand.	x%y will gives 0 as a result

SQL Comparison Operators

Let's take that variable x = 30 and y = 10

Operator	Description	Example
=	Return true if both operands are equal	x=y is not true
!=	Return true if both operands are not equal	x!=y is true
<>	Return true if both operands are not equal	x<>y is true
>	Return true if left operand is greater than right operand	x>y is true
<	Return true if left operand is lesser than right operand	x<y is not true
>=	Return true if left operand is greater or equal to right operand	x>=y is true
<=	Return true if left operand is lesser or equal to right operand.	x<=y is not true
!<	Return true if left operand is not lesser than right operand.	x!<y is true
!>	Return true if left operand is not greater than right operand..	x!>y is not true

SQL Logical Operators

Operator	Description
ALL	The ALL operator is used to compare a value to all of the values in another value set.
AND	The AND operator allows many conditions to exist in the WHERE clause of a SQL query.
ANY	The ANY operator compares a value to any valid value in the list according to the criteria.
BETWEEN	The BETWEEN operator is used to find values that are between the minimum and maximum values in a set of values.
EXISTS	The EXISTS operator is used to look for a row in a specified table that meets a certain set of criteria.
IN	When a value is compared to a list of literal values that have been supplied, the IN operator is utilised.
LIKE	The LIKE operator compares a value to other values that are comparable using wildcard operators.
NOT	The NOT operator reverses the meaning of the logical operator with which it is associated. NOT EXISTS, NOT BETWEEN, NOT IN, and so on are examples. It's a negation operator, as the name implies.
OR	The OR operator is used in the WHERE clause of a SQL query to combine multiple conditions.
IS NULL	When a value is compared to a NULL value, the NULL operator is employed.
UNIQUE	The UNIQUE operator looks for uniqueness in every row of a table (no duplicates).

With this article at Logicmojo, you must have the complete idea of SQL Operators.