Monday, January 28, 2008

The WHERE clause is used to filter through the table and extract only the rows you want.

BASIC SYNTAX

SELECT column FROM table

WHERE column operator value

EXPLANATION

You already know what SELECT and FROM is, the new part is:

WHERE column operator value

What WHERE does is tell the SELECT Statement to only select the entries from the table that match a certain criteria. Let's give an example and then we'll explain it a bit more. Using our database we'll issue the following command

SELECT prod_id, prod_name FROM ProductsWHERE vend_id='BRS01';

The output is shown below:

What'd we do? We selected the product ID and product name of the products whose vendor code is BRS01.

So WHERE column operator value means we write WHERE, the name of the column to check or 'vend_id', the operator which in our case was '=', and the value which was BRS01.

This is a list of the basic operators, although we will learn other ones in the future.

Operator

Description

=

Equal

<>

Not equal

>

Greater than

<

Less than

>=

Greater than or equal

<=

Less than or equal

BETWEEN

Between an inclusive range

Here's some examples of these operators using the table 'TeethEyes', shown below:

SELECT child_name, child_age FROM TeethEyesWHERE child_teeth>18;

returns:

SELECT child_name FROM TeethEyesWHERE child_age<=8;

returns:

Harry

Barry

Kari

And finally,

SELECT child_name,child_teeth,child_eyes FROM TeethEyes WHERE child_age BETWEEN 4 AND 9;

returns:

The WHERE statement is almost always used, and will be used extensively in SQL injection. Make sure you've got it so far.