AND, OR and NOT

Logical operators used to form predicates into composite search conditions.

• AND - both sides must evaluate to true for the combined result to be true
• OR - either side or both sides must evaluate to true for the combined result to be true
• NOT - reverses the value of true - true becomes false - false becomes true

SELECT capital, area, gdp, cars
FROM country
WHERE area > 200
  AND (gdp < 300 OR cars > 20000)
  AND NOT name = 'Italy';

Using < and >

SELECT DISTINCT student_id, course_code
FROM assignment
WHERE grade > 70
AND course_code <> 'c4';

Means the same as:

SELECT DISTINCT student_id, course_code
FROM assignment
WHERE grade > 70
AND NOT course_code = 'c4';

BETWEEN and IN

BETWEEN is an operator that operates on the range of values of the specified column. If the value is between or equal to the range specified, this will satisfy the condition.

IN sets a condition where a value specified as the condition is checked for in a column. If the predicate is found in the column, the condition is satisfied.

Both BETWEEN and IN can be combined with NOT to produce reversed results.

LIKE

Used to match a character string with a known string or part of a string:

SELECT DISTINCT classification 
FROM production
WHERE classification LIKE '_h%s';

Where the following rules apply to the above LIKE condition:

• The underscore _ stands for any single character. This means for the above that the second character in the string must be 'h'.
• The % stands for any set of zero or more characters.
• The 's' at the end means that the final character in the string must be 's'.

LIKE can also be used in the form NOT LIKE.

IS NULL

Tests whether an entry is null:

SELECT name
FROM country
WHERE cars IS NULL;

Can also be used as IS NOT NULL.

Comments, suggestions, ideas to
Stuart Banner