SQL: WHERE Clause

Where Clause

 

Introduction

 

The WHERE clause consists of the keyword WHERE followed by a search condition that specifies the rows to be retrieved.

  • The WHERE clause is like a search
  • The body of the WHERE clause will be a set of expressions that can evaluate TRUE OR FALSE
  • TRUE or FALSE is called a “Boolean expression”
  • If the expression is TRUE, it will return a result set

 

Example Table

Employee Table

EmpID FirstName LastName City Salary
2458 Nigel Crasto Hyberbad 100000
4857 May Smith Orlando 200000
5874 Dan Hurley Denver 300000
8574 Tim Porter Seattle 400000

 

Comparison operators

Operator Description Example
= Equal to Author = ‘Alcott’
<> Not equal to (many DBMSs accept != in addition to <>) Dept <> ‘Sales’
> Greater than Hire_Date > ‘2012-01-31’
< Less than Bonus < 50000.00
>= Greater than or equal Dependents >= 2
<= Less than or equal Rate <= 0.05
BETWEEN Between an inclusive range Cost BETWEEN 100.00 AND 500.00
LIKE Match a character pattern First_Name LIKE ‘Will%’
IN Equal to one of multiple possible values DeptCode IN (101, 103, 209)
IS or IS NOT Compare to null (missing data) Address IS NOT NULL
IS NOT DISTINCT FROM Is equal to value or both are nulls (missing data) Debt IS NOT DISTINCT FROM – Receivables
AS Used to change a field name when viewing results SELECT employee AS ‘department1’

 

Example:

  1. Less than:

SELECT FirstName, City, Salary

FROM Employee

WHERE Salary < 300000;

FirstName City Salary
Nigel Hyperbad 100000
May Orlando 200000

 

  1. Less than equal to:

SELECT FirstName, City, Salary

FROM Employee

WHERE Salary <= 300000;

FirstName City Salary
Nigel Hyperbad 100000
May Orlando 200000
Dan Denver 300000

 

  1. Greater than:

SELECT FirstName, City, Salary

FROM Employee

WHERE Salary > 300000;

FirstName City Salary
Tim Seattle 400000

 

  1. Greater than equal to:

SELECT FirstName, City, Salary

FROM Employee

WHERE Salary >= 300000;

FirstName City Salary
Dan Denver 300000
Tim Seattle 400000

 

  1. Equal to:

SELECT FirstName, City, Salary

FROM Employee

WHERE Salary = 300000;

FirstName City Salary
Dan Denver 300000

 

  1. Not Equal to:

SELECT FirstName, City, Salary

FROM Employee

WHERE Salary <> 300000;

FirstName City Salary
Nigel Hyperbad 100000
May Orlando 200000
Tim Seattle 400000

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s