SQL WHERE Clause

The WHERE clause is used to specify a selection criterion.

The WHERE Clause

To conditionally select data from a table, a WHERE clause can be added to the SELECT statement.

Syntax

SELECT columnName FROM tableName WHERE columnName operator value;

With the WHERE clause, the following operators can be used:

Operator	Description
=	Equal
<>	Not equal
>	Greater than
<	Less than
>=	Greater than or equal
<=	Less than or equal
BETWEEN	Between an inclusive range
LIKE	Search for a pattern

Note: In some versions of SQL the <> operator may be written as !=.

Using the WHERE Clause

To select only the books published by "Fast Press", we add a WHERE clause to the SELECT statement:

SELECT * FROM Books WHERE Publisher = 'Fast Press';

The "Books" table:

Title	Author	Publisher	Year
الدورة الدموية	إبن النفيس	دار العلم	1650
Java 2	L. Johnston	Fast Press	2002
Linux and Unix	J. Sam	Fast Press	2004
Linux Distributions	J. Sam	Fast Press	2003
Operating Systems	M. Stone	Coriolis	2005

The result is:

Title	Author	Publisher	Year
Java 2	L. Johnston	Fast Press	2002
Linux and Unix	J. Sam	Fast Press	2004
Linux Distributions	J. Sam	Fast Press	2003

Using Quotes

Note that we have used single quotes around the conditional values in the examples.

SQL uses single quotes around text values (most database systems will also accept double quotes). Some versions of SQL (such as MySQL) accept single and double quotes also for numeric values. However, they (numeric values) should not be enclosed in quotes for most database systems.

For text values:

This is correct:
SELECT * FROM Books WHERE Publisher = 'Coriolis';

This is wrong:
SELECT * FROM Books WHERE Publisher = Coriolis;

For numeric values:

This is correct:
SELECT * FROM Books WHERE Year = 2005;

This is wrong for most database systems:
SELECT * FROM Books WHERE Year = '2005';

The LIKE Condition

The LIKE condition is used to specify a search for a pattern in a column.

Syntax

SELECT columnName FROM tableName WHERE columnName LIKE pattern;

A "%" sign can be used to define wildcards (missing letters in the pattern) both before and after the pattern.

Using LIKE

The following SQL statement will return books with titles that start with 'Linux':

SELECT * FROM Books WHERE Title LIKE 'Linux%';

The following SQL statement will return books with titles that end with an 's':

SELECT * FROM Books WHERE Title LIKE '%s';

The following SQL statement will return books with titles that contain the pattern 'in':

SELECT * FROM Books WHERE Title LIKE '%in%';

Test your SQL Skills

To test your SQL skills click here.