When you select data, there’s a good chance that you’re looking for something specific. In using the SELECT statement you can attach a WHERE clause to limit the data that you are looking through. If you’re very good and know what you’re looking for then you can use the WHERE clause to find exactly what you want.
WHERE Clause Syntax
SELECT [ DISTINCT | UNIQUE ] ( *, columnname, [ AS alias ], … )
FROM tablename
[ WHERE condition ]
[ GROUP BY group_by_expression ]
[ HAVING group_condition ]
[ ORDER BY columnname ];
The condition is what must be met in order to return a row. In the WHERE clause you can use logical operators such as NOT, AND, and OR. The NOT operator will reverse a condition for example:
SELECT * FROM TblPrices
WHERE price = 3.99;
The above SQL statement will return all columns for rows that have a price of 3.99.
SELECT * FROM TblPrices
WHERE NOT PRICE = 3.99;
When we add the NOT operator and execute the query all rows with a price that is not 3.99 will be returned. The AND operator is used if you want two or more statements to be true in order to return a row. The OR only requires one statement to be true when returning a row.
| Mathematical Comparison Operators | Description |
| = | Equal to |
| > | Greater than. |
| < | Less than. |
| <>, !=, ^= | Not Equal |
| <= | Less than |
| >= | Greater than |
| [NOT] BETWEEN x AND y | If the NOT operator is used then when selecting data, numeric data between two values will not be included. If the NOT operator is not used then all rows with the values between x and y will be selected. |
| [ NOT ] IN( x, y ) | This will search for values included in a list. If the NOT operator is used then it will look for values not in the list and return them. |
| [ NOT ] LIKE | Used when searching for patterns. This comes in handy when you are not sure what you are looking for. The '%' is equvilent to the '*' in windows, used when searching for files. The '_' is used to replace one character. |
| IS [ NOT ] NULL | If you're looking for a NULL value then use the IS NULL otherwise use the NOT operator if you are looking for any value. |
When you first start working with Oracle and it's comparison operators you'll notice that you can apply the mathematical comparison operators to strings. When comparing strings, Oracle alphabetizes the strings and anything after a certain point would be considered greater than. Anything before a certain point would be considered less than, and so on. Lets say we have a list of string:
A, B, C, D, E, F, and G
in the column "string." Now if we wanted to get all records that are less than 'D' we would get A, B, and C. Anything greater than 'D' we would get E, F, and G. Dates work the same way. If we had some dates that we want to compare:
29-MAR-07
30-JUN-04
14-JAN-01
If we wanted to get any records with a date that is earlier than June 30, 2004, then we would get January 14, 2001. If we wanted the records with a date that is greater than June 30, 2004 we would get March 29, 2007.
BETWEEN...AND Operator
Another operator that can be used to search is the BETWEEN...AND operator. This gets the same results as using a combination of the greater and less than operators. This can also be applied to strings as well:
SELECT name
FROM TblNames
WHERE id BETWEEN 4 AND 10;
is the same as:
SELECT name
FROM TblNames
WHERE id > 4 AND id < 10;
IN Operator
The IN operator will return records that match any of the values that are listed. This can also be used when limited query results by using strings.
SELECT name
FROM TblNames
WHERE id IN ( 5, 6, 7, 8 );
The above query will return all names that have the id of either 5, 6, 7, or 8.
LIKE Operator
The LIKE operator allows the user to use wildcards when searching for patterns. The two wildcard characters that Oracle uses are the percent sign (%) which matches zero, one, or more characters; and the underscore (_) which matches only one character. The '%' and '_' are Oracle's equivelent to the '*' and '_' in Windows, respectfully.
If we have a list of names that we want to search through and we're looking for all the names starting with P we would query the database as:
SELECT name
FROM TblNames
WHERE name LIKE 'P%';
We would get all names starting with the letter 'P'. For argument's sake we couldn't remember the middle letter of a person's name. If we were to use the '%' we may get more names back than what we really want. This is where the '_' comes in handy:
SELECT name
FROM TblNames
WHERE name LIKE 'P_M';
Logical Operators
AND, OR, and NOT are logical operators. The AND operator returns true when both statements are correct. The OR operator will return true if one of the two statements are true. The NOT will reverse the meaning of a statement. When using multiple operators it's important to know how they are solved:
- Arithmetic operations.
- Comparison operators ( <, >, =, LIKE, etc )
- NOT, AND, and OR
Dealing with NULL Values
Doing comparing against a NULL is a bit different than in other programming languages. You can do do a comparason against a value. However, it's not going to tell you much.
SELECT name
FROM TblNames
WHERE name = NULL;
Will always return nothing. The correct way of searching for a is:
SELECT name
FROM TblNames
WHERE name IS NULL;
ORDER BY Clause Syntax
Oracle offers a way to sort results that are returned in a select statement.
SELECT [DISTINCT | UNIQUE ] ( * | coloumn [ AS aliasname ], ... )
FROM tablename
[ WHERE condition ]
[ GROUP BY group_by_expression ]
[ HAVING group_condition ]
[ ORDER BY column, column, .... ] [ ASC | DESC ] [ NULLS FIRST | LAST ];
When using the ORDER BY clause Oracle orders the results in the following order unless you specify the results to be in descending order:
- Numeric values
- Character values
- NULL values
The NULLS FIRST and NULLS LAST are pretty self explainitory. When you do an ORDER BY and leave out the ASC or DESC Oracle automatically orders the columns in ascending order. In ascending order NULL values will be listed last, but this can be overridden by using the NULLS FIRST. When sorting data in descending order and you want the NULL values to be at the bottom of the list then the NULLS LAST would be used.