Monday, January 28, 2008

SQL Basics - The WHERE Clause

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


SELECT column FROM table
WHERE column operator value


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 Products
WHERE vend_id='BRS01';

The output is shown below:

output where 1

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.

<>Not equal
>Greater than
<Less than
>=Greater than or equal
<=Less than or equal
BETWEENBetween an inclusive range

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


SELECT child_name, child_age FROM TeethEyes
WHERE child_teeth>18;



SELECT child_name FROM TeethEyes
WHERE child_age<=8;





And finally,

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



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

No comments:

Contact Form