SQL WHERE

SQL WHERE Clause

  1. The WHERE clause is used to get a filtered resultset.
  2. The WHERE clause is used to extract only those records that satisfy a specific condition.
When you want to retrieve specific data from a table excluding all other data WHERE clause is used. For example, when you want the information about students in class 10th only then you don't 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.

WHERE Syntax

WHERE {column or expression} operator value

WHERE Syntax with SELECT Statement

SELECT column1, column2, ...
FROM table_name
WHERE condition;
  1. column or expression - an expression or name of column in table.
  2. operator - operators such as =, <, >, BETWEEN, LIKE, IN etc.
  3. value - Any user value or a column name for comparison
NOTE: The WHERE clause is not only used in SELECT statement, it is also used in UPDATE, DELETE etc.
To understand WHERE clause syntax an various uses consider the following demo database table "Student".
  id | first_name | last_name | age |   subject   
----+------------+-----------+-----+-------------
  1 | Carol      | Dentist   |  15 | FoodTech
  2 | Sam        | Wick      |  19 | Architect
  3 | Daryl      | Dixon     |  13 | Mathematics
  4 | Rick       | Grimes    |  14 | Physics
  5 | Morgan     | Dee       |  15 | Wrestling

WHERE Clause Example 1

The following query is used to find the name of student with id 100.
SELECT first_name, last_name FROM student
WHERE id = 100;
In above query, "id" is coulmn name, "=" is comparison operator and 100 is value as mentioned the WHERE syntax.

Output

 first_name | last_name 
------------+-----------
 Carol      | Dentist
(1 row)

WHERE Clause Example 2

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.
This means you cannot write a query like this
SELECT id as identifier, first_name, last_name FROM student
WHERE identifier = 100;
It gives an error message "column does not exist".

Output

postgres=> select id as identifier, first_name, last_name from s where identifier=1;
ERROR:  column "identifier" does not exist
LINE 1: ...as identifier, first_name, last_name from s where identifier...
                                                             ^
postgres=> 

How to use expressions in the WHERE Clause?

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

Example:

Lets consider the employee table as given below.
 eno |    dept     | salary  
-----+-------------+---------
   2 | Mathematics | 3000.98
   3 | Physics     | 2170.89
   4 | Chemistry   | 3170.89
   5 | Physics     | 4170.89
   1 | Mathematics | 1000.98
The following SQL query displays employee number, current salary, and a 10% increase in the salary for only those employees where the percentage increase in salary is greater than 30000.
SELECT eno, salary, salary*0.1 AS new_salary FROM employee
WHERE salary*0.1 > 3000;

Output:

 eno | salary  | new_salary 
-----+---------+------------
   2 | 3000.98 |   3301.078
   4 | 3170.89 |   3487.979
   5 | 4170.89 |   4587.979
(3 rows)

Pre - SQL Aliases Next - SQL Operators

Comments