Relational operators

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 <=)

Examples

Copy
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')

SELECT *
   FROM Sales_Data WHERE EXISTS (SELECT Sales_Data.Amount
   FROM Sales_Data WHERE Sales_Data.Salesperson_ID IS NOT NULL)

SELECT *
   FROM Sales_Data WHERE Sales_Data.Amount = ANY (SELECT Sales_Data.Amount
   FROM Sales_Data WHERE Sales_Data.Salesperson_ID = 'SP-1')

SELECT *
   FROM Sales_Data WHERE Sales_Data.Amount = ALL (SELECT Sales_Data.Amount
   FROM Sales_Data WHERE Sales_Data.Salesperson_ID IS NULL)