How to Use WHERE Clauses in SQL
The WHERE clause in SQL requires little explanation because its meaning and use are obvious: A statement performs an operation (such as SELECT, DELETE, or UPDATE) only on table rows WHERE a stated condition is True. The syntax of the WHERE clause is as follows:
SELECT column_list FROM table_name WHERE condition ; DELETE FROM table_name WHERE condition ; UPDATE table_name SET column1=value1, column2=value2, ..., columnn=valuen WHERE condition ;
The condition in the WHERE clause may be simple or arbitrarily complex. You may join multiple conditions together by using the logical connectives AND, OR, and NOT to create a single condition.
The following are some typical examples of WHERE clauses:
WHERE CUSTOMER.CustomerID = SALES.CustomerID WHERE FOODS.Calories = COMIDA.Caloria WHERE FOODS.Calories < 219 WHERE FOODS.Calories > 3 * base_value WHERE FOODS.Calories < 219 AND FOODS.Protein > 27.4
The conditions that these WHERE clauses express are known as predicates. A predicate is an expression that asserts a fact about values.
The predicate FOODS.Calories < 219, for example, is True if the value for the current row of the column FOODS.Calories is less than 219. If the assertion is True, it satisfies the condition. An assertion may be True, False, or unknown. The unknown case arises if one or more elements in the assertion are null.
The comparison predicates (=, <, >, <>, <=, and >=) are the most common, but SQL offers several others that greatly increase your capability to filter out a desired data item from others in the same column. These predicates give you that filtering capability:
IN [NOT IN]
LIKE [NOT LIKE]
ALL, SOME, ANY