Tuesday, 13 January 2015

Filtering Data With The WHERE Clause In SQL

All the SELECT statements used in the previous examples were issued to retrieve all rows (records) from the defined tables. To limit the number of returned rows from the query, you use the WHERE clause which should be used immediately after the FROM clause. In this clause you specify a condition comprising three components: expression, comparison operator, and value.


    Syntax :

… WHERE  expression   comparison operator   value         

Here, expression can be a table column, a constant value, or an expression itself. A condition is evaluated by comparing the data defined in the expression position with the value using the comparison operator. In subsequent exercises you will go through different flavors of the WHERE clause. But here, you are provided with couple of simple examples to show some basic usage of this clause.

In the first statement below, LOCATION_ID (a column name) performs as an expression, (=) is the comparison operator, and 1700 is the value which is being compared with the expression. The query retrieves all records (with all columns - *) for the departments established under location number 1700.

In the second example, we used the BETWEEN operator and specified a range of values to get a list of employees who are earning between 100 and 10000.


    SQL Statements :

SELECT         *
FROM           departments
WHERE        location_id = 1700;

SELECT         *
FROM            employees
WHERE        salary between 100 and 10000        
ORDER  BY  salary;


No comments:

Post a Comment