AppDividend
Latest Code Tutorials

SQL Where Clause Example | SQL Where Query Tutorial

0

SQL Where Clause Example | SQL Where Query Tutorial is today’s topic. The WHERE clause is used to filter the database records. The WHERE clause is used to extract only those records that fulfill the specified condition. The SQL WHERE clause is used to specify the condition while fetching the data from a single table or by joining the multiple tables. If a 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

The WHERE clause is not only used in the SELECT statement; it is also used in an UPDATE, DELETE statement. The syntax of a SELECT statement with a WHERE clause is 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.

Now, we will head for the example, but before that, you might check out my Create table in SQL and How to insert data in database posts.

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 Clause Example

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 CreatorName column.

Write the following query.

Select * from Apps
Where CreatorName = 'Krunal'

The output is following.

 

SQL Where Query Tutorial

Let’s fetch the row by filtering AppCategory.

Select * from Apps
Where AppCategory = 'Investment'

The output is following.

 

WHERE Clause Example

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

The output is following.

 

SQL Where Statement with Logical Operator

Operators in The WHERE Clause

The following operators can be used in a WHERE clause.

Operator Description
= Equal
> Greater than
< Less than
>= 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.

 

SQL Where Clause Example | SQL Where Query Tutorial

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.

 

SQL Where Query Tutorial With Example

WHERE clause combined with OR LOGICAL Operator

The WHERE clause when used with the OR operator, is only executed if any or the entire specified filter criteria are met. See the following query.
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 OR LOGICAL Operator

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.

 

WHERE clause combined with NOT IN Keyword

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.

Leave A Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.