SQL Operators

This article provides a list of SQL operators, and can therefore be used as cheat sheet for SQL operators.

Not all of these operators are supported across all DBMSs. If in doubt, check the documentation for your specific DBMS.

SQL Arithmetic Operators

Operator	Description
`+`	Addition operator. Adds two numbers. This operator can also add a number, in days, from a date (may depend on your DBMS).
`-`	Subtraction operator. Subtracts two numbers. This operator can also subtract a number, in days, from a date (may depend on your DBMS).
`*`	Multiplication operator. Multiplies two expressions.
`/`	Division operator. Divides one number by another.
`%`	Modulus operator. Returns the remainder of one number divided by another.

SQL Bitwise Operators

Operator	Description
`&`	Bitwise AND operator. Performs a bitwise logical AND operation between two integer values.
`\|`	Bitwise OR operator. Performs a bitwise logical OR operation between two specified integer values as translated to binary expressions within SQL statements.
`#`	Bitwise Exclusive OR (XOR) operator.
`^`	Bitwise Exclusive OR (XOR) operator. Performs a bitwise exclusive OR operation between two integer values.
`~`	Bitwise NOT operator. Performs a bitwise logical NOT operation on an integer value.
`<<`	Bitwise Shift Left operator. Shifts the bits in the left operand to the left by the amount specified by the right operand.
`>>`	Bitwise Shift Right operator. Shifts the bits in the left operand to the right by the amount specified by the right operand.

SQL Comparsion Operators

Operator	Description
`=`	Equals operator. Compares the equality of two expressions.
`>`	Greater Than operator. Compares two expressions and returns TRUE if the left operand has a value higher than the right operand; otherwise, the result is FALSE.
`<`	Less Than operator. Compares two expressions and returns TRUE if the left operand has a value lower than the right operand; otherwise, the result is FALSE.
`>=`	Greater Than or Equal To operator. Compares two expressions and returns TRUE if the left operand has a value greater than or equal to the right operand; otherwise, it returns FALSE.
`<=`	Less Than or Equal To operator. Compares two expressions and returns TRUE if the left operand has a value lower than or equal to the right operand; otherwise, it returns FALSE.
`<>`	Not Equal To operator. Compares two expressions and returns TRUE if the left operand is not equal to the right operand; otherwise, the result is FALSE.
`!<`	Not Less Than operator. Compares two expressions and returns TRUE if the left operand does not have a value lower than the right operand; otherwise, the result is FALSE.
`!=`	Not Equal To operator. Compares two expressions and returns TRUE if the left operand is not equal to the right operand; otherwise, the result is FALSE.
`!>`	Not Greater Than operator. Compares two expressions and returns TRUE if the left operand doesn’t have a greater value than the right operand; otherwise, the result is FALSE.

SQL Compound Operators

Operator	Description
`+=`	Add Assignment operator. Adds two numbers and sets a value to the result of the operation.
`-=`	Subtraction Assignment operator. Subtracts two numbers and sets a value to the result of the operation.
`*=`	Multiplication Assignment operator. Multiplies two numbers and sets a value to the result of the operation.
`/=`	Division Assignment operator. Divides one number by another and sets a value to the result of the operation.
`%=`	Modulus Assignment operator. Divides one number by another and sets a value to the result of the operation.
`&=`	Bitwise AND Assignment operator. Performs a bitwise logical AND operation between two integer values, and sets a value to the result of the operation.
`^=`	Bitwise Exclusive OR (XOR) Assignment operator. Performs a bitwise exclusive OR operation between two integer values, and sets a value to the result of the operation.
`\|=`	Bitwise OR Assignment operator. Performs a bitwise logical OR operation between two specified integer values as translated to binary expressions within SQL statements, and sets a value to the result of the operation.

SQL Logical Operators

