> The LIKE operator is used to compare a value to other similar values using wildcard characters.
> This allows you to retrieve information when only part of the value is known.
> There are two wildcards used with the LIKE operator:% - The percent sign is used to ignore zero, one, or more characters or numbers.

_ - The underscore is used to ignore one character or number.

SELECT column1, column2, …
FROM table1, table2, …
WHERE testcolumn [NOT] like ‘pattern’ [ESCAPE 'escapechar'], …;

> SQL standards for LIKE works only with character strings, not with numbers or datetimes. A few rare RDBMS systems let you use LIKE to search numeric and datetime columns.
> LIKE uses a pattern of exact characters and wildcard characters to do the match.
> Wildcards are special characters used to ignore parts of a value.
> String comparisons may be case sensitive or case insensitive depending on your RDBMS.
> Some RDBMS systems take trailing spaces into account for the LIKE operator.
> You can combine LIKE conditions with other conditions using AND and OR.
> You can reverse the LIKE test with NOT LIKE.
> Wildcard searches are slow. Do not use the LIKE command if another search will do. Do not use WHERE STATE LIKE ‘TX’ when WHERE STATE = ‘TX’ would be faster.
> NOT LIKE works the same as LIKE <>.
> Access uses a question mark (?) instead of the conventional underscore (_).

BETWEEN determines if a test column value falls within a specified range.

SELECT column1, column2, ...
  FROM table1, table2, ...
 WHERE testcolumn [NOT] BETWEEN lowvalue AND highvalue, ...;

> BETWEEN is the same as saying WHERE (testcolumn >= lowvalue) AND (testvalue <= highvalue).
> BETWEEN works with numbers, datetimes, character strings and expressions.
> Character string comparisons are case sensitive or insensitive depending on your DBMS.
> The first value must be lower than (or equal to) the second value separated by the word AND.
> The low value and the high value are included in the search results.
> You can reverse the selected values with NOT BETWEEN.
> You can combine BETWEEN with other conditions using AND and OR.
> The testcolumn does not have to be one of the columns you SELECT.
> If the testcolumn value is a NULL and meets the NOT BETWEEN, then the returned value is NULL.

Note: Access surrounds a date with the # symbol, such as #2050-01-01#.
DB2 and SQL Server surround the date with the single quote symbol (’), and they omit the DATE key word. Example: ‘2050-01-01′ instead of DATE ‘2050-01-01′.

 

> NULLs represent missing or unknown values.
> A NULL does not match any value, not even other NULLs.
> “= NULL” can not be used because unknown values do not satisfy specific conditions.The RDBMS engine may try to convert NULL to a string and look for the letters N-U-L-L.
SQL uses “IS NULL” to determine a null condition.

SELECT column1, column2, ...
  FROM table1, table2, ...
 WHERE testcolumn IS [NOT] NULL, ...;

> IS NULL works for columns of any data type.
> You can find a non-NULL condition with IS NOT NULL.
> You can combine IS NULL with other conditions using AND and OR.
> A NULL is not the same thing as an empty string (”).
> Oracle is the exception because it treats an empty sting (”) as a null.
> You can forbid nulls in a table with NOT NULL in the TABLE definition. See Data Definition Language (DDL).

Sometimes you can rewrite a condition, so that its meaning is easier to grasp.These two statements are equivalent:

Which one was easier for you to understand?

We are used to saying, “Less than 10,000 AND more than 100,000.” But if we write:

This will never return data, because if the price is less than 10,000, then the price can not be greater than 100,000. Rewrite the statement:

> 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.

> 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.

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, ...;

> 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.

Three data types allow you to work with a DATE, TIME, or TIMESTAMP.

DATE represents the date as MM/DD/YYYY, DD.MM.YYYY, or YYYY-MM-DD.
TIME is represented as HH:MM AM, HH.MM.SS, or HH:MM:SS.
TIMESTAMP contains the date and time combined.

> The only arithmetic operations that can be performed on DATE/TIME values are addition and subtraction.
> A labeled duration (key words such as YEAR(S), MONTH(S), DAY(S), HOUR(S), MINUTE(S), and SECOND(S)) and other key words such as CURRENT DATE, and CURRENT TIME can be used as part of the calculation.
> The operands must be DATE/TIME data types, or use DECIMAL defined equivalents of DECIMAL (8,0) for date duration, and DECIMAL (6,0) for time duration.

You will learn more about data types in Data Definition.