SELECT with WHERE and Operators

The WHERE clause is used to specify a selection criterion. SELECT * FROM table WHERE condition The condition can be expressed as: column operator value

Boolean Operators

The following boolean operators can be used:

Operator	Description
=	Equal
<>	Not equal
>	Greater than
<	Less than
>=	Greater than or equal
<=	Less than or equal

Note: In some versions of SQL the <> operator may be written as !=

Example:

SELECT * FROM stud WHERE id>2 and semester <3;

BETWEEN Operator

The BETWEEN operator implements a range comparison.
It tests whether a value is between two other values. value1 [NOT] BETWEEN value2 AND value3 It is equivalent to: value1 >= value2 AND value1 <= value3 Example: SELECT * FROM stud WHERE id BETWEEN 3 AND 7

IN Operator

The IN operator tests whether a value matches any value in a list of values: value1 [NOT] IN ( value2 [, value3] ... ) This comparison tests if value1 matches value2 or matches value3, and so on.
It is equivalent to the following logical predicate: value1 = value2 [ OR value1 = value3 ] ... or if NOT is included: NOT (value1 = value2 [ OR value1 = value3 ] ...) For example, SELECT name FROM s WHERE city IN ('Rome','Paris')

LIKE Operator

LIKE implements a pattern match comparison, it matches a string value against a pattern string containing wild-card characters, which are underscore '_' for any single character and percent '%' that matches zero or more characters. Examples:

Match Value Pattern Result

'abc' '_b_' True

'ab' '_b_' False

'abc' '%b%' True

'ab' '%b%' True

'abc' 'a_' False

'ab' 'a_' True

'abc' 'a%_' True

'ab' 'a%_' True

Match Value	Pattern	Result
'abc'	'_b_'	True
'ab'	'_b_'	False
'abc'	'%b%'	True
'ab'	'%b%'	True
'abc'	'a_'	False
'ab'	'a_'	True
'abc'	'a%_'	True
'ab'	'a%_'	True

LIKE comparison has the following general format:

value1 [NOT] LIKE value2 [ESCAPE value3] This comparison uses value2 as a pattern to match value1.
The optional NOT reverses the result. All values must be string (character). Example: SELECT * FROM stud WHERE name LIKE '%o%'
The optional ESCAPE sub-clause specifies an escape character for the pattern, allowing the pattern to use '%' and '_' (and the escape character) for matching. The ESCAPE value must be a single character string. In the pattern, the ESCAPE character precedes any character to be escaped.

For example, to match a string ending with '%', use:

value1 LIKE '%/%' ESCAPE '/'

IS NULL Operator

NULL has a special meaning: The value of the column is not currently known (missing),
however its value may be known at a later time.

Since two NULL columns may eventually be assigned different values,
one NULL cannott be compared to another in the conventional way.
The following syntax is illegal in SQL: WHERE qty = NULL.
A special comparison operator -- IS NULL, tests a column for NULL:

value1 IS [NOT] NULL The optional NOT reverses the result. Example: SELECT name FROM stud WHERE semester IS NULL