WHERE

It is possible to restrict the results of an AnalytiX-BI query by adding a WHERE clause. The condition of a WHERE clause is a Boolean predicate made from one or more logical comparisons. Comparisons are allowed generically between columns and/or expressions.

The following query retrieves all products that are discontinued by comparing a column to a constant.

SELECT * FROM Products WHERE Discontinued = true

The following query retrieves all products that have to be reordered and are not discontinued, performing a comparison between two columns.

SELECT * FROM Products WHERE UnitsInStock <= ReorderLevel AND Discontinued = false

Predicates can also use generic expressions. The following query retrieves all orders that have been placed in August 1997 by using the year [MT1] and month [MT2] functions.

SELECT * FROM Orders WHERE year(OrderDate) = 1997 AND month(OrderDate) = 8

Below is the list of the supported comparison operators for search predicates.

= (Equals)

Tests equality between two expressions. The following query retrieves all the products that have their CategoryID equal to 8.

SELECT * FROM Categories WHERE CategoryID = 8

<> (Not Equals)

Tests inequality between two expressions. The following query retrieves all the products that have their CategoryID not equal to 3.

SELECT * FROM Categories WHERE CategoryID <> 3

!= (Not Equals)

Tests inequality between two expressions. The following query retrieves all the products that have their CategoryID not equal to 5.

SELECT * FROM Categories WHERE CategoryID != 5

> (Greater Than)

Tests whether the left expression is greater than the right expression. The following query retrieves all products that have a UnitPrice greater than 40.

SELECT * FROM Products WHERE UnitPrice > 40

>= (Greater Than or Equal)

Tests whether the left expression is greater than or equal to the right expression. The following query retrieves all products that have a UnitPrice greater than or equal to 40.

SELECT * FROM Products WHERE UnitPrice >= 40

< (Less Than)

Tests whether the left expression is less than the right expression. The following query retrieves all products that have a UnitPrice less than 40.

SELECT * FROM Products WHERE UnitPrice < 40

<= (Less Than or Equal)

Tests whether the left expression is less than or equal to the right expression. The following query retrieves all products that have a UnitPrice less than or equal to 40.

SELECT * FROM Products WHERE UnitPrice <= 40

[NOT] LIKE

Indicates that the character string on the right is to be used with pattern matching against the left expression.

The % (percent) character can be used as a wildcard to match any string of zero or more characters. The following query retrieves all customers whose ContactName starts with a and ends with o.

SELECT ContactName FROM Customers WHERE ContactName LIKE 'a%o'

The _ (underscore) character can be used as a wildcard to match any single character. The following query retrieves all customers whose CustomerID starts with TRA and ends with H.

SELECT * FROM Customers WHERE CustomerID LIKE 'TRA_H'

Square brackets can be used to match any single character enclosed in the brackets. The following query retrieves all customers whose CustomerID starts with B and where the second letter is either a L or an O.

SELECT * FROM Customers WHERE CustomerID LIKE 'B[LO]%'

The ^ (caret) character can be used with square brackets in order to match any characters not enclosed in the brackets. The following query retrieves all customers whose CustomerID starts with B and has a second letter that is not L or S.

SELECT * FROM Customers WHERE CustomerID LIKE 'B[^LS]%'

Prefixing the LIKE keyword with NOT produces the negation of the result. The following query retrieves all customers whose CustomerID does not start with A.

SELECT * FROM Customers WHERE CustomerID NOT LIKE 'A%'

To match a literal % (percent) or _ (underscore) characters they must be enclosed in square brackets. The following query retrieves all bikes from a hypothetical Bikes table that have their discount set to 30%.

SELECT * FROM Bikes WHERE Discount LIKE '30[%]'

[NOT] BETWEEN

Tests whether the value of an expression is between two other expressions. The following query retrieves all orders places between two specific dates.

SELECT * FROM Orders WHERE OrderDate BETWEEN '1997-09-03' AND '1997-09-05'

Bounds are included in the result. Prefixing the BETWEEN keyword with NOT returns all rows not in the specified interval, not including the bounds. The following query retrieves all orders not placed between two specific dates and made in September 1997.

SELECT * FROM Orders

WHERE OrderDate NOT BETWEEN '1997-09-10' AND '1997-09-20'

AND year(OrderDate) = 1997

AND month(OrderDate) = 9

IS [NOT] NULL

Tests whether the left expression is NULL. The following query retrieves all customers whose fax number is NULL.

SELECT * FROM Customers WHERE Fax IS NULL

When prefixing with the NOT keyword, tests whether the left expression is not NULL.

Note: previous versions of AnalytiX-BI did not support the IS NULL comparison operator, and required to use the equals operator. While the syntax is still supported, it is recommended to use the IS NULL comparison operator when checking for NULLs.

[NOT] IN

[NOT] EXISTS

Used with a subquery to test for existence (or non-existence) of rows returned by the subquery. When testing for existence, the values in the rows returned from the subquery are irrelevant, as EXISTS only needs to check for the existence of returned rows. For this reason, the subqueries written with EXISTS are typically in the form of SELECT * or SELECT 1.

The following query produces the same output as the query in the previous section, using EXISTS instead of IN.

SELECT CompanyName FROM Customers AS c WHERE EXISTS (SELECT 1 FROM Orders WHERE c.CustomerID = CustomerID AND OrderDate BETWEEN '9/1/96' AND '9/30/96')

NOT EXISTS can be used to test for absence of rows returned from the subquery, and is generally preferable to NOT IN. The following query retrieves all customers that have not placed an order.

SELECT CustomerID FROM Customers AS c WHERE NOT EXISTS (SELECT 1 FROM Orders WHERE c.CustomerID = Orders.CustomerID)

It is preferable to use [NOT] EXISTS over [NOT IN] where possible.