SQL Where Clause Example | SQL Where Query
SQL WHERE clause is used to filter the database records. The WHERE clause is used to extract only those records that fulfill the specified condition. SQL WHERE clause is used to specify the condition while fetching the data from a single table or by joining the multiple tables.
If the given condition is satisfied, then only it returns the specific value from the table. You should use a WHERE clause to filter the records and fetching the necessary records.
SQL Where Clause Example
SQL WHERE clause is used to specify a condition while retrieving the data from a single table or by joining with multiple tables. If the given condition is met, then only it returns specific value from the table.
The syntax of a SELECT statement with a WHERE clause is the following.
SELECT column1, column2, columnN FROM table_name WHERE [condition]
“SELECT column1, column2, column3 FROM tableName” is the standard SELECT statement.
“WHERE” is the keyword that restricts the select query result set and “condition” is a filter to be applied to the results. The filter can be the range, single value or subquery.
If you have followed that tutorial then in that tutorial, I have created a table and inserted some records.
Now, we fetch the records based on the WHERE clause.
Okay, now first let’s fetch all records.
For that, I need to write the following query.
SELECT * FROM Apps
See the following output.
SQL WHERE Query
SQL requires single quotes around text values. However, the numeric fields should not be enclosed in the quotes.
Okay, now let’s use the WHERE query to filter the record. Let’s fetch the record by the CreatorName column.
Write the following query.
Select * from Apps Where CreatorName = 'Krunal'
See the output.
Let’s fetch the row by filtering AppCategory.
Select * from Apps Where AppCategory = 'Investment'
See the output.
You can specify the condition using the comparison or logical operators like >, <, =, LIKE, NOT, etc.
SQL Where Statement with Logical Operator
We can use the Logical operator with the Where clause to filter out the data by comparing.
Let’s say we fetch the data whose AppPrice is greater than 70$. See the following query.
Select * from Apps Where AppPrice > 70
See the output.
Operators in WHERE Clause
The following operators can be used in a WHERE clause.
|>=||Greater than or equal|
|<=||Less than or equal|
|<>||Not equal. Note: In some versions of SQL this operator may be written as !=|
|BETWEEN||Between the certain range|
|LIKE||Search for the pattern|
|IN||To specify multiple possible values for a column|
WHERE clause combined with AND LOGICAL Operator
The WHERE clause when used with the AND operator, is only executed if All the filter criteria specified are met. See the following query.
Select CreatorName from Apps Where AppPrice > 70 AND AppName = 'Moneycontrol'
So, we are fetching the only CreatorName in which the AppPrice is > 70 and AppName = MoneyControl. See the below output.
WHERE clause combined with IN Keyword
The WHERE clause when used together with an IN keyword only affects the rows whose values match the list of values provided in the IN keyword. IN helps to reduce the number of OR clauses you may have to use. See the following query.
Select * from Apps Where AppPrice IN (50, 60, 110)
In the above query, we are fetching those records whose AppPrice is either 50, 60, or 110. If AppPrice matches in the database, then it will give us that row in the output.
In our case, only 50 and 60 matches, 110 is not there in the database. So, two rows will be returned.
WHERE clause combined with OR LOGICAL Operator
Select * from Apps Where AppPrice = 50 OR AppCategory = 'Fashion'
In the above query, Either AppPrice = 50 holds or AppCategory = Fashion holds.
If both true then also it gives the result. See the output.
WHERE clause combined with NOT IN Keyword
The WHERE clause used with the NOT IN keyword does not affect the rows whose values match the list of values provided in the NOT IN keyword. See the following query.
Select * from Apps Where AppPrice NOT IN(40, 50, 60)
So, it will return those rows which have not AppPrice 40, 50, 60. It will give us the remaining rows.
The SQL WHERE statement is used to restrict the number of rows affected by the SELECT, UPDATE or DELETE query. The WHERE term can be used in the conjunction with logical operators such as AND and OR, comparison operators such as equals to(=), etc.
When used with the AND logical operator, all the criteria must be met. When used with the OR logical operator, any of the criteria must be met. The keyword IN is used to select rows matching the list of values.
Finally, SQL Where Clause Example | SQL Where Query Tutorial is over.