Written by Ananya Chandra on January 30, 2014 in SQL

SQL WHERE Clause – Things beginners must know

GET ALERTS:

Get our Latest updates delivered to your mailbox!

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.

sql where statement

Example:

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:

WHERE condition;

EXAMPLE: Consider the following GRADE table which has students records.

Id First_Name Last_Name Test1 Test2 Test3 Location
1 Aria M 80 75 90 ClearLake
2 Raj D 86 80 98 Downtown
3 Fed F 78 80 92 MainCampus
4 Ted W 60 76 98 Pearland
5 John X 77 56 99 MainCampus
6 Mia O 87 89 79 ClearLake
7 Rose A 67 98 86 Downtown

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;

OR

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.

{ 0 comments… add one now }

Leave a Comment

Previous post:

Next post: