SQL Operators

SQL operators are used to specify comparisons between expressions.

You can use various types of SQL operators.

Operator Example Description Syntax

BETWEEN

"COSTS"."UNIT_COST" BETWEEN 100.0 AND 5000.0

Determines if a value is between two non-inclusive bounds.

BETWEEN can be preceded with NOT to negate the condition.

BETWEEN [LowerBound] AND [UpperBound]

IN

"COSTS"."UNIT_COST" IN(200, 600, 'A')

Determines if a value is present in a set of values.

IN ([Comma Separated List])

IS NULL

"PRODUCTS"."PROD_NAME" IS NULL

Determines if a value is null.

IS NULL

LIKE

"PRODUCTS"."PROD_NAME" LIKE 'prod%'

Determines if a value matches all or part of a string. Often used with wildcard characters to indicate any character string match of zero or more characters (%) or any single character match (_).

LIKE

+

(FEDERAL_REVENUE + LOCAL_REVENUE) - TOTAL_EXPENDITURE

Plus sign for addition.

+

-

(FEDERAL_REVENUE + LOCAL_REVENUE) - TOTAL_EXPENDITURE

Minus sign for subtraction.

-

* or X

SUPPORT_SERVICES_EXPENDITURE * 1.5

Multiply sign for multiplication.

*

X

/

CAPITAL_OUTLAY_EXPENDITURE/1.05

Divide by sign for division.

/

%

 

Percentage

%

||

STATE||CAST(YEAR AS CHAR(4))

Character string concatenation.

||

(

(FEDERAL_REVENUE + LOCAL_REVENUE) - TOTAL_EXPENDITURE

Open parenthesis.

(

)

(FEDERAL_REVENUE + LOCAL_REVENUE) - TOTAL_EXPENDITURE

Close parenthesis.

)

>

YEAR > 2000 and YEAR < 2016 and YEAR <> 2013

Greater than sign, indicating values higher than the comparison.

>

<

YEAR > 2000 and YEAR < 2016 and YEAR <> 2013

Less than sign, indicating values lower than the comparison.

<

=

 

Equal sign, indicating the same value.

=

>=

 

Greater than or equal to sign, indicating values the same or higher than the comparison.

>=

<=

 

Less than or equal to sign, indicating values the same or lower than the comparison.

<=

<>

YEAR > 2000 and YEAR < 2016 and YEAR <> 2013

Not equal to, indicating values higher or lower, but different.

<>

,

STATE in ('ALABAMA','CALIFORNIA')

Comma, used to separate elements in a list.

,