Conditions

Simple Conditions

Operators


-- equals, is the same as
WHERE quantity = 15;

-- is not equal to, is not the same as
WHERE quantity != 15;

-- is less than
WHERE quantity < 15

-- is less than or equal to
WHERE quantity <= 15

-- is greater than
WHERE quantity > 15

-- is greater than or equal to
WHERE quantity >= 15

-- is between two numbers (inclusive)
WHERE quantity>=10 AND quantity <=15
WHERE quantity BETWEEN 10 AND 15

-- is not between two numbers (inclusive) 
WHERE quantity < 10 OR quantity > 15 
 
-- is in a list of values
WHERE colour='black' OR colour='gold' OR colour='silver' OR colour='white'
WHERE colour IN ['black', 'gold', 'silver', 'white']

LIKE Operator

The “=” operator uses an exact match, including capital/lowercase letters.

SELECT *
FROM staff
WHERE surname = 'MacDonald'

The “LIKE” operator allows matches that ignore any capitalisation.

SELECT * 
FROM staff 
WHERE surname LIKE 'macdonald'

Complex Conditions

Each condition must be in full. You cannot write:

WHERE forename = 'John' OR 'Jon'

AND, OR, NOT

-- and - all conditions must be met
WHERE surname ='MacDonald' 
    AND forename = 'John'

-- or - any of the conditions must be met
WHERE quantity = 10 
    OR quantity = 20

-- not - the condition must not be met
WHERE not (surname = "MacDonald")