MRRisley:=("SQL Operators")
Note: This guide is meant to be a companion to Itzik Ben-Gan's T-SQL Fundamentals, 3rd edition.
Back to SQL Main Page
You can find the Microsoft Doc for T-SQL operators here.
1. Mathematical Operators
Mathematical operators allow you to perform simple mathematical procedures such as addition and multiplication.
| Operation | Operator |
|---|---|
| Addition | + |
| Subtraction | - |
| Multiplication | * |
| Division | / |
2. Comparison Operators
Comparison operators allow you to compare values. These operators evaluate to True, False, or Unknown. All of these operators work with number and date data. String data can be evaluated with these operators as well, but equivalence is usually the only relevant operator for strings.
| Operation | Operator |
|---|---|
| equal to | = |
| not equal to | <> or != |
| greater than | > |
| greater than or equal to | >= |
| not greater than | !> |
| less than | < |
| less than or equal to | <= |
| not less than | !< |
3. Logic Operators
Logic operators assist in the evaluation of logical conditions, usually in the WHERE clause. The following logic operators are commonly used.
AND
The AND logic operator allows you to evaluate whether two or more conditions are true. The evaluation of conditions with the AND
operator require all conditions to be satisfied.
select businessentityid, persontype, title
from [AdventureWorks].[Person].[Person]
where persontype = 'EM' and title = 'Mr.'
businessentityid persontype title ---------------- ---------- -------- 6 EM Mr. 139 EM Mr. 273 EM Mr. (3 row(s) affected)
Results are only returned when the persontype field is equal to 'EM' and the title field is equal to 'Mr.'.
OR
The OR logic operator allows you to evaluate whether at least one condition is true. The evaluation of conditions with the OR
operator require at least one condition to be satisfied.
select businessentityid, persontype, title
from [AdventureWorks].[Person].[Person]
where persontype = 'EM' or title = 'Mr.'
businessentityid persontype title ---------------- ---------- -------- 1 EM NULL 2 EM NULL 3 EM NULL 4 EM NULL 5 EM Ms. 6 EM Mr. 7 EM NULL 8 EM NULL 9 EM NULL 10 EM NULL ... 273 EM Mr. 284 SP Mr. 285 SP Mr. 291 SC Mr. 307 SC Mr. ... (847 row(s) affected)
Results are returned when at least either the persontype field is equal to 'EM' or the title field is equal to 'Mr.'. Notice that both
can be true. The OR operator essentially specifies that all joined conditions cannot evaluate to False. Some can.
BETWEEN
The BETWEEN logic operator allows you to evaluate whether a value falls within an interval, inclusive.
select modifieddate, businessentityid
from [AdventureWorks].[Person].[Person]
where modifieddate between '12/15/2013' and '12/31/2013'
order by modifieddate
modifieddate businessentityid ----------------------- ---------------- 2013-12-15 00:00:00.000 2554 2013-12-15 00:00:00.000 3680 2013-12-15 00:00:00.000 3914 ... 2013-12-23 00:00:00.000 2933 2013-12-23 00:00:00.000 2455 2013-12-23 00:00:00.000 1728 ... 2013-12-31 00:00:00.000 18444 2013-12-31 00:00:00.000 19638 2013-12-31 00:00:00.000 19643 (576 row(s) affected)
Results are returned when the modifieddate field is greater than or equal to December 15, 2013, and less than or equal to December 31, 2013.
NOT
The NOT logic operator allows you to negate a logical condition. If evaluation of the condition would return True, the addition of the NOT
operator would return False. The converse also occurs.
select modifieddate, businessentityid
from [AdventureWorks].[Person].[Person]
where modifieddate not between '12/15/2013' and '12/31/2013'
order by modifieddate
modifieddate businessentityid ----------------------- ---------------- 2006-06-23 00:00:00.000 28 2006-06-23 00:00:00.000 2298 2007-01-19 00:00:00.000 2119 ... 2013-12-14 00:00:00.000 16503 2013-12-14 00:00:00.000 16437 2013-12-14 00:00:00.000 16834 2014-01-01 00:00:00.000 17110 2014-01-01 00:00:00.000 16213 2014-01-01 00:00:00.000 16646 ... 2015-04-15 16:33:33.107 305 2015-04-15 16:33:33.123 307 2015-04-15 16:33:33.123 309 (19396 row(s) affected)
Results are returned when the modifieddate field is NOT greater than or equal to December 15, 2013, and less than or equal to December 31, 2013. This
implies that results should be less than December 15, 2013, or greater than December 31, 2013.
IN
The IN logic operator allows you to specify a list of possible values for a logical condition. If the checked value is in the supplied list, then the
logical condition evaluates to True.
select modifieddate, businessentityid
from [AdventureWorks].[Person].[Person]
where modifieddate in
('10/1/2010', '10/1/2011', '10/1/2012')
order by modifieddate
modifieddate businessentityid ----------------------- ---------------- 2011-10-01 00:00:00.000 417 2011-10-01 00:00:00.000 451 2011-10-01 00:00:00.000 453 2011-10-01 00:00:00.000 523 2011-10-01 00:00:00.000 527 2011-10-01 00:00:00.000 535 2011-10-01 00:00:00.000 585 2011-10-01 00:00:00.000 713 2011-10-01 00:00:00.000 839 2011-10-01 00:00:00.000 897 2011-10-01 00:00:00.000 959 2011-10-01 00:00:00.000 1279 2011-10-01 00:00:00.000 1453 2011-10-01 00:00:00.000 1455 2011-10-01 00:00:00.000 1457 2011-10-01 00:00:00.000 1477 2011-10-01 00:00:00.000 1863 2011-10-01 00:00:00.000 2009 2011-10-01 00:00:00.000 4781 2011-10-01 00:00:00.000 5743 2011-10-01 00:00:00.000 8954 2011-10-01 00:00:00.000 17865 2011-10-01 00:00:00.000 18247 2011-10-01 00:00:00.000 20657 2012-10-01 00:00:00.000 18937 2012-10-01 00:00:00.000 13540 2012-10-01 00:00:00.000 14326 2012-10-01 00:00:00.000 17172 (28 row(s) affected)
Results are returned when the modifieddate field equals any of the dates in the list, which are October 1 for the years 2010, 2011, and 2012.
LIKE
The LIKE logic operator allows you to specify a pattern in order to evaluate a logical condition. This operator is useful
for certain sub-strings within a larger string value. This is equivalent to wildcard searches.
The Microsoft Doc describes how the wildcard searches work. I will demonstrate the % and _ wildcard operators.
The following query uses the _ wildcard search.
Results are returned when the firstname field has a four-character value with 'ean' as the second to fourth characters.
select firstname, count(*) 'count'
from [AdventureWorks].[Person].[Person]
where firstname like '_ean'
group by firstname
order by firstname
firstname count -------------------------------------------------- ----------- Jean 8 Sean 53 (2 row(s) affected)
The following query uses the % wildcard search. Results are returned when the firstname field contains 'ean'.
select firstname, count(*) 'count'
from [AdventureWorks].[Person].[Person]
where firstname like '%ean%'
group by firstname
order by firstname
firstname count -------------------------------------------------- ----------- Deanna 48 Denean 1 Jean 8 Jeanette 1 Jeanie 1 Sean 53 Teanna 1 (7 row(s) affected)