ODBC and JDBC Guide

Chapter 7
|
Supported standards 49
Relational operators
Examples
SELECT Sales_Data.Invoice_ID FROM Sales_Data
WHERE Sales_Data.Salesperson_ID = 'SP-1'
SELECT Sales_Data.Amount FROM Sales_Data WHERE Sales_Data.Invoice_ID <> 125
SELECT Sales_Data.Amount FROM Sales_Data WHERE Sales_Data.Amount > 3000
SELECT Sales_Data.Time_Sold FROM Sales_Data
WHERE Sales_Data.Time_Sold < '12:00:00'
SELECT Sales_Data.Company_Name FROM Sales_Data
WHERE Sales_Data.Company_Name LIKE '%University'
SELECT Sales_Data.Company_Name FROM Sales_Data
WHERE Sales_Data.Company_Name NOT LIKE '%University'
SELECT Sales_Data.Amount FROM Sales_Data WHERE Sales_Data.Amount IS NULL
SELECT Sales_Data.Amount FROM Sales_Data WHERE Sales_Data.Amount IS NOT NULL
SELECT Sales_Data.Invoice_ID FROM Sales_Data
WHERE Sales_Data.Invoice_ID BETWEEN 1 AND 10
SELECT COUNT(Sales_Data.Invoice_ID) AS agg
FROM Sales_Data WHERE Sales_Data.INVOICE_ID IN (50,250,100)
SELECT COUNT(Sales_Data.Invoice_ID) AS agg
FROM Sales_Data WHERE Sales_Data.INVOICE_ID NOT IN (50,250,100)
SELECT COUNT(Sales_Data.Invoice_ID) AS agg FROM Sales_Data
WHERE Sales_Data.INVOICE_ID NOT IN (SELECT Sales_Data.Invoice_ID
FROM Sales_Data WHERE Sales_Data.Salesperson_ID = 'SP-4')
Operator Meaning
= Equal
<> Not equal
> Greater than
>= Greater than or equal to
< Less than
<= Less than or equal to
LIKE Matching a pattern
NOT LIKE Not matching a pattern
IS NULL Equal to Null
IS NOT NULL Not equal to Null
BETWEEN Range of values between a lower and upper bound
IN A member of a set of specified values or a member of a subquery
NOT IN Not a member of a set of specified values or a member of a subquery
EXISTS ‘True’ if a subquery returned at least one record
ANY Compares a value to each value returned by a subquery (operator must be preceded by =, <>, >, >=,
<, or <=); =Any is equivalent to In
ALL Compares a value to each value returned by a subquery (operator must be preceded by =, <>, >, >=,
<, or <=)