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:

  • Comparison predicates

  • BETWEEN

  • IN [NOT IN]

  • LIKE [NOT LIKE]

  • NULL

  • ALL, SOME, ANY

  • EXISTS

  • UNIQUE

  • OVERLAPS

  • MATCH

  • SIMILAR

  • DISTINCT

  • Add a Comment
  • Print
  • Share
blog comments powered by Disqus
Advertisement

Inside Dummies.com