Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

SQL etc

Posted Sep 13, 1998

Introduction to Databases for the Web: Pt. 2 - Page 2

By Selena Sol

Where

Suppose you don't want all of the data for all of the rows in a table. Instead, perhaps you only want data for rows that match some criteria. For example, perhaps I want to see the data for the employees, but I only am interested in seeing the data for the employees who make over $45,000 per year.

Well, SQL provides the "where" clause for just these circumstances. The "where" clause allows you to specify conditions that a column cell must meet if it is to be considered a match and be returned in the results. In SQL syntax terminology, the WHERE clause is called the predicate.

The generic syntax of the WHERE clause looks something like the following:

SELECT column_name
FROM table_name
WHERE where_clause;

Consider the following case in which we ask the database to return only the rows in the SALES table in which the Employee number is equal to "101"

SELECT * 
FROM SALES
WHERE E_NUM = 101;

In this case, the database would return the following:

---------------------------------
001   001   1   99.99   101   102
003   002   1  865.99   101   103
---------------------------------

The WHERE clause can be used in conjunction with various testing operators besides the "=" sign. Specifically, you can use the ">", "<", "<=", or ">=" operators to select ranges. Thus, to get a report of all the employees making more than 45,000 per year, you might use the following:

SELECT *
FROM EMPLOYEES
WHERE EMP_SALARY > 45000;

If you are comparing a column of the CHARACTER data type, you can place the match string in single quotes ('').

For example, to find out Rick Tan's phone number from the CLIENTS table, you might use:

SELECT C_PHONE, C_NAME
FROM CLIENTS
WHERE C_NAME = 'Rick Tan';

In this case, the database would return the following:

C_PHONE           C_NAME
------------------------
649-2038        Rick Tan
------------------------

Note that although SQL is generally case insensitive, when you are matching CHARACTERS using the single quotes, you must be aware of case. Thus, WHERE name = 'CHRIS' would not return the same as WHERE name = 'Chris'.

And, Or, and Not

So far, the WHERE clauses we have discussed have been pretty darn simple. And as such, they are not all that useful in real world situations in which multiple conditions affect our needs. Actually, SQL provides a set of operators that allow you to combine multiple predicate statements. Specifically, a WHERE clause can be made up of multiple predicates by chaining them together with the AND, OR, and NOT operators.

These operators work as you might expect. The AND operator specifies multiple conditions which a column must match in order to be returned. The OR operator specifies multiple acceptable conditions. And the NOT operator specifies negativity in a match. However, these are best understood by example.

Consider the case in which we want to return only rows from the SALES table in which the salesman was salesman number "101", but we only want sales in which the price was greater than $100.00:

SELECT P_NUM, S_AMOUNT, E_NUM
FROM SALES
WHERE E_NUM = 101 AND S_AMOUNT > 100;

In this case, our database would return:

P_NUM     S_AMOUNT       E_NUM
------------------------------
002         865.99         101
------------------------------

In this case, the row was returned because S_AMOUNT was greater than "100" "AND" E_NUM was equal to "101". Now consider the same query using OR:

SELECT P_NUM, S_AMOUNT, E_NUM
FROM SALES
WHERE E_NUM = 101 OR S_AMOUNT > 100;

In this case, we would get the following response:

P_NUM     S_AMOUNT       E_NUM
------------------------------
001          99.99         101
001         199.98         102
003         865.99         101
------------------------------

Notice that the first row satisfied the condition because E_NUM was equal to "101" even though S_AMOUNT was less than "100". Likewise, the second row was selected because S_AMOUNT was greater than "100" even though E_NUM was not equal to "101."

Another important thing to note about AND and OR is that if you decide to combine multiple conditions, you must be very careful about how you compose your predicate. AND and OR conditions can be defined logically using parentheses as in most languages, but the order of the parentheses has meaning. Thus

SEX = "M" AND (WEIGHT > 150 OR AGE < 35)

is much different than saying

(SEX = 'M' AND WEIGHT > 150) OR AGE < 35

In the first case, no female subject could be returned whereas in the second case, a female could be returned if she was younger than 35.

Finally, the NOT operator is used to specify the reverse condition.

Thus, if you want a list of all the employees other than Lim Sing Yuen from the sample database, you would use:

SELECT EMP_NUM, EMP_NAME
FROM EMPLOYEES
WHERE NOT (EMP_NAME = 'Lim Sing Yuen');

In this case, the database would return the following:

EMP_NUM     EMP_NAME
--------------------
001     Lim Li Chuen
003    Loo Soon Keat
--------------------


SQL etc Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
SQL etc Forum
Topic By Replies Updated
MySQL rollback UAL225 0 August 21st, 09:56 PM
Complex Search Query Galway 0 May 20th, 10:04 PM
change collation at once supercain 2 May 15th, 06:18 AM
SQL Features, tools and utilities question Neomite 1 April 10th, 09:13 AM