August 22nd, 2008

AND, OR, and NOT, Part 2 of 3

Posted by admin in D. WHERE Clause

> NOT does not connect two conditions like AND and OR.
Instead it reverses a single condition.

> NOT is placed before the column name or expression
and not before the operator.

> NOT acts on only one condition.
To reverse two or more conditions,
repeat the NOT for each condition.

> The following two statements are equivalent.
Choose whichever style works best for you.

August 21st, 2008

AND, OR, and NOT, Part 1 of 3

Posted by admin in D. WHERE Clause

> AND, OR, and NOT are Boolean operators designed to work with truth values: TRUE, FALSE, or NULL.
> The ANSI/ISO standards specify that NOT has the highest precedence, followed by AND, and then OR.
> Default precedence rules say that x AND NOT y OR z is equivalent to (x AND (NOT y)) OR z.

    SELECT column1, column2, ...
      FROM table1, table2, ...
     WHERE testcolumn1 [NOT] condition1
[AND | OR] testcolumn2 [NOT] condition2 ...;

> AND is used to combine two search conditions where both must be true.
> OR is used to combine two search conditions where one or the other (or both) must be true.
> NOT is used to select rows where a search condition is false.

 Here are the appropriate truth tables:

In MySQL 4.0.4 and earlier, FALSE AND NULL evaluate to NULL, not FALSE.
It is always a good idea to use parentheses to remove ambiguity.

August 20th, 2008

Column and Row Selection

Posted by admin in D. WHERE Clause

The column names are used to select only those columns.
The WHERE clause is used to specify the rows you wish to retrieve.
If we use both, then we retrieve only the columns and rows we need.

SELECT column1, column2, ...
  FROM table1, table2, ...
 WHERE condition1, condition2, ...;
August 19th, 2008

WHERE Clause (Row Selection)

Posted by admin in D. WHERE Clause

> SQL queries that give you all of the rows in a table are useful for seeing what is in a database, but it is a rare occasion when you want to see everything.
> The WHERE clause is used to specify a search condition (also called a predicate) to obtain the rows you wish to retrieve when the condition is true.
> Rows that do not meet the WHERE condition, or are unknown, are excluded.
> Character strings are compared by their collating sequence. “<” means precedes. “>” means follows. See Collating Sequence.
> Numbers are compared by their arithmetic value. “<” means less than. “>” means greater than.
> Datetimes are compared chronologically. “<” means earlier. “>” means later.

SELECT [ALL | DISTINCT] [* | column1[, column2, ...]]
  FROM table1[, table2, ...]
 WHERE testcolumn oper value, ...;

> In the WHERE search condition, testcolumn is the name of a column in the FROM table.
> Testcolumn does not have to be listed in the SELECT columns.
> Oper is one of the comparison operators: =, <>, <, <=, >, >=, BETWEEN, LIKE, IN, IS NULL, AND, OR, NOT.
> Value is a value that is compared to the value in the testcolumn.
> Testcolumn or value can be an expression, which is any valid combination of column names, functions, literals and operators that solve to a single value.

> Access, DB2, and SQL Server are case insensitive. That is ‘a’ = ‘A’.
> Oracle and PostgreSQL are case sensitive. That is ‘a’ is not = to ‘A’.
> MySQL is case insensitive in WHERE comparisons, and case sensitive in string functions.

« Previous Page