I am going to make headway on the series of very short articles or tidbits on Transaction SQL Operators. An operator is a symbol specifying an action that is performed on one or more expressions.
I will exploring the Logical Operators today. In short, these operators return either a boolean TRUE or FALSE.
To demonstrate many of these operators, we need a temporary table to query against. The Transaction SQL code below creates a table that contains the first eight prime numbers.
Any complex real world programming example probably has one or more of these operators in the TSQL script.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
-- -- ~ First 8 prime numbers ~ -- -- Drop existing table IF EXISTS ( SELECT * FROM tempdb.sys.objects T WHERE T.TYPE = 'U' AND T.name LIKE '#PRIMES%' ) DROP TABLE #PRIMES; GO -- Make temp table CREATE TABLE #PRIMES (NUM INT PRIMARY KEY); GO -- Add data to table INSERT INTO #PRIMES VALUES (2), (3), (5), (7), (11), (13), (17), (19); -- Show the data SELECT * FROM #PRIMES; |
The output of the SELECT statement showing the prime numbers.
|
1 2 3 4 5 6 7 8 9 10 |
NUM ----------- 2 3 5 7 11 13 17 19 |
The SOME operator compares a scalar value with a single column set of values. It returns TRUE when one or more conditions are satisfied.
|
1 2 3 4 5 6 |
PRINT '13 LESS THAN [SOME] OF THE FIRST 8 PRIMES?' IF 13 < SOME (SELECT NUM FROM #PRIMES) PRINT 'TRUE' ELSE PRINT 'FALSE' ; PRINT ''; |
|
1 2 |
13 LESS THAN [SOME] OF THE FIRST 8 PRIMES? TRUE |
The ANY operator is equivalent to the SOME operator. It returns FALSE when all of the conditions are not satisfied.
|
1 2 3 4 5 6 |
PRINT '23 LESS THAN [ANY] OF THE FIRST 8 PRIMES?' IF 23 < ANY (SELECT NUM FROM #PRIMES) PRINT 'TRUE' ELSE PRINT 'FALSE' ; PRINT ''; |
|
1 2 |
23 LESS THAN [ANY] OF THE FIRST 8 PRIMES? FALSE |
The ALL operator compares a scalar value with a single column set of values. It returns TRUE when all of the conditions are satisfied.
|
1 2 3 4 5 6 |
PRINT '3 LESS THAN [ALL] OF THE FIRST 8 PRIMES?' IF 3 < ALL (SELECT NUM FROM #PRIMES) PRINT 'TRUE' ELSE PRINT 'FALSE' ; PRINT ''; |
|
1 2 |
3 LESS THAN [ALL] OF THE FIRST 8 PRIMES? FALSE |
The BETWEEN operator compares a scalar against a range of values. It returns TRUE when the scalar is greater than or equal to the lower limit and less than or equal to the upper limit.
|
1 2 3 4 5 6 |
PRINT 'AVERAGE OF FIRST 8 PRIMES IS [BETWEEN] 8 AND 10' IF (SELECT AVG(NUM) FROM #PRIMES) BETWEEN 8 AND 10 PRINT 'TRUE' ELSE PRINT 'FALSE' ; PRINT ''; |
|
1 2 |
AVERAGE OF FIRST 8 PRIMES IS [BETWEEN] 8 AND 10 TRUE |
The logical AND operator combines two BOOLEAN expressions. It returns TRUE if both conditions are true; otherwise, it returns false. The example below uses simple integer expressions to demonstrate this operator.
|
1 2 3 4 5 6 |
PRINT 'TWO GREATER THAN ONE [AND] FOUR GREATER THAN THREE' IF (2 > 1) AND (4 > 3) PRINT 'TRUE' ELSE PRINT 'FALSE' ; PRINT ''; |
|
1 2 |
TWO GREATER THAN ONE [AND] FOUR GREATER THAN THREE TRUE |
The logical OR operator combines two BOOLEAN expressions. It returns TRUE if any conditions are true; otherwise, it returns false. The example below uses simple integer expressions to demonstrate this operator.
|
1 2 3 4 5 6 |
PRINT 'TWO GREATER THAN ONE [OR] FOUR LESS THAN THREE' IF (2 > 1) OR (4 < 3) PRINT 'TRUE' ELSE PRINT 'FALSE' ; PRINT ''; |
|
1 2 |
TWO GREATER THAN ONE [OR] FOUR LESS THAN THREE TRUE |
The LIKE operator determines if a character string matches a specific pattern. The example below queries the sys.databases table and returns the row for the master database.
|
1 2 3 |
SELECT name as dbms_name, database_id as dbms_id FROM master.sys.databases WHERE name LIKE 'master%'; |
|
1 2 3 |
dbms_name dbms_id ---------- ----------- master 1 |
The IN operator ascertains if a value matches any elements in the sub-query or list of elements. The example below queries the sys.databases table and returns a row for each system database.
|
1 2 3 4 |
SELECT name as dbms_name, database_id as dbms_id FROM master.sys.databases WHERE name IN ('tempdb', 'model', 'master', 'msdb') ORDER BY database_id; |
|
1 2 3 4 5 6 |
dbms_name dbms_id ---------- ----------- master 1 tempdb 2 model 3 msdb 4 |
The NOT operator negates the BOOLEAN expression. The example below queries the sys.databases table and returns the first 10 databases by id. It removes the master database by using the NOT LIKE clause.
|
1 2 3 4 |
SELECT TOP 10 name as dbms_name, database_id as dbms_id FROM master.sys.databases WHERE name NOT LIKE 'master%' ORDER BY database_id; |
|
1 2 3 4 5 6 7 8 9 10 11 12 |
dbms_name dbms_id -------------------- ----------- tempdb 2 model 3 msdb 4 ReportServer 5 ReportServerTempDB 6 MATH 7 AdventureWorksDW2012 8 AUTOS 9 AdventureWorks2012 10 BASIC 11 |
Last but not least, the EXISTS operator finds out if the sub-query contains at least one row. The following example checks to see if the Adventure Works database for 2012 is installed.
|
1 2 3 4 5 |
IF EXISTS (SELECT 1 FROM master.sys.databases WHERE name = 'AdventureWorks2012') PRINT 'TRUE - ADVENTURE WORKS IS INSTALLED' ELSE PRINT 'FALSE - ADVENTURE WORKS IS INSTALLED' ; PRINT ''; |
|
1 |
TRUE - ADVENTURE WORKS IS INSTALLED |
Next time, I will be researching the Set Operators.
