SQL WHERE Clause

The WHERE Clause is used when you want to retrieve specific information from a table excluding other irrelevant data. For example, when you want to see the information about students in class 10th only then you do need the information about the students in other class. Retrieving information about all the students would increase the processing time for the query.

So SQL offers a feature called WHERE clause, which we can use to restrict the data that is retrieved. The condition you provide in the WHERE clause filters the rows retrieved from the table and gives you only those rows which you expected to see. WHERE clause can be used along with SELECT, DELETE, UPDATE statements.
Syntax of SQL WHERE Clause:

WHERE {column or expression} comparison-operator value

Syntax for a WHERE clause with Select statement is:

SELECT column_list FROM table-name
WHERE condition;

· column or expression - Is the column of a table or a expression

· comparison-operator - operators like = < > etc.

· value - Any user value or a column name for comparison

For Example: To find the name of a student with id 100, the query would be like:

SELECT first_name, last_name FROM student_details
WHERE id = 100;

Comparison Operators and Logical Operators are used in WHERE Clause. These operators are discussed in the next chapter.

NOTE: Aliases defined for the columns in the SELECT statement cannot be used in the WHERE clause to set conditions. Only aliases created for tables can be used to reference the columns in the table.
How to use expressions in the WHERE Clause?

Expressions can also be used in the WHERE clause of the SELECT statement.

For example: Lets consider the employee table. If you want to display employee name, current salary, and a 20% increase in the salary for only those products where the percentage increase in salary is greater than 30000, the SELECT statement can be written as shown below

SELECT name, salary, salary*1.2 AS new_salary FROM employee
WHERE salary*1.2 > 30000;

Output:

name	salary	new_salary
-----------	----------	----------------
Hrithik	35000	37000
Harsha	35000	37000
Priya	30000	360000

NOTE: Aliases defined in the SELECT Statement can be used in WHERE Clause.

---------------------------------------------------------------------------------------------------

The WHERE clause allows you to filter the results from an SQL statement - select, insert, update, or delete statement.

It is difficult to explain the basic syntax for the WHERE clause, so instead, we'll take a look at some examples.

Example #1

SELECT *
FROM suppliers
WHERE supplier_name = 'IBM';

In this first example, we've used the WHERE clause to filter our results from the suppliers table. The SQL statement above would return all rows from the suppliers table where the supplier_name is IBM. Because the * is used in the select, all fields from the suppliers table would appear in the result set.

Example #2

SELECT supplier_id
FROM suppliers
WHERE supplier_name = 'IBM'
or supplier_city = 'Newark';

We can define a WHERE clause with multiple conditions. This SQL statement would return all supplier_id values where the supplier_name is IBM or the supplier_city is Newark.

Example #3

SELECT suppliers.suppler_name, orders.order_id
FROM suppliers, orders
WHERE suppliers.supplier_id = orders.supplier_id
and suppliers.supplier_city = 'Atlantic City';

We can also use the WHERE clause to join multiple tables together in a single SQL statement. This SQL statement would return all supplier names and order_ids where there is a matching record in the suppliers and orders tables based on supplier_id, and where the supplier_city is Atlantic City.