SQL - Operators

SQL operators are found in just about every SQL query. Operators are the mathematical and equality symbols used to compare, evaluate, or calculate values. Equality operators include the (<), (>), and (=) symbols, which are used to compare one value against another. Each of these characters have special meaning, and when SQL comes across them, they help tell SQL how to evaluate an expression or conditional statement. Most operators will appear inside of conditional statements in the WHERE clause of SQL Commands.


Operators come in three flavors: mathematical, logical, and equality. Mathematical operators add, subtract, multiply, and divide numbers. Logical operators include AND and OR. Take note of the following tables for future reference.
SQL operators are generally found inside of queries-- more specifically, in the conditional statements of the WHERE clause.

SQL Equality Operator Query:

USE mydatabase;

SELECT customer,day_of_order
FROM orders
WHERE  day_of_order > '7/31/08'

Sql Equality Operator:

customer	day_of_order
Tizag	2008-08-01 00:00:00.000
Tizag	2008-08-01 00:00:00.000

In this case, we've used the equality operator greater than (>) to return orders from the orders table with a date greater than '7/31/08'.

SQL - Equality Operator Table

Equality involves comparing two values. To do so requires the use of the (<), (>), or (=) special characters. Does X = Y? Is Y < X? These are both questions that can be answered using a SQL Equality Operator expression.

SQL Equality Operators:

Operator	Example	Defined	Result
=, IS	5 = 5	5 equal to 5?	True
!=, IS NOT	7 != 2	7 IS NOT (!=) equal to 2?	True
<	7 < 4	7 less than 4?	False
>	7 > 4	greater than 4?	True
<=	7 <= 11	Is 7 less than or equal to 11?	True
>=	7 >= 11	Is 7 greater than or equal to 11?	False

SQL - Mathematical Operators

SQL mathematical operations are performed using mathematical operators (+, -, *, /, and %). We can use SQL like a calculator to get a feel for how these operators work.

SQL Mathematical Operators:

SELECT 
15 + 4, --Addition
15 - 4, --Subtraction
15 * 4, --Multiplication
15 / 5, -- Division
15 % 4; --Modulus

SQL Results:

Addition	Subtraction	Multiplication	Division	Modulus
19	11	60	3	3

Modulus may be the only unfamiliar term on the chart. Modulus performs division, dividing the first digit by the second digit, but instead of returning a quotient, a "remainder" value is returned instead.

Modulus Example:

USE mydatabase;

SELECT (5 / 2) -- = 2.5
SELECT (5 % 2) -- = 1 is the value that will be returned

SQL - Logical Operators

These operators provide you with a way to specify exactly what you want SQL to go and fetch, and you may be as specific as you'd like! We'll discuss these a little later on and provide some real world scenarios as well.
We cover these operators thoroughly in the SQL AND/OR lesson.

• AND - Compares/Associates two values or expressions
• OR - Compares/Associates two values or expressions

Posted in: SQL