WHERE CLAUSE and ORDER BY CLAUSE


The syntax for the WHERE clause in Oracle/PLSQL is:

WHERE conditions;

Parameters or Arguments

conditions
The conditions that must be met for records to be selected.

EXAMPLE - WITH SINGLE CONDITION

It is difficult to explain the syntax for the Oracle WHERE clause, so let's look at some examples.

SELECT *
FROM customers
WHERE last_name = 'Anderson';

In this Oracle WHERE clause example, we've used the WHERE clause to filter our results from the customers table. The SELECT statement above would return all rows from the customers table where the last_name is Anderson. Because the * is used in the SELECT, all fields from the customers table would appear in the result set.

EXAMPLE - USING AND CONDITION

SELECT *
FROM suppliers
WHERE state = 'California'
AND supplier_id <= 750;

This Oracle WHERE clause example uses the WHERE clause to define multiple conditions. In this case, this SELECT statement uses the AND condition to return all suppliers that are located in the state of California and whose supplier_id is less than or equal to 750.

EXAMPLE - USING OR CONDITION

SELECT supplier_id
FROM suppliers
WHERE supplier_name = 'Apple'
OR supplier_name = 'Microsoft';

This Oracle WHERE clause example uses the WHERE clause to define multiple conditions, but instead of using the AND condition, it uses the OR condition. In this case, this SELECT statement would return all supplier_id values where the supplier_name is Apple orMicrosoft.

EXAMPLE - COMBINING AND & OR CONDITIONS

SELECT *
FROM suppliers
WHERE (state = 'Florida' AND supplier_name = 'IBM')
OR (supplier_id > 5000);

This Oracle WHERE clause example uses the WHERE clause to define multiple conditions, but it combines the AND condition and theOR condition. This example would return all suppliers that reside in the state of Florida and whose supplier_name is IBM as well as all suppliers whose supplier_id is greater than 5000.

The round brackets determine the order that the AND and OR conditions are evaluated. Just like you learned in the order of operations in Math class!

EXAMPLE - JOINING TABLES

SELECT suppliers.suppler_name, orders.order_id
FROM suppliers
INNER JOIN orders
ON suppliers.supplier_id = orders.supplier_id
WHERE suppliers.state = 'California';

This Oracle WHERE clause example uses the WHERE clause to join multiple tables together in a single SELECT statement. This SELECT statement would return all supplier_name and order_id values where there is a matching record in the suppliers and orderstables based on supplier_id, and where the supplier's state is California.

ORDER BY CLAUSE

The Oracle ORDER BY clause is used to sort the records in your result set. The ORDER BY clause can only be used in SELECT statements.

SYNTAX

The syntax for the ORDER BY clause in Oracle/PLSQL is:

SELECT expressions
FROM tables
WHERE conditions
ORDER BY expression [ ASC | DESC ];

Parameters or Arguments

expressions
The columns or calculations that you wish to retrieve.
tables
The tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause.
conditions
The conditions that must be met for the records to be selected.
ASC
Optional. It sorts the result set in ascending order by expression (default, if no modifier is provider).
DESC
Optional. It sorts the result set in descending order by expression.

Note: If the ASC or DESC modifier is not provided in the ORDER BY clause, the results will be sorted by expression in ascending order (which is equivalent to ORDER BY expression ASC.

EXAMPLE - SORTING WITHOUT USING ASC/DESC ATTRIBUTE

The Oracle ORDER BY clause can be used without specifying the ASC or DESC value. When this attribute is omitted from the ORDER BY clause, the sort order is defaulted to ASC or ascending order.

For example:

SELECT supplier_city
FROM suppliers
WHERE supplier_name = 'Microsoft'
ORDER BY supplier_city;

This Oracle ORDER BY example would return all records sorted by the supplier_city field in ascending order and would be equivalent to the following ORDER BY clause:

SELECT supplier_city
FROM suppliers
WHERE supplier_name = 'Microsoft'
ORDER BY supplier_city ASC;

Most programmers omit the ASC attribute if sorting in ascending order.

EXAMPLE - SORTING IN DESCENDING ORDER

When sorting your result set in descending order, you use the DESC attribute in your ORDER BY clause as follows:

SELECT supplier_city
FROM suppliers
WHERE supplier_name = 'Microsoft'
ORDER BY supplier_city DESC;

This Oracle ORDER BY example would return all records sorted by the supplier_city field in descending order.

EXAMPLE - SORTING BY RELATIVE POSITION

You can also use the Oracle ORDER BY clause to sort by relative position in the result set, where the first field in the result set is 1. The next field is 2, and so on.

For example:

SELECT supplier_city
FROM suppliers
WHERE supplier_name = 'Microsoft'
ORDER BY 1 DESC;

This Oracle ORDER BY would return all records sorted by the supplier_city field in descending order, since the supplier_city field is in position #1 in the result set and would be equivalent to the following ORDER BY clause:

SELECT supplier_city
FROM suppliers
WHERE supplier_name = 'Microsoft'
ORDER BY supplier_city DESC;

EXAMPLE - USING BOTH ASC AND DESC ATTRIBUTES

When sorting your result set using the Oracle ORDER BY clause, you can use the ASC and DESC attributes in a single SELECT statement.

For example:

SELECT supplier_city, supplier_state
FROM suppliers
WHERE supplier_name = 'Microsoft'
ORDER BY supplier_city DESC, supplier_state ASC;

Source: www.techonthenet.com


Advertisements