SQL Clauses

With most of your queries, normally you will want to use clauses to limit the information returned. This especially true for UPDATE and DELETE commands.

The four common clauses are WHERE, GROUP BY, ORDER BY, and LIMIT (LIMIT is an extension to the SQL standard). Examples:

• SELECT * FROM tablename ORDER BY columnname DESC

  (Retrieves every column and every record from the table, sorted by columnname in descending order.)

• SELECT * FROM tablename ORDER BY column1name ASC, column2name DESC

  (Retrieves every column and every record from the table, sorted by column1name in ascending order, then by column2name in descending order.)

• SELECT * FROM tablename LIMIT 10

  (Retrieves every column of the first ten records from the table.)

• SELECT * FROM tablename LIMIT 100, 50

  (Retrieves every column of the 101st through 150th records from the table.)

You can also, and frequently will, use multiple clauses in the same query. The clauses always go in this order:

SELECT columns FROM tablename WHERE clause GROUP BY columnname ORDER BY columnname LIMIT x

Clauses can be applied just to a column name, or they can be used in conjunction with parentheses and operators to create more elaborate conditionals. Table B.3 lists the operators and comparators you'll commonly use.

Table B.3. These operators and comparators can be used in conjunction with parentheses to create different expressions in SQL queries.

Operators and Comparators
Operator	Meaning
+	Addition
-	Subtraction
*	Multiplication
/	Division
%	Modulus
<	Less than
>	Greater than
<=	Less than or equal to
>=	Greater than or equal to
=	Equals
!= (also <>)	Not equal to
%	Multiple wildcard character (used with LIKE)
_	Single wildcard character (underscore) (used with LIKE)
IS NOT NULL	Has a value (including an empty string or zero)
IS NULL	Does not have a value
BETWEEN	Within a range
NOT BETWEEN	Outside of a range
IN	Is one of a listed set of values
NOT IN	Is not one of a listed set of values
OR (also ||)	Where at least one of two conditionals is true
AND (also &&)	Where both conditionals are true
NOT (also !)	Where the condition is not true
OR NOT (also ^)	Where only one of two conditionals is true
LIKE	Where the value matches a string
NOT LIKE	Where the value does not match a string
REGEXP	Where the value matches a pattern
NOT REGEXP	Where the value does not match a pattern
MATCH AGAINST	Where the values matches against a series of words