SQL Language Operators

SQL operators are symbols or keywords that are used to perform operations on one or more values. These values can be columns, variables, or literals. Operators are fundamental to building complex queries and performing calculations, comparisons, and logical operations within your SQL statements.

Arithmetic Operators

Used to perform mathematical calculations.

Addition +

Adds two values.

Subtraction -

Subtracts one value from another.

Multiplication *

Multiplies two values.

Division /

Divides one value by another.

Modulo %

Returns the remainder of a division.

Comparison Operators

Used to compare two values. These operators return TRUE, FALSE, or UNKNOWN.

Equal to =

Checks if two values are equal.

Not equal to <> or !=

Checks if two values are not equal.

Greater than >

Checks if the left value is greater than the right value.

Less than <

Checks if the left value is less than the right value.

Greater than or equal to >=

Checks if the left value is greater than or equal to the right value.

Less than or equal to <=

Checks if the left value is less than or equal to the right value.

Logical Operators

Used to combine conditional statements or test for true.

AND AND

Returns TRUE if both conditions are true.

OR OR

Returns TRUE if at least one condition is true.

NOT NOT

Reverses the logical state of its operand.

Bitwise Operators

Used to perform bitwise operations on integer values.

Bitwise AND &

Performs a bitwise AND operation.

Bitwise OR |

Performs a bitwise OR operation.

Bitwise XOR ^

Performs a bitwise exclusive OR operation.

Bitwise NOT ~

Performs a bitwise NOT operation.

Bitwise Left Shift <<

Shifts bits to the left.

Bitwise Right Shift >>

Shifts bits to the right.

Other Operators

Includes operators for pattern matching, set operations, and more.

LIKE LIKE

Searches for a specified pattern in a column.

IN IN

Allows you to specify multiple values in a WHERE clause.

BETWEEN BETWEEN

Selects values within a given range.

IS NULL IS NULL

Tests for NULL values.

IS NOT NULL IS NOT NULL

Tests for non-NULL values.

ALL ALL

Combines with a comparison operator to retrieve all rows that satisfy the condition.

ANY ANY

Combines with a comparison operator to retrieve rows where the comparison is true for at least one value in the list.

EXISTS EXISTS

Tests for the existence of rows in a subquery.

UNION UNION

Combines the result set of two or more SELECT statements.

UNION ALL UNION ALL

Combines the result set of two or more SELECT statements, including duplicates.

INTERSECT INTERSECT

Returns only rows that exist in both result sets.

EXCEPT EXCEPT

Returns rows from the first SELECT statement that are not in the second.

Operator Precedence

Operators are evaluated in a specific order, known as operator precedence. Operators with higher precedence are evaluated before operators with lower precedence. You can use parentheses () to override the default precedence.


-- Example demonstrating operator precedence
SELECT column1, column2
FROM your_table
WHERE (column1 + column2) * column3 > 100;

Understanding SQL operators is crucial for writing effective and efficient database queries. For detailed syntax and specific implementations in different SQL dialects (e.g., SQL Server, MySQL, PostgreSQL), please refer to the relevant documentation for your database system.