SQL Where Clause

Read this article to understand the Where clause along with the examples. We have shared few examples and we have shared basic as well as advance level common interview questions also.

In this post, I will provide you a short overview of Where clause with all its options. In addition, we have covered some questions which we receive from the audience. 

First, let’s talk about the theory of Where clause and why we should use it.

Definition

The Where Clause or Keyword is used to filter the data.Sometimes we want to print only selected records. Sometimes we need to filter the data. Guess what “when you have to select the records which is between the age of 5 to 10”. Guess you have millions of records and you want to print only few records based on criteria. The keyword “Criteria“, the keyword “Constraint” it means “Where” in the sql. Where clause can be used with Select, Update, Delete command.

Reasons to Use

As it’s name suggest we can use the Where clause to filter the data. Below are the some key areas where we need to use Where clause.

1. Show the employees from INDIA country.
2. Show the products which has price range between 100 to 1000.
3. Update the students marks which is less than 35.
4. Delete the students who are failed.

Syntax

SELECT column1, column2, columnN 
FROM table_name
WHERE [condition];

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE [condition];

DELETE FROM table_name
WHERE [condition];

What will be the general english statement when you want to select few student from your class whose roll number less than 10.

select few students from myclass where roll number less than 10.

That’s it!

Lets convert above statement into SQL

select name from student_table 
where roll_no < 10;

ohh!!! Finally I did it without any syntax error :)!

It means we can use the comparator operators to construct the criteria or constraints.

Below are few operators which we can use for constructing the conditions.

The condition can be a combination of one or more column values compared to a specific value, a range of values, or other logical expressions using comparison operators such as = , < , > , <= , >= , <> (not equal), LIKE , IN , BETWEEN , IS NULL , IS NOT NULL , and logical operators such as AND , OR , and NOT .

Example

Example 1:

Lets see the graphical representation of the Where clause. We have “Customers” table, having 5 records with few columns. Print some records which belongs to “Doe”. The general statement will be “Select records from customers table where last name is Doe”.

Too simple right!!

Now time to understand Where clause with multiple conditions.

Multiple Conditions

Can we print the the records which belongs to USA country and age is greater than 30.

Answer is yes, by using multiple conditions operator. What are the alternatives for multiple conditions?

The  Where  clause can be combined with  AND ,  OR , and  NOT  operators.

SELECT column1, column2, columnN
FROM table_name
WHERE [condition] AND [condition2] AND [condition3 ]...;

SELECT column1, column2, columnN
FROM table_name
WHERE [condition] OR [condition2] OR [condition3 ]...;

SELECT column1, column2, columnN
FROM table_name
WHERE NOT [condition];

SELECT column1, column2, columnN
FROM table_name
WHERE column1 IS NOT NULL;

SELECT column1, column2, columnN
FROM table_name
WHERE column1 IS NULL;

Time to learn AND, OR and NOT in detail. Take a breath and give us 2 minutes to explain this.

What will be your general statement to print records which belongs to USA and age is above 30 and name is John?

Let me think…..

Yeah.. I am ready and the statement will be

Example 2

Select records from Customer table where Country is USA AND age is greater than 30 AND Name is John.

Convert above statement to SQL format

Select * 
FROM Customers
Where Country = "USA" AND age > 30 AND first_name = 'John';

Great..!! Finally we learned Where with multiple conditions.

Great..!! Finally we learned Where with multiple conditions. What happen if few conditions are satisfied and few are not.

Hmm………. Lets first think on this.

You are right! It will not print those records because AND means each and every condition should be satisfied. If one of the condition is not satisfied then that record will be skipped and filtered out.

Great. What if I have to show such records when one of the condition is satisfied?

Lets learn about the OR operator.

What will be your general statement to print records which belongs to either USA or INDIA?

select records from Customers table where country is USA OR country is INDIA. isn’t it?

Yes! Lets convert to SQL

Select * FROM Customers
Where Country = "USA" OR Country = "INDIA";

OR condition will print all records which satisfied either condition 1 or condition 2.

Lets learn something can will be inverse to our condition. What is means?

It means if condition is satisfied then skip those records. Print those records which are not satisfying the condition. Ohhhh…. Nice.

NOT operator can be used in such situations when you want to inverse the condition.

SELECT * FROM Customers
Where NOT age > 30;

This will skip all records where age is greater than 30 and print remaining records.

Kudos!!! Finally we have finished Where clause with all the possible combinations of logical operators.

Example 3 :

I hope all above example clears the concept of Where clause and it’s use.

Linking

Where clause is used in Select , Update, Delete statement. We can use Where before Order By clause. Advance level Where having the Like , Between, IN keywords also. You can refer all the interview questions. Please refer all the links for understanding the Where in depth.

SQL Where Clause with Latest SQL Interview Questions 2023

Basic Where clause SQL Interview questions

1. What is the purpose of the WHERE clause in SQL?
2. How do you use the WHERE clause in a SELECT statement to retrieve data from a table?
3. What are some common comparison operators used in the WHERE clause?
4. How do you use logical operators in conjunction with the WHERE clause?
5. How can you use the WHERE clause in an UPDATE statement to modify data in a table?
6. How can you use the WHERE clause in a DELETE statement to delete data from a table?

Intermediate Where clause SQL Interview questions

1. How can you optimise query performance using the WHERE clause?
2. How can you implement data security measures using the WHERE clause?

Advanced Where clause SQL Interview questions

1. How can you use the WHERE clause to create dynamic queries?
2. What are some best practices or considerations when using the WHERE clause in SQL?

Questions covered from comment section

In this section we will cover the new questions as well as answers. Please comment the questions into comment section so we will include it here. Below are few questions which we thought should be covered.

Que : What are some best practices or considerations when using the WHERE clause in SQL?

Ans : While updating or deleting the records from the table we need to double-check conditions for accuracy. We need to use appropriate comparison operators and optimising conditions for performance.

Quiz

Click on this link to solve few MCQ questions on Where clause. You can practice all the syntax here or here.

Last question, Can we just print some records whose first name having “oh”. ohh……

Currently not but yeah.. SQL has that capability to do it. Lets learn something about the Like keyword in next session 🙂 .

Happy offline learning… Lets meet in next session.