The sort sequence for numeric data, dates, and times is easily obvious.
When you look at the sort sequence for alphanumeric data with special characters, the order is sometimes not so obvious. See Collating Sequence.
If the ORDER BY columns do not identify each row uniquely, rows with duplicate values in the ORDER BY clause will be listed in arbitrary order.
You can use expressions in place of the ORDER BY columns.Nulls complicate the situation.
Even though nulls mean that there is no value, nulls are considered equal when sorted.
The SQL standard leaves it up to the RDBMS system to decide if nulls are greater or less than non-null values.

SQL allows you to use a relative column number in place of the column name in the ORDER BY clause.
This relative column number points to the column name in the SELECT clause.
This practice is a bad idea because the order of the SELECTed columns could be changed, and this would mean that the relative column numbers now point to different column names and is confusing.

You can reference column alias names in the ORDER BY clause. See: AS (Column Aliases).
You can intermingle, column names, alias column names, expressions, and relative column numbers.

> Access, MySQL, and SQL Server treat nulls as the lowest possible value.
> DB2, Oracle, and PostgreSQL treat nulls as the highest value.
> Access won’t let you sort expressions.
> Oracle won’t let you sort BLOB, CLOB, or NCLOB.
> SQL Server won’t let you sort IMAGE, TEXT, or NTEXT.

The SQL standard says that row order is irrelevant. The query results are therefore unordered and arbitrary.
You can use the ORDER BY clause to sort rows in the result table by a column or columns in ascending (ASC) order (lowest to highest) or descending (DESC) order (highest to lowest). See Collating Sequence.

     SELECT   [ALL | DISTINCT]
              [* | column1[, column2, ...]]
       FROM   [table1 correlation, ...
             | view1 correlation, ...]
      WHERE    condition1[, condition2, ...]
      GROUP BY column1[, column2, ...]
     HAVING    condition1[, condition2, ...]
      ORDER BY column1 [ASC | DESC][, column2 [ASC | DESC], ...];

> The ORDER BY clause is always the last clause in a SELECT statement when used.
> The columns in ORDER BY do not have to be listed in the SELECT clause.
> Specify ASC for ascending, or DESC for descending. If nothing is specified, it defaults to ASC.
> The GROUP BY and HAVING clauses will be discussed later. See GROUP BY and HAVING.

The IN operator is used to find if a given value matches a value in a specified list.

SELECT column1, column2, ...
  FROM table1, table2, ...
 WHERE testcolumn [NOT] IN (value1, value2, ...;

> IN works with character strings, numbers, and datetimes. The testcolumn can be an expression.
The list value must be the same comparable data type as the testcolumn.
> The IN list is contained in parentheses and contains one or more values separated by commas.
The values in the list can be in any order.
> You can combine IN conditions with other conditions using AND and OR.
> NOT IN is used to find values that are not in the list values.
> String comparisons may or may not be case sensitive depending on your RDBMS.
> The IN operator is shorter than using many OR conditions and runs faster. For example:

WHERE testcolumn IN            or  WHERE (testcolumn = value1)
      (value1, value2, value3)        OR (testcolumn = value2)
                                      OR (testcolumn = value3)

For speed, list the values most likely to find a match first.

 

> The SIMILAR operand was introduced by SQL:1999 and gives an alternate method of doing a comparison.
> With SIMILAR you can compare a character string to an expression.

SELECT column1, column2, ...
  FROM table1, table2, ...
 WHERE testcolumn [NOT] SIMILAR TO (expression), ...;

Example:

WHERE DAY_FIELD SIMILAR TO
      '('Day=' (Mon | Tue | Wed | Thu | Fri))'

or

WHERE COLOR SIMILAR TO
      '('The color is ' (red | green | blue | 12 | 14))'

 

> Sometimes you may wish to include an underscore or a percent sign in the LIKE comparison pattern.
> You need a way to tell SQL that the underscore or percent sign is not to be used as a wildcard character.
> You can do this by typing an escape character just prior to the character you wish SQL to take literally.
> You can choose any character as the escape character, as long as that character does not appear in the string you are testing, as shown:

> This statement will look for the percent sign (%) anywhere in the NOTES.
> The first % says there may be any number of characters ahead of the percent sign.
> The next #% says that it is looking for the percent sign and it is not a wildcard character because it has the escape character ahead of it. The escape character must be defined as shown above.
> The last % says that there may be any number of characters after the percent sign.

> Access does not support the ESCAPE clause. Instead it uses brackets [ ] for an exact character matches on a single chracter. In Access WHERE NOTES LIKE ‘%[%]%’ does the same as the example shown above.
> SQL Server expands on the Microsoft use of brackets. A range of a single character match can be specified and the [^] character means not. Examples:

‘[a-g]et’ matches ‘bet’, & ‘get’, but not ‘let’.
‘[bln]et’ matches ‘bet’, ‘let’, & ‘net’, but not ‘get’.
‘ma[^t]%’ matches ‘man’, & ‘maverick’, but not ‘mat’, or ‘material’.

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