Open In Colab

< Previous Page | Home Page | Next Page >

Open in Colab

SQL Syntax: WHERE Clause

A Where clause is used to fetch a particular row or set of rows from a table. This clause filters records based on given conditions and only those row(s) that satisfy the condition in the WHERE clause are returned.

The syntax looks as follows:

SELECT Column_name1, Column_name2, .... FROM Table_name WHERE Condition;

NOTE: we can use this clause with other SQL statements as well such as UPDATE, DELETE etc.


WHERE Conditions

Conditions are expressed via operators. Below you can see the complete list of operators that we can use in the where clause.

Operator Description
> Greater than operator
< Less than operator
= Equal operator
>= Greater than or equal
<= Less than or equal
<> Not equal.
IN To specify a set of values
BETWEEN To specify a range of values
LIKE To specify a pattern


WHERE with one condition

Let's again use our STUDENT Table.

ROLL_NO NAME AGE BRANCH CITY
10001 Kate 22 CSE Mumbai
10002 Richard 21 ECE Delhi
10003 Rick 33 ME Chennai
10004 Peter 26 CSE Delhi
10005 Peter 22 CSE Noida
10006 Mark 22 ECE Jaipur
10007 Brad 23 ME Rampur

Lets say we want to fetch the name of the those students who are more than 23 years old. The SQL statement would look like this:

SELECT NAME FROM STUDENT WHERE NAME > 23;

Result:

NAME
Rick
Peter

WHERE with a string as condition

From the example above, we have seen that when the condition requires numeric values in conditions, we do not require quotes. However when the condition involves a column that has text value then in such case we have to enclose the value in single quotes. Lets look at an example to understand this.

Lets say we want to find out the AGE of student 'Kate'. Here we have to use the following query:

SELECT NAME, AGE FROM STUDENT WHERE NAME = 'Kate';

Result:

NAME AGE
Kate 22

WHERE with multiple conditions (AND & OR)

Lets take the same table of STUDENTS and fetch those students whose age is greater than 23 and the BRANCH is ME.

The Query for this is rather straightforward:

SELECT * FROM STUDENT WHERE AGE > 23 AND BRANCH = 'ME';

Result:

ROLL_NO NAME AGE BRANCH CITY
10003 Rick 33 ME Chennai


Another multiple conditions example: Fetch the student names, where either AGE is between 20 and 22 or name is Rick.

Query:

SELECT * FROM STUDENT WHERE AGE BETWEEN 20 and 22 OR NAME = 'Rick';

Note: the BETWEEN Expression is inclusive.

Result:

ROLL_NO NAME AGE BRANCH CITY
10001 Kate 22 CSE Mumbai
10002 Richard 21 ECE Delhi
10003 Rick 33 ME Chennai
10005 Peter 22 CSE Noida
10006 Mark 22 ECE Jaipur

WHERE with negative conditions (NOT)

Imagine we now want to fetch those students that do not meet a specific criteria. Here we can just use the NOT operator.

Query:

SELECT * FROM STUDENT WHERE NOT BRANCH = 'ME';

Result:

ROLL_NO NAME AGE BRANCH CITY
10001 Kate 22 CSE Mumbai
10002 Richard 21 ECE Delhi
10004 Peter 26 CSE Delhi
10005 Peter 22 CSE Noida
10006 Mark 22 ECE Jaipur

WHERE with the LIKE Operator

Sometimes, you don’t know exactly the complete keyword that you want to query. For example, you may know that your most favorite student beginns with an R but you don’t know exactly the name. This is where the LIKE operator comes into play.

The syntax looks as follows:

SELECT * FROM table_name WHERE column_1 LIKE pattern;

SQLite provides two options for constructing patterns. They are percent sign % and underscore _ :

  • The percent sign % matches any sequence of zero or more characters.
  • The underscore _ matches any single character.

Percent sign % examples

  • The R% pattern that uses the percent matches any string that starts with an R e.g.,Richard and Rick.

  • The %d pattern matches any string that ends with D like Brad or Richard

  • And the %ic% pattern matches any string that contains IC such as Rick or Richard (once again)

Underscore _ examples

  • The Ma_kpattern matches Mark, Maik, etc.
  • The __pple pattern matches topple, supple, tipple, etc.


Further examples are listed below:

LIKE Operator Description
WHERE Name LIKE 'a%' Finds any values that start with "a"
WHERE Name LIKE '%a' Finds any values that end with "a"
WHERE Name LIKE '%or%' Finds any values that have "or" in any position
WHERE Name LIKE '_r%' Finds any values that have "r" in the second position
WHERE Name LIKE 'a_%' Finds any values that start with "a" and are at least 2 characters in length
WHERE Name LIKE 'a__%' Finds any values that start with "a" and are at least 3 characters in length
WHERE Name LIKE 'a%o' Finds any values that start with "a" and ends with "o"

< Previous Page | Home Page | Next Page >

Open in Colab