SQL Logical and Compound Operators

Logical and compound operators enable you to modify or combine the conditions within a SQL expression. For example, to create a dynamic group that identifies all PCs where Microsoft Excel is installed, enter the following condition:

SELECT * FROM NODES, PKGRPTDB WHERE PKGRPTDB.NODENAME = NODES.NODENAME AND PKGRPTDB.PACKAGE LIKE 'Excel%'

If you then apply the NOT operator to the expression, you can identify that PCs do not have Excel and use the Distribute Software operation to install it:

SELECT * FROM NODES, PKGRPTDB WHERE PKGRPTDB.NODENAME = NODES.NODENAME AND (NOT PKGRPTDB.PACKAGE LIKE 'Excel%')

Operator

Description

AND

Joins two or more conditions, and displays a row only if that row's data satisfies ALL specified conditions.

OR

Joins two or more conditions, but returns a row if any of the conditions listed hold true.

IN

Simplifies creating compound statements by enabling you to create a list of possible matches. For example:

SELECT * FROM NODES WHERE DOMAINNAME IN ('ENGINEERING', 'SALES')

BETWEEN

Enables you to specify the outer values of a range. For example, to find all Clients which have a department beginning with a letter in the first half of the alphabet (in other words, between a and n):

SELECT * FROM NODES WHERE DEPT BETWEEN 'a%' AND 'n%'

NOT

Usually positioned before a Operator such as IN, BETWEEN or LIKE, this can be used to exclude, rather than include, a particular filter. For example, to select all Clients except for those in the Accounts department:

SELECT * FROM NODES WHERE DEPT NOT LIKE 'Accounts%

 

When AND and OR operators are combined in an expression, SQL evaluates the ANDs before the ORs. If you are not sure about the order in which an expression will be evaluated, use brackets to enclose the parts of the parts of the expression that you want to evaluate first.