Operator	Description
`ALL`	Returns TRUE when all of the subquery values meet the condition.
`AND`	Combines two Boolean expressions and returns TRUE when both expressions are TRUE.
`ANY`	Returns TRUE when any of the subquery values meet the condition.
`BETWEEN`	Specifies a range to test. Returns TRUE when the operand is within the range of comparisons.
`EXISTS`	Specifies a subquery to test for the existence of rows. Returns TRUE when the subquery returns one or more records.
`IN`	Determines whether a specified value matches any value in a subquery or a list. Returns TRUE when the operand is equal to one of a list of expressions.
`LIKE`	Determines whether a specific character string matches a specified pattern. Returns TRUE when the operand matches a pattern.
`NOT`	Negates a Boolean input (it reverses the value of any Boolean expression). It therefore returns TRUE when the expression is FALSE. In MariaDB, `NOT` is limited to negating the `IN`, `BETWEEN`, and `EXISTS` clauses.
`OR`	Combines two conditions. Returns TRUE when either of the conditions is TRUE.
`SOME`	Same as `ANY`. Returns TRUE when any of the subquery values meet the condition.

SQL Set Operators

Operator	Description
`EXCEPT`	Returns distinct rows by comparing the results of two queries. Returns distinct rows from the left input query that aren’t output by the right input query.
`INTERSECT`	Returns distinct rows by comparing the results of two queries. Returns distinct rows that are output by both the left and right input queries operator.
`INTERSECT ALL`	Returns distinct rows by comparing the results of two queries. Returns distinct rows that are output by both the left and right input queries operator.
`UNION`	Concatenates the results of two queries into a single result set, excluding duplicates.
`UNION ALL`	Concatenates the results of two queries into a single result set, including duplicates.
`MINUS`	Returns distinct rows by comparing the results of two queries. Returns distinct rows from the left input query that aren’t output by the right input query.

SQL String Operators

Operator	Description
`+`	String Concatenation operator (SQL Server). String concatenation is the operation of joining character strings end-to-end.
`\|\|`	String Concatenation operator (DB2, Oracle, PostgreSQL, SQLite). String concatenation is the operation of joining character strings end-to-end.
`+=`	String Concatenation Assignment operator.
`%`	Percent Wildcard character/s to match. Matches any string of zero or more characters. This wildcard character can be used as either a prefix or a suffix, and it can also be used in the middle.
`[]`	Wildcard character/s to match. Matches any single character within the specified range or set that is specified between brackets `[ ]`. These wildcard characters can be used in string comparisons that involve pattern matching, such as `LIKE`. Supported in SQL Server. Not supported in MySQL, Oracle, DB2, and SQLite.
`[^]`	Wildcard character/s not to match. Matches any single character that is not within the range or set specified between the square brackets `[^]`. These wildcard characters can be used in string comparisons that involve pattern matching, such as `LIKE`.
`_`	Wildcard character – match one character. Matches any single character in a string comparison operation that involves pattern matching, such as `LIKE`. Not supported in DB2.

SQL Unary Operators

Operator	Description
`+`	Positive unary operator. Numeric value is positive.
`-`	Negative unary operator. Numeric value is negative.
`~`	Bitwise NOT operator. Returns the ones complement of the number.
`PRIOR`	Oracle proprietary operator. Evaluates the following expression for the parent row of the current row in a hierarchical, or tree-structured query. In such a query, you must use this operator in the `CONNECT BY` clause to define the relationship between the parent and child rows.

Other SQL Operators

Operator	Description
`(+)`	Outer join operator (Oracle proprietary operator). Indicates that the preceding column is the outer join column in a join.
`::`	Scope Resolution operator. Provides access to static members of a compound data type. A compound data type is one that contains multiple simple data types and methods.

What are SQL Operators?

SQL operators are symbols that specify an action that is performed on one or more expressions.

SQL operators manipulate individual data items and return a result. The data items are called operands or arguments. SQL operators are represented by special characters or by keywords.

Here’s a simple example:

SELECT PetId FROM Pets
WHERE PetName = 'Fluffy'
AND DOB > '2020-01-01';

There are three operators in this SQL SELECT statement.

The Equals operator (=) compares the equality of two expressions (the value of the PetName column and the string Fluffy)
The Greater Than operator (>) compares two expressions and returns TRUE if the left operand has a value higher than the right operand; otherwise, the result is FALSE. In this case, it’s comparing the value of the DOB column and the date 2020-01-01 to see if that date is greater than the value of the DOB column.
The AND operator combines two Boolean expressions and returns TRUE when both expressions are TRUE. In this case, it returns TRUE when PetName = 'Fluffy' and DOB > '2020-01-01'.

The operators listed on this page can be used to construct complex SQL queries that can help you return the data you need.

For more examples, see 12 Commonly Used SQL Operators.