SQL SELECT WHERE Clause

In this SQL section, we will help you understand the concept of the SQL SELECT WHERE clause with a few examples.

The WHERE clause in SELECT query displays those records as per the conditions returned in the queries. The WHERE clause filters the table data based on the expression in the given queries.

We can use comparison, logic, and other operation with the WHERE clause in the queries.

The WHERE clause syntax with the SELECT statement is as follows:

SELECT * FROM Table_Name WHERE conditions;

The WHERE clause in the SELECT statement is used to fetch the records from the table.

Let’s understand the SQL SELECT WHERE Clause with the help of examples.

Assume the following table, which has certain data.

Table Name 1- D_Students

Student_Id	Student_Name	First_ Semester	Second_ Semester	Third_ Semester	Fourth_ Semester	Fifth_ Semester	Sixth_ Semester	Total	Department_ Id
202111	Vineeta Sharma	93	88	85	85	95	92	90	5
202112	Vaibhav Lokhande	85	90	92	80	85	82	86	2
202113	Yash Dhull	90	88	94	87	85	90	89	3
202114	Sonali Patole	95	90	92	88	92	90	91	4
202115	Amit Sonar	85	83	82	86	92	84	85	1
202116	Meena Mishra	78	75	80	74	85	77	78	3
202117	Mahesh Kumbhar	75	83	75	78	80	76	82	5
202118	Sakshi Patil	80	78	74	78	80	77	83	2
202119	Sopan Bhore	70	68	75	75	80	80	80	2
202220	Prajwal Lokhande	80	85	85	75	78	80	86	4
202221	Anuja Wanare	85	88	86	82	84	85	85	4
202222	Venkatesh Iyer	90	89	87	90	92	91	90	3
202223	Anushka Sen	70	75	71	74	80	78	80	1
202224	Aakash Jain	80	75	72	74	85	80	83	2
202225	Akshay Agarwal	85	83	78	88	90	82	89	3
202226	Shwetali Bhagwat	90	83	85	88	90	80	86	1
202227	Priya Wagh	80	83	85	80	82	85	87	4
202228	Saurabh Sangale	85	83	80	90	84	84	89	5
202229	Manthan Koli	85	75	84	78	82	80	86	2
202230	Mayur Jain	80	88	87	90	92	90	88	1

Table Name 2- Department

Department_Id	Department_Name
1	Computer Engineering
2	Information Technology
3	Mechanical Engineering
4	Automobile Engineering
5	Civil Engineering
6	Electrical Engineering
7	Electronics and Tele-Communication Engineering

SELECT WHERE Clause with the Comparison Operators

Example 1: Execute a query to retrieve the information from the table where the second_semester percentage is 75.

SELECT Student_Id, Student_Name, First_Semester, Second_Semester, Third_Semester, Fourth_Semester, Fifth_Semester, Sixth_Semester, Total, Department_Id FROM D_Students WHERE Second_Semester = 75;  

In the above SQL SELECT WHERE clause example, we have displayed the student's information from the D_Students table whose second_semester percentage is 75. Here, we have used an equal comparison operator in the query.

The following output is as follows:

Student_Id	Student_Name	First_ Semester	Second_ Semester	Third_ Semester	Fourth_ Semester	Fifth_ Semester	Sixth_ Semester	Total	Department_ Id
202116	Meena Mishra	78	75	80	74	85	77	83	3
202223	Anushka Sen	70	75	71	74	80	78	80	1
202224	Aakash Jain	80	75	72	74	85	80	83	2
202229	Manthan Koli	85	75	84	78	82	80	86	2

Example 2: Execute a query to retrieve the information from the table where the third_semester percentage is greater than 83.

SELECT Student_Id, Student_Name, First_Semester, Second_Semester, Third_Semester, Fourth_Semester, Fifth_Semester, Sixth_Semester, Total, Department_Id FROM D_Students WHERE Third_Semester > 83;   

In the above SQL SELECT WHERE clause example, we have displayed the student's information from the table whose third_semester percentage is greater than 83. Here, we have used a greater than comparison operator in the query.

The following output is as follows:

Student_Id	Student_Name	First_ Semester	Second_ Semester	Third_ Semester	Fourth_ Semester	Fifth_ Semester	Sixth_ Semester	Total	Department_ Id
202111	Vineeta Sharma	93	88	85	85	95	92	90	5
202112	Vaibhav Lokhande	85	90	92	80	85	82	86	2
202113	Yash Dhull	90	88	94	87	85	90	89	3
202114	Sonali Patole	95	90	92	88	92	90	91	4
202220	Prajwal Lokhande	80	85	85	75	78	80	86	4
202221	Anuja Wanare	85	88	86	82	84	85	85	4
202222	Venkatesh Iyer	90	89	87	90	92	91	90	3
202226	Shwetali Bhagwat	90	83	85	88	90	80	86	1
202227	Priya Wagh	80	83	85	80	82	85	87	4
202229	Manthan Koli	85	75	84	78	82	80	86	2
202230	Mayur Jain	80	88	87	90	92	90	88	1

Example 3: Execute a query to retrieve the information from the table where the fourth_semester percentage is less than equal to 85.

SELECT Student_Id, Student_Name, First_Semester, Second_Semester, Third_Semester, Fourth_Semester, Fifth_Semester, Sixth_Semester, Total, Department_Id FROM D_Students WHERE Fourth_Semester <= 85;  

In the above SQL SELECT WHERE clause example, we have displayed the student's information from the table whose fourth_semester percentage is less than equal to 85. Here, we have used less than equal to the comparison operator in the query.

The following output is as follows:

Student_Id	Student_Name	First_ Semester	Second_ Semester	Third_ Semester	Fourth_ Semester	Fifth_ Semester	Sixth_ Semester	Total	Department_ Id
202111	Vineeta Sharma	93	88	85	85	95	92	90	5
202112	Vaibhav Lokhande	85	90	92	80	85	82	86	2
202116	Meena Mishra	78	75	80	74	85	77	78	3
202117	Mahesh Kumbhar	75	83	75	78	80	76	82	5
202118	Sakshi Patil	80	78	74	78	80	77	83	2
202119	Sopan Bhore	70	68	75	75	80	80	80	2
202220	Prajwal Lokhande	80	85	85	75	78	80	86	4
202221	Anuja Wanare	85	88	86	82	84	85	85	4
202223	Anushka Sen	70	75	71	74	80	78	80	1
202224	Aakash Jain	80	75	72	74	85	80	83	2
202227	Priya Wagh	80	83	85	80	82	85	87	4
202229	Manthan Koli	85	75	84	78	82	80	86	2

SELECT WHERE Clause with the Logical Operators

Example 1: Execute a query to retrieve the information from the table where fourth_semester is less than 80 OR fifth_semester is greater than 85.

SELECT Student_Id, Student_Name, First_Semester, Second_Semester, Third_Semester, Fourth_Semester, Fifth_Semester, Sixth_Semester, Total, Department_Id FROM D_Students WHERE Fourth_Semester < 80 OR Fifth_Semester > 85;

In the above SQL SELECT WHERE clause example, we have displayed the student's information from the table whose fourth_semester percentage is less than 80 OR fifth_semester percentage is greater than 85.

The following output is as follows:

Student_Id	Student_Name	First_ Semester	Second_ Semester	Third_ Semester	Fourth_ Semester	Fifth_ Semester	Sixth_ Semester	Total	Department_ Id
202111	Vineeta Sharma	93	88	85	85	95	92	90	5
202114	Sonali Patole	95	90	92	88	92	90	91	4
202115	Amit Sonar	85	83	82	86	92	84	85	1
202116	Meena Mishra	78	75	80	74	85	77	78	3
202117	Mahesh Kumbhar	75	83	75	78	80	76	82	5
202118	Sakshi Patil	80	78	74	78	80	77	83	2
202119	Sopan Bhore	70	68	75	75	80	80	80	2
202220	Prajwal Lokhande	80	85	85	75	78	80	86	4
202222	Venkatesh Iyer	90	89	87	90	92	91	90	3
202223	Anushka Sen	70	75	71	74	80	78	80	1
202224	Aakash Jain	80	75	72	74	85	80	83	2
202225	Akshay Agarwal	85	83	78	88	90	82	89	3
202226	Shwetali Bhagwat	90	83	85	88	90	80	86	1
202229	Manthan Koli	85	75	84	78	82	80	86	2
202230	Mayur Jain	80	88	87	90	92	90	88	1

Example 2: Execute a query to retrieve the information from the table where fourth_semester is greater than 80 and fifth_semester is greater than 85.

SELECT Student_Id, Student_Name, First_Semester, Second_Semester, Third_Semester, Fourth_Semester, Fifth_Semester, Sixth_Semester, Total, Department_Id FROM D_Students WHERE Fourth_Semester > 80 AND Fifth_Semester > 85;

In the above SQL SELECT WHERE clause example, we have displayed the student's information from the table whose fourth_semester percentage is greater than 80 and fifth_semester percentage is greater than 85.

The following output is as follows:

Student_Id	Student_Name	First_ Semester	Second_ Semester	Third_ Semester	Fourth_ Semester	Fifth_ Semester	Sixth_ Semester	Total	Department_ Id
202111	Vineeta Sharma	93	88	85	85	95	92	90	5
202114	Sonali Patole	95	90	92	88	92	90	91	4
202115	Amit Sonar	85	83	82	86	92	84	85	1
202222	Venkatesh Iyer	90	89	87	90	92	91	90	3
202225	Akshay Agarwal	85	83	78	88	90	82	89	3
202226	Shwetali Bhagwat	90	83	85	88	90	80	86	1
202230	Mayur Jain	80	88	87	90	92	90	88	1

Example 3: Execute a query to retrieve the information from the table where the Total field starts with the number 8.

SELECT Student_Id, Student_Name, First_Semester, Second_Semester, Third_Semester, Fourth_Semester, Fifth_Semester, Sixth_Semester, Total, Department_Id FROM D_Students WHERE Total LIKE '8%';

In the above SQL SELECT WHERE clause example, we have displayed the student's information from the table where total student percentages start with the number 8.

The following output is as follows:

Student_Id	Student_Name	First_ Semester	Second_ Semester	Third_ Semester	Fourth_ Semester	Fifth_ Semester	Sixth_ Semester	Total	Department_ Id
202112	Vaibhav Lokhande	85	90	92	80	85	82	86	2
202113	Yash Dhull	90	88	94	87	85	90	89	3
202115	Amit Sonar	85	83	82	86	92	84	85	1
202117	Mahesh Kumbhar	75	83	75	78	80	76	82	5
202118	Sakshi Patil	80	78	74	78	80	77	83	2
202119	Sopan Bhore	70	68	75	75	80	80	80	2
202220	Prajwal Lokhande	80	85	85	75	78	80	86	4
202221	Anuja Wanare	85	88	86	82	84	85	85	4
202223	Anushka Sen	70	75	71	74	80	78	80	1
202224	Aakash Jain	80	75	72	74	85	80	83	2
202225	Akshay Agarwal	85	83	78	88	90	82	89	3
202226	Shwetali Bhagwat	90	83	85	88	90	80	86	1
202227	Priya Wagh	80	83	85	80	82	85	87	4
202228	Saurabh Sangale	85	83	80	90	84	84	89	5
202229	Manthan Koli	85	75	84	78	82	80	86	2
202230	Mayur Jain	80	88	87	90	92	90	88	1

Example 4: Write a sub-query to retrieve the information from the table where department name is ‘Computer Engineering’, ‘Information Technology’, and Mechanical Engineering.

SELECT Student_Id, Student_Name, First_Semester, Second_Semester, Third_Semester, Fourth_Semester, Fifth_Semester, Sixth_Semester, Total, Department_Id FROM D_Students WHERE Department_Id IN (SELECT Department_Id FROM Department WHERE Department_Name IN ('Computer Engineering', 'Information Technology', 'Mechanical Engineering'));

In the above SQL SELECT WHERE clause example, we have displayed the student's information from the table where the student department name is 'Computer Engineering', 'Information Technology', and 'Mechanical Engineering'.

The following output is as follows:

Student_Id	Student_Name	First_ Semester	Second_ Semester	Third_ Semester	Fourth_ Semester	Fifth_ Semester	Sixth_ Semester	Total	Department_ Id
202115	Amit Sonar	85	83	82	86	92	84	85	1
202223	Anushka Sen	70	75	71	74	80	78	80	1
202226	Shwetali Bhagwat	90	83	85	88	90	80	86	1
202230	Mayur Jain	80	88	87	90	92	90	88	1
202112	Vaibhav Lokhande	85	90	92	80	85	82	86	2
202118	Sakshi Patil	80	78	74	78	80	77	83	2
202119	Sopan Bhore	70	68	75	75	80	80	80	2
202224	Aakash Jain	80	75	72	74	85	80	83	2
202229	Manthan Koli	85	75	84	78	82	80	86	2
202113	Yash Dhull	90	88	94	87	85	90	89	3
202116	Meena Mishra	78	75	80	74	85	77	78	3
202222	Venkatesh Iyer	90	89	87	90	92	91	90	3
202225	Akshay Agarwal	85	83	78	88	90	82	89	3

Example 5: Write a query to retrieve the information of those students from the table whose student name didn't start with the letter 'M'.

SELECT Student_Id, Student_Name, First_Semester, Second_Semester, Third_Semester, Fourth_Semester, Fifth_Semester, Sixth_Semester, Total, Department_Id FROM D_Students WHERE NOT Student_Name LIKE 'M%';

In the above SQL SELECT WHERE clause example, we have displayed the student's information of those students from the table whose student's name didn't start with the letter 'M'.

The following output is as follows:

Student_Id	Student_Name	First_ Semester	Second_ Semester	Third_ Semester	Fourth_ Semester	Fifth_ Semester	Sixth_ Semester	Total	Department_ Id
202111	Vineeta Sharma	93	88	85	85	95	92	90	5
202112	Vaibhav Lokhande	85	90	92	80	85	82	86	2
202113	Yash Dhull	90	88	94	87	85	90	89	3
202114	Sonali Patole	95	90	92	88	92	90	91	4
202115	Amit Sonar	85	83	82	86	92	84	85	1
202118	Sakshi Patil	80	78	74	78	80	77	83	2
202119	Sopan Bhore	70	68	75	75	80	80	80	2
202220	Prajwal Lokhande	80	85	85	75	78	80	86	4
202221	Anuja Wanare	85	88	86	82	84	85	85	4
202222	Venkatesh Iyer	90	89	87	90	92	91	90	3
202223	Anushka Sen	70	75	71	74	80	78	80	1
202224	Aakash Jain	80	75	72	74	85	80	83	2
202225	Akshay Agarwal	85	83	78	88	90	82	89	3
202226	Shwetali Bhagwat	90	83	85	88	90	80	86	1
202227	Priya Wagh	80	83	85	80	82	85	87	4
202228	Saurabh Sangale	85	83	80	90	84	84	89	5