SQL WHERE Clause
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
- 132 views