SQL WHERE clause

has average rating 8 out of 10. Total 2 users rated.

<<PreviousNext>>

Description

The basic form of the SELECT statement is SELECT-FROM-WHERE block. In an SELECT statement WHERE clause is optional. Using SELECT without a WHERE clause is useful for browsing data from tables. In a WHERE clause, you can specify a search condition (logical expression) that has one or more conditions. When the condition (logical expression) evaluates to true the WHERE clause filter unwanted rows from the result. Here is the syntax :

Syntax

SELECT <column_list> 
FROM < table name >
WHERE <condition>;

Parameters

Types of conditions

Comparison Operators

Example : Using the WHERE clause

The following query display the employee_id, first_name, last_name, department_id of employees whose departmet_id=100 :

Sample table : employees

SELECT employee_id, first_name, 
last_name, department_id
FROM employees 
WHERE department_id=100;

Output

The following query displays the employee_id, job_id, salary of employees whose last_name = 'Lorentz'.
Note : Character strings are enclosed by quotation marks. Character values are case-sensitive for some database.

SELECT employee_id, job_id, salary
FROM employees
WHERE last_name = 'Lorentz';

Output

Example : WHERE clause using comparison conditions

The following query displays the employee_id, first_name, last_name and salary of employees whose salary is greater than or equal to 4000 :

Sample table : employees

SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary>=4000;

Output

Example : WHERE clause using expression

The following query displays the first_name, last_name , salary and (salary+(salary*commission_pct)) as Net Salary of employees whose Net Salary is in the range 10000 and 15000 and who gets atleast a percentage of commission_pct.

Sample table : employees

SELECT first_name,last_name,salary,
(salary+(salary*commission_pct)) "Net Salary" 
FROM employees 
WHERE
(salary+(salary*commission_pct)) 
BETWEEN 10000 AND 15000 
AND commission_pct>0

Output

Example : WHERE clause using BETWEEN condition

The BETWEEN condition is used to test for values in a list.

The following query displays the employee_id, first_name, last_name and salary of employees whose salary is greater than or equal to 4000 and less than equal to 6000 where 4000 is lower limit and 6000 is upper limit of the salary.

Sample table : employees

SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary BETWEEN 4000 AND 6000;

Output

Example : WHERE clause using IN condition

The IN condition is used to test for values in a list.

The following query displays the employee_id, first_name, last_name, department_id and salary of employees whose department_id 60, 90 or 100.

Sample table : employees

SELECT employee_id, first_name, last_name, 
department_id, salary 
FROM employees
WHERE department_id IN(60,90,100);

Output

Example : WHERE clause using LIKE condition

The LIKE condition is used to perform wildcard searches of string values. Search condition can contain either numbers or literal characters, _ denotes one character and % denotes zero or many characters.

The following query displays the employee_id, first_name, last_name and salary of employees whose first_name starting with 'S'.

Sample table : employees

SELECT employee_id, first_name, last_name, 
department_id, salary
FROM employees
WHERE first_name LIKE('S%');

Output

Example : WHERE clause using NULL condition

IS NULL operator is used to test for nulls.

The following query displays the employee_id, first_name, last_name and salary of employees whose department_id is null.

Sample table : employees

SELECT employee_id, first_name, last_name, 
department_id, salary
FROM employees
WHERE department_id IS NULL;

Output

SELECT employee_id, first_name, last_name, 
department_id, salary
FROM employees
WHERE first_name LIKE('S%')
AND salary>=4000;

SELECT employee_id, first_name, last_name, 
department_id, salary
FROM employees
WHERE first_name LIKE('S%')
OR first_name LIKE('A%')

SELECT employee_id, first_name, last_name, 
department_id, salary
FROM employees
WHERE department_id
NOT IN (90, 60, 100);

photo credit: Nemo's great uncle via photopin cc

<<PreviousNext>>