SQL Where clause is basically used to fetch the data from a particular table or multiple tables by joining tables. Only when the SQL where clause condition is processed, the database will return a value from the table specified by filtering the entries (records in the table). In short, the SQL Where clause in the SQL queries is used to fetch the data either from a single table or joining with multiple tables. SQL Where clause can be used in SELECT, UPDATE, DELETE or INSERT statements.
Considering an example will help you to understand where clause even better.
You are the Database administrator for an Organization with 100 employees. Retrieving details of these employees like their age, salary, gender, name, employee id and etc., details will be simple, as there isn’t any actual need for you to place any kind of conditions. But, let us consider a condition like you have to display the list of employees whose salary is more than $3000, then you need to apply the condition like:
"select * from employee where salary > 3000;".
In this way, you can make use of SQL where clause that helps you to extract specific information from the tables.
SQL WHERE SYNTAX:
EXAMPLE: Consider the following GRADE table which has students records.
Operators used in WHERE clause
#1. = (Equal) operator
Example: Display the first name and last name of the student whose id is 6
Select First_Name, Last_Name, Id From grade Where id = 6 ;
Note: All string values should be specified within single quotes (‘ ‘) and numbers as it is.
#2. < > (Greater than or Less than ) operator
Example: Display students with Test1 grades greater than 85
Select First_Name, Last_Name, Test1 From grade Where Test1 > 85;
Using OR & AND along with Greater than or Less than
Example: Display students with Test1 grade greater than 70 or Test2 grade greater than 80
Select First_Name, Last_Name, Test1, Test 2 From grade where Test1 > 70 or Test2 > 80;
Example: Display students with Test1 grade greater than 60 and Test2 grade greater than 80
Select First_Name, Last_Name, Test1, Test 2 From grade where Test1 > 60 or Test2 > 80;
In SQL, AND has precedence over OR. Therefore, when a SQL WHERE clause includes both AND(s) and OR(s), you should include parentheses to clarify your requirements.
#3. BETWEEN operator
Between operator finds the value specified within the range.
Example: Display students with Test 1 grades between 75 and 85
Select First_Name, Last_Name, Test1 From grade where Test1 >= 75 and Test1<= 85;
Select First_Name, Last_Name, Test1 From grade where Test1 between 75 and 85;
Note: In BETWEEN operator the lower limit is used first and then the higher limit or else it will return any random rows.
#4. LIKE operator
Like operator is primarily used to design for specified column characters.
Like operator finds the string or character by filtering the certain criteria. We use “%” and “_” symbols where “%” matches many characters and “_” matches the exactly one character.
Example: Display the First name who’s name starts with “Ar”
Select First_Name, Last_Name From grade where First_Name like 'Ar%';
Example: Display the First name who’s name ends with “j”
Select First_Name, Last_Name From grade where First_Name like '%j';
Example: Display students First name which starts with “J” and ends with “N”
Select First_Name, Last_Name From grade where First_Name like 'J__n';
#5. IN operator
In SQL where clause we use IN operator to specify multiple values
Example: Display the students who have taken Tests from either ClearLake or Downtown Campus
Select * From grade Where Location IN ('ClearLake' ,'Downtown');
Hope you liked our article on SQL Where Clause and how to use it. We will be bringing many other tutorials for you, that helps you to strengthen your basics of SQL. Stay tuned for more articles like SQL where clause.