WHERE
Use the WHERE clause to return only the records that satisfy a specified condition
Syntax
SELECT column1, column2, ...
FROM table_name
WHERE condition
SELECT salesperson#, amount_paid
FROM ar_invoices
WHERE amount_paid > 0
| salesperson# |
amount_paid
|
| 1 | 1.00
|
Sample Data
These are the tables we will use as an example:
SELECT *
FROM ar_invoices
| invoice# |
customer# |
salesperson# |
invoice_date |
invoice_amount |
amount_paid |
current_month_interest_charges |
total_interest_charges
|
| 1 | 1 | 1 | 2019-06-17 10:43:53.727 | 100.00 | 1.00 | 0.00 | 0.00
|
| 2 | 2 | 2 | 2019-05-28 10:43:53.727 | 50.00 | 0.00 | 5.00 | 10.00
|
| 3 | 3 | 2 | 2019-06-17 10:43:53.727 | 70.00 | 0.00 | 10.00 | 20.00
|
| 4 | 2 | 1 | 2019-07-07 10:43:53.727 | 80.00 | 0.00 | 0.00 | 0.00
|
SELECT *
FROM ar_salespeople
| salesperson# |
salesperson_name
|
| 1 | bob
|
| 2 | mary
|
| 3 | tim
|
WHERE Operators
| Operator |
Description |
| = |
Equals |
| > |
Greater Than |
| < |
Lesser Than |
| >= |
Greater Than or Equal |
| <= |
Lesser Than or Equal |
| <> or != |
Not Equal |
| BETWEEN |
Between a Certain Range (inclusive) |
| LIKE |
Search for a Pattern |
| IN |
To Specify Multiple Possible Values for a Column |
BETWEEN
Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2
SELECT salesperson#, invoice_amount
FROM ar_invoices
WHERE invoice_amount BETWEEN 60 AND 90
| salesperson# |
invoice_amount
|
| 2 | 70.00
|
| 1 | 80.00
|
LIKE
There are 2 wildcards used with the LIKE operator:
% - Represents none, one, or multiple characters_ - Represents a single character
Syntax
SELECT column1, column2, ...
FROM table_name
WHERE columnn LIKE pattern
SELECT salesperson#, total_interest_charges
FROM ar_invoices
WHERE total_interest_charges LIKE '_0.00'
| salesperson# |
total_interest_charges
|
| 2 | 10.00
|
| 2 | 20.00
|
IN
The IN operator is a shorthand for multiple OR conditions
Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...)
SELECT salesperson#, amount_paid
FROM ar_invoices
WHERE salesperson# IN (1)
| salesperson# |
amount_paid
|
| 1 | 1.00
|
| 1 | 0.00
|
SELECT salesperson#, amount_paid
FROM ar_invoices
WHERE salesperson# NOT IN (1)
| salesperson# |
amount_paid
|
| 2 | 0.00
|
| 2 | 0.00
|
~or~
Combine the IN operator with a SELECT statement
SELECT column_name(s)
FROM table_name
WHERE column_name IN (SELECT STATEMENT)
SELECT DISTINCT salesperson#, salesperson_name
FROM ar_salespeople
WHERE salesperson# IN (SELECT salesperson# FROM ar_invoices)
| salesperson# |
salesperson_name
|
| 1 | bob
|
| 2 | mary
|
The WHERE clause can be combined with the AND, OR, and NOT operators
| Operator |
Description |
| AND |
Displays a record if all the conditions seperated by AND are TRUE |
| OR |
Displays a record if any of the conditions seperated by OR is TRUE |
| NOT |
Displays a record if the condition(s) is NOT TRUE |
For Example:
AND
Syntax
SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND condition3 ...
SELECT salesperson#, invoice_amount
FROM ar_invoices
WHERE salesperson# = 1 AND invoice_amount > 90
| salesperson# |
invoice_amount
|
| 1 | 100.00
|
OR
Syntax
SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2 OR condition3 ...
SELECT customer#, invoice_amount
FROM ar_invoices
WHERE customer# = 1 OR customer# = 2
| customer# |
invoice_amount
|
| 1 | 100.00
|
| 2 | 50.00
|
| 2 | 80.00
|
NOT
Syntax
SELECT column1, column2, ...
FROM table_name
WHERE NOT condition
SELECT customer#, invoice_amount
FROM ar_invoices
WHERE NOT customer# = 2
| customer# |
invoice_amount
|
| 1 | 100.00
|
| 3 | 70.00
|