SQL - Where

GSAK (Geocaching Swiss Army Knife)

SQL - Where

The WHERE clause can be used together with the SELECT, UPDATE and DELETE clauses to limit the scope of these commands to data that meets a particular condition

Syntax:

WHERE condition

Condition

The condition is an expression that must evaluate to a Boolean true or false. The following operators are allowed:

Arithmetic
+ - * / ( )

Logic
and or

comparison
< <= = > >=

string constants
e.g. 'Peter Pan'

numeric constants
e.g. 12.45

fieldnames
e.g. userid, users.userid

IN
e.g.
userid IN (300,401,402)
username IN ('James','Smith')

Warning: The IN operator is only supported for SELECT (does not work with UPDATE or DELETE)

Like
e.g.
username Like '%Smith'

You can use the % character to match any series of characters:

'%Smith' will match Smith at the end of username
'Smith%' will match Smith at the beginning of username

'%Smith%' will match Smith anywhere in username

Sub queries
You can use a subquery after the IN clause. Only non-correlated sub queries are allowed. A sub query must select a single field from a table. A sub query is executed and returns a comma separated list of values that replaces the query text in the IN clause. A sub query must be enclosed by brackets.

Example:

select * from users where productid in (select product id from products where productname like 'Ico%')

Notes
When using a SELECT with a join between several tables you must use fully qualified names (tablename.fieldname in every part of the query). For a single table, use the short form fieldname without the tablename.