Skip to main content

SQL WHERE Clause

Body

Whenever you want to retrieve data from your table, there are times that you don’t want to pull all the information. You can retrieve data from the database by using the WHERE clause to filter data based on your criteria.

The WHERE clause in SQL is used to filter out data or to simply select specific data to perform the desired operation.

The WHERE clause can be used in SELECT, UPDATE, and DELETE statement. It is needed to filter what data to select, update, or delete.

WHERE clause in SELECT statement

In SELECT statement we can filter out result by writing the WHERE clause followed by the condition just after the table name.

The WHERE Clause syntax

SELECT column1, column 2,... columnN FROM table_name1 WHERE column operator value

Consider the following example:

SELECT * from Users WHERE country=”USA”

Users:

Firstname Lastname Age Maritalstatus Country
John Smith 40 Married Usa
Bill Steve 20 Single Usa

This statement will select all the users who are from USA, so it is clear that we can retrieve data based on conditions easily, rather than retrieving all data and then search for desired fields which would be quite hectic.

Regarding the syntax

SELECT * FROM Users WHERE country=”USA”

The thing to note is that the column name is written without quotes (“) and the matching field is in quotes (“) .if you do not follow this criteria then it would end up with a syntax error or an unwanted result.

SELECT * FROM Users WHERE “country”=usa

OR

SELECT * from Users where county=usa

Both statements are invalid.

Options available in the WHERE clause

In the WHERE clause we can set various kinds of conditions rather than just selecting the data by using the normal comparison operator.

The conditions can be specified on following operators

Column name =”Some condition” (Equals to)

Example:

SELECT * FROM Users WHERE country=”USA”
Firstname Lastname Age Maritalstatus Country
John Smith 40 Married Usa
Bill Steve 20 Single Usa

Column name <> “some condition” (No equal to)

Example:

SELECT * FROM Users WHERE country <> ”USA”
Firstname Lastname Age Maritalstatus Country
Mathew Simon 30 Married UK
Amanda Rogers 28 Married Germany
Steve Hills 30 Single france

Column name < “Some Numeric condition” (Is Less Than)

Example:

SELECT * FROM Users WHERE Age < 30
Firstname Lastname Age Maritalstatus Country
Bill Steve 20 Single Usa
Amanda Rogers 28 Married Germany

Column name > “some Number condition” (Is Greater than)

Example:

SELECT * FROM Users WHERE Age > 30
Firstname Lastname Age Maritalstatus Country
John Smith 40 Married Usa

Column name <= “Some Numeric condition” (Is less than equal to)

Example:

SELECT * FROM Users WHERE Age <= 30
Firstname Lastname Age Maritalstatus Country
Mathew Simon 30 Married UK
Bill Steve 20 Single Usa
Amanda Rogers 28 Married Germany
Steve Hills 30 Single france

Column name >= “some Number condition” (is Greater than equal to)

Example:

SELECT * FROM Users WHERE Age>=30
Firstname Lastname Age Maritalstatus Country
John Smith 40 Married Usa
Mathew Simon 30 Married UK
Steve Hills 30 Single france

Column name BETWEEN “some Number condition” (is between the max and min specified)

Example:

SELECT * FROM Users WHERE Age BETWEEN “20” AND “30”
Firstname Lastname Age Maritalstatus Country
Bill Steve 20 Single Usa
Amanda Rogers 28 Married Germany

Column name LIKE “some wildcard” (matches some wildcard or string pattern)

Example:

SELECT * FROM Users WHERE Country LIKE “FRA%”
Firstname Lastname Age Maritalstatus Country
Steve Hills 30 Single france

Column name IN “some query or dataset” IS in the dataset given.

Example:

SELECT * FROM Users WHERE Age IN(10,20,30)
Firstname Lastname Age Maritalstatus Country
Mathew Simon 30 Married UK
Bill Steve 20 Single Usa
Steve Hills 30 Single france

WHERE clause in Update Statement

The where clause is a “MUST” to be used in the update statement, but can be however ignored if you want to update all the data of the table, the WHERE clause in the update statement are used just as we would select the data in Select statement. The syntax would

UPDATE Users SET country=”usa” WHERE Country=”France”

This statement will change all the countries from France to USA.

For further help on where clause in SQL update please refer to the update tutorial

WHERE clause in Delete Statement

The WHERE clause is a “MUST” to be used in the DELETE statement, but can be however ignored if you want to delete all the data from the tables. The syntax of the WHERE clause in the DELETE statement would be the same as we would use it in SELECT statement.

DELETE FROM Users where Country=”USA”

This statement deletes all rows having country set to USA.

For further help on where clause in SQL delete please refer to the delete tutorial.

Add new comment