Slide 9.19: SQL WHERE clause

SQL WHERE Clause

The WHERE clause is used to filter records. It is used to extract only those records that fulfill a specified criterion.

With the where clause, the operators below can be used:

 SELECT column_name(s) FROM table_name
   WHERE column_name operator value;

Operator	Description	Operator	Description	Operator	Description
`=`	Equal	`<>` or `!=`	Not equal	`>`	Greater than
`<`	Less than	`>=`	Greater than or equal	`<=`	Less than or equal
`LIKE`	Search for a pattern	`BETWEEN`	Between an inclusive range	`IN`	One of the multiple vluaes

person_id	last_name	first_name	address	city
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

To select only the persons living in the city Sandnes, we add a WHERE clause to the SELECT statement:

 SQL> SELECT * FROM person WHERE city = 'Sandnes';

 PERSON_ID   LAST_NAME     FIRST_NAME           ADDRESS          CITY
 ---------   -----------   -----------------    -------------    -----------
 1           Hansen        Ola                  Timoteivn 10     Sandnes
 2           Svendson      Tove                 Borgvn 23        Sandnes

Single quotes are used around the conditional values in the examples. SQL uses single quotes around text values (most database systems also accept double quotes). Numeric values should not be enclosed in quotes.

 SQL> SELECT * FROM person WHERE  first_name=Tove;
 ERROR 1064 (42000): Syntax error near '=Tove' at line 1

 SQL> SELECT * FROM person WHERE Year='1965';
 ERROR 1064 (42000): Syntax error near '=1965' at line 1

Demonstration
Below is an SQL test area from W3Schools, which uses the well-known Northwind sample database. The tables here are for read only because of the problem of embedding the scripts. For a fully working example, check this by using Chrome.

Result:

The Database includes:

Tablename	Record
Customers	91
Categories	8
Employees	10
OrderDetails	518
Orders	196
Products	77
Shippers	3
Suppliers	29

W3C WebSQL

WebSQL Browser Support