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. <\/p>\n
I will exploring the Logical Operators<\/a> today. In short, these operators return either a boolean TRUE or FALSE.<\/p>\n 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.<\/p>\n Any complex real world programming example probably has one or more of these operators in the TSQL script.<\/p>\n The output of the SELECT statement showing the prime numbers.<\/p>\n The SOME<\/a> operator compares a scalar value with a single column set of values. It returns TRUE when one or more conditions are satisfied.<\/p>\n The ANY<\/a> operator is equivalent to the SOME operator. It returns FALSE when all of the conditions are not satisfied.<\/p>\n The ALL<\/a> operator compares a scalar value with a single column set of values. It returns TRUE when all of the conditions are satisfied.<\/p>\n The BETWEEN<\/a> 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.<\/p>\n The logical AND<\/a> 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.<\/p>\n The logical OR<\/a> 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.<\/p>\n The LIKE<\/a> 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.<\/p>\n The IN<\/a> 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.<\/p>\n The NOT<\/a> 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.<\/p>\n Last but not least, the EXISTS<\/a> operator finds out if the sub-query contains at least one row. The following example checks to see if the Adventure Works<\/a> database for 2012 is installed.<\/p>\n\r\n--\r\n-- ~ First 8 prime numbers ~\r\n--\r\n\r\n-- Drop existing table\r\nIF EXISTS \r\n(\r\n SELECT * \r\n FROM tempdb.sys.objects T \r\n WHERE T.TYPE = 'U' AND T.name LIKE '#PRIMES%'\r\n) \r\nDROP TABLE #PRIMES;\r\nGO\r\n\r\n-- Make temp table\r\nCREATE TABLE #PRIMES (NUM INT PRIMARY KEY);\r\nGO\r\n\r\n-- Add data to table\r\nINSERT INTO #PRIMES VALUES \r\n (2), (3), (5), (7),\r\n (11), (13), (17), (19);\r\n\r\n-- Show the data\r\nSELECT * FROM #PRIMES;\r\n<\/pre>\n<\/p>\n
\r\nNUM\r\n-----------\r\n2\r\n3\r\n5\r\n7\r\n11\r\n13\r\n17\r\n19\r\n<\/pre>\n<\/p>\n
\r\nPRINT '13 LESS THAN [SOME] OF THE FIRST 8 PRIMES?' \r\nIF 13 < SOME (SELECT NUM FROM #PRIMES)\r\n PRINT 'TRUE' \r\nELSE\r\n PRINT 'FALSE' ;\r\nPRINT '';\r\n<\/pre>\n<\/p>\n
\r\n13 LESS THAN [SOME] OF THE FIRST 8 PRIMES?\r\nTRUE\r\n<\/pre>\n<\/p>\n
\r\nPRINT '23 LESS THAN [ANY] OF THE FIRST 8 PRIMES?' \r\nIF 23 < ANY (SELECT NUM FROM #PRIMES)\r\n PRINT 'TRUE' \r\nELSE\r\n PRINT 'FALSE' ;\r\nPRINT '';\r\n<\/pre>\n<\/p>\n
\r\n23 LESS THAN [ANY] OF THE FIRST 8 PRIMES?\r\nFALSE\r\n<\/pre>\n<\/p>\n
\r\nPRINT '3 LESS THAN [ALL] OF THE FIRST 8 PRIMES?' \r\nIF 3 < ALL (SELECT NUM FROM #PRIMES)\r\n PRINT 'TRUE' \r\nELSE\r\n PRINT 'FALSE' ;\r\nPRINT '';\r\n<\/pre>\n<\/p>\n
\r\n3 LESS THAN [ALL] OF THE FIRST 8 PRIMES?\r\nFALSE\r\n<\/pre>\n<\/p>\n
\r\nPRINT 'AVERAGE OF FIRST 8 PRIMES IS [BETWEEN] 8 AND 10' \r\nIF (SELECT AVG(NUM) FROM #PRIMES) BETWEEN 8 AND 10 \r\n PRINT 'TRUE' \r\nELSE\r\n PRINT 'FALSE' ;\r\nPRINT '';\r\n<\/pre>\n<\/p>\n
\r\nAVERAGE OF FIRST 8 PRIMES IS [BETWEEN] 8 AND 10\r\nTRUE\r\n<\/pre>\n<\/p>\n
\r\nPRINT 'TWO GREATER THAN ONE [AND] FOUR GREATER THAN THREE' \r\nIF (2 > 1) AND (4 > 3)\r\n PRINT 'TRUE' \r\nELSE\r\n PRINT 'FALSE' ;\r\nPRINT '';\r\n<\/pre>\n<\/p>\n
\r\nTWO GREATER THAN ONE [AND] FOUR GREATER THAN THREE\r\nTRUE\r\n<\/pre>\n<\/p>\n
\r\nPRINT 'TWO GREATER THAN ONE [OR] FOUR LESS THAN THREE' \r\nIF (2 > 1) OR (4 < 3)\r\n PRINT 'TRUE' \r\nELSE\r\n PRINT 'FALSE' ;\r\nPRINT '';\r\n<\/pre>\n<\/p>\n
\r\nTWO GREATER THAN ONE [OR] FOUR LESS THAN THREE\r\nTRUE\r\n<\/pre>\n<\/p>\n
\r\nSELECT name as dbms_name, database_id as dbms_id \r\nFROM master.sys.databases\r\nWHERE name LIKE 'master%';\r\n<\/pre>\n<\/p>\n
\r\ndbms_name dbms_id\r\n---------- -----------\r\nmaster 1\r\n<\/pre>\n<\/p>\n
\r\nSELECT name as dbms_name, database_id as dbms_id \r\nFROM master.sys.databases\r\nWHERE name IN ('tempdb', 'model', 'master', 'msdb')\r\nORDER BY database_id;\r\n<\/pre>\n<\/p>\n\r\ndbms_name dbms_id\r\n---------- -----------\r\nmaster 1\r\ntempdb 2\r\nmodel 3\r\nmsdb 4\r\n<\/pre>\n<\/p>\n
\r\nSELECT TOP 10 name as dbms_name, database_id as dbms_id \r\nFROM master.sys.databases\r\nWHERE name NOT LIKE 'master%' \r\nORDER BY database_id;\r\n<\/pre>\n<\/p>\n
\r\ndbms_name dbms_id\r\n-------------------- -----------\r\ntempdb 2\r\nmodel 3\r\nmsdb 4\r\nReportServer 5\r\nReportServerTempDB 6\r\nMATH 7\r\nAdventureWorksDW2012 8\r\nAUTOS 9\r\nAdventureWorks2012 10\r\nBASIC 11\r\n<\/pre>\n<\/p>\n
\r\nIF EXISTS (SELECT 1 FROM master.sys.databases WHERE name = 'AdventureWorks2012')\r\n PRINT 'TRUE - ADVENTURE WORKS IS INSTALLED' \r\nELSE\r\n PRINT 'FALSE - ADVENTURE WORKS IS INSTALLED' ;\r\nPRINT '';\r\n<\/pre>\n<\/p>\n
\r\nTRUE - ADVENTURE WORKS IS INSTALLED\r\n<\/pre>\n<\/p>\n