SQL Where Clause

In the previous article we have seen about how to write effective SELECT statement to get data from database. The SQL WHERE clause is powerful & one of the most commonly used clause use in day to day life. Basically It allows you to filter out the result set and your get limited data based on condition. The WHERE clause can be used along with the SQL SELECT statement, SQL INSERT statement, SQL UPDATE statement, or SQL DELETE statement.

SQL WHERE clause syntax

It is the most frequently used SQL command and following is the general syntax of SELECT command with WHERE clause to fetch data from table:

SELECT column_name(s)
FROM table_name
WHERE column_name operator value

• [SELECT column_name(s)] – The result set retrieve all entered columns data. It may contain multiple columns. If pass ‘*’ then it results all columns from selected table.
• [FROM table_name] – This is a table name from which result set is retrieved.
• [WHERE] – This Clause used to filter out the result data set based on “condition”. You can specify any condition using WHERE clause. It supports AND or OR operators to specify more than one condition in single query.

The WHERE clause works similar to IF condition used in the coding language. This clause is used to compare given value with the field value available in MySQL table. If given value from outside is equal to the available field value in MySQL table then it returns that row.

Let’s take example A=100 and B=200 and see how operators works with this example:

Operator	Description	Example
=	This operator checks the values of both fields are same.	[A = B] is False
!=	This operator checks the values of both fields are not same.	[A != B] is True.
<	This checks the value of left operand is less than the value of right operand. If this condition satisfies then result is True.	[A < B] is True.
>	This checks the value of left operand is greater than the value of right operand. If this condition satisfies then result is True.	[A > B] is not True.
<=	This checks the value of left operand is less than equal to the value of right operand. If this condition satisfies then result is True.	[A <= B] is True.
>=	This checks the value of left operand is greater than equal to the value of right operand. If this condition satisfies then result is True.	[A >= B] is not True.

Examples:
Let’s see upon executing following query on Employee table resulting following list:

SELECT * FROM Employee

Following is the result after executing the above SQL query:

Employee_Id	Full_Name	Gender	City	Phone
1	Tanya Smith	Female	Stavanger	9123456789
2	Janet Tampi	Female	Chicago	9876543210
3	Nilsen Phil	Male	NULL	9112233445
4	Nilsen Jones	Male	Atlantic	9988776655
5	Peter Willams	Male	Sandnes	9944552299

Let’s now look at a practical example.

SELECT * FROM Employee
WHERE Employee_Id = 2

Following is the filtered result after executing the above SQL query:

Employee_Id	Full_Name	Gender	City	Phone
2	Janet Tampi	Female	Chicago	9876543210

AND logical operator:

SELECT * FROM Employee
WHERE Full_Name = ‘Nilsen Jones’
AND Gender = 'Male'

Following is the filtered result after executing the above SQL query using AND Logical Operator:

Employee_Id	Full_Name	Gender	City	Phone
4	Nilsen Jones	Male	Atlantic	9988776655

OR logical operator:

SELECT * FROM Employee
WHERE Employee_Id = 1 OR Employee_Id = 2

Following is the filtered result after executing the above SQL query using OR Logical Operator:

Employee_Id	Full_Name	Gender	City	Phone
1	Tanya Smith	Female	Stavanger	9123456789
2	Janet Tampi	Female	Chicago	9876543210

IN logical operator:

SELECT * FROM Employee
WHERE City IN (‘Chicago’, ‘Atlantic’)

Following is the filtered result after executing the above SQL query using IN Logical Operator:

Employee_Id	Full_Name	Gender	City	Phone
2	Janet Tampi	Female	Chicago	9876543210
4	Nilsen Jones	Male	Atlantic	9988776655

NOT IN logical operator:

SELECT * FROM Employee
WHERE City NOT IN (‘Chicago’, ‘Atlantic’)

Following is the filtered result after executing the above SQL query using NOT IN Logical Operator:

Employee_Id	Full_Name	Gender	City	Phone
1	Tanya Smith	Female	Stavanger	9123456789
3	Nilsen Phil	Male	NULL	9112233445
5	Peter Willams	Male	Sandnes	9944552299

BETWEEN logical operator:

SELECT * FROM Employee
WHERE Employee_Id BETWEEN 2 AND 4

Following is the filtered result after executing the above SQL query using

Employee_Id	Full_Name	Gender	City	Phone
2	Janet Tampi	Female	Chicago	9876543210
3	Nilsen Phil	Male	NULL	9112233445
4	Nilsen Jones	Male	Atlantic	9988776655

LIKE logical operator:

SELECT * FROM Employee
WHERE Full_Name LIKE 'N%'

Ending Wildcard:

Following is the filtered result after executing the above SQL query using LIKE Logical Operator with Ending Wildcard:

Employee_Id	Full_Name	Gender	City	Phone
3	Nilsen Phil	Male	NULL	9112233445
4	Nilsen Jones	Male	Atlantic	9988776655

Leading Wildcard:

SELECT * FROM Employee
WHERE Full_Name LIKE '%s'

Following is the filtered result after executing the above SQL query using LIKE Logical Operator with Leading Wildcard:

Employee_Id	Full_Name	Gender	City	Phone
4	Nilsen Jones	Male	Atlantic	9988776655
5	Peter Willams	Male	Sandnes	9944552299

Multiple Wildcards:

Find any record which has anywhere, the letter ‘a’.

SELECT * FROM Employee
WHERE Full_Name LIKE '%a%'

Following is the filtered result after executing the above SQL query using LIKE Logical Operator with Middle Wildcard:

Employee_Id	Full_Name	Gender	City	Phone
1	Tanya Smith	Female	Stavanger	9123456789
2	Janet Tampi	Female	Chicago	9876543210
5	Peter Willams	Male	Sandnes	9944552299

Single Character Wildcard:

Find the record which has Name started with ‘J’ and has ‘a’ character anywhere in record.

SELECT * FROM Employee
WHERE Full_Name LIKE 'J%a%'

Following is the filtered result after executing the above SQL query using LIKE Logical Operator with Single Character Wildcard:

Employee_Id	Full_Name	Gender	City	Phone
2	Janet Tampi	Female	Chicago	9876543210

 Summary:

• The SQL WHERE clause is used to filter the number of rows in result set based on condition.
• The WHERE clause can be used with a SELECT, UPDATE or DELETE query.
• In the WHERE clause allows you to write the condition using logical operators like AND, OR, LIKE, BETWEEN, IN, NOT IN etc.
• Using AND operator means the all condition should satisfies.
• Using OR operator means the any condition satisfies.
• Using IN keyword is basically used to choose rows matching a list of values.

If you enjoy reading this article please make sure to share it with your friends. I would like all of you to please join this discussion and add more valuable points to it. Thanks.