SQL WHERE

Anytime you want to access certain rows within a table use the SQL WHERE clause. You could use it to:

• Show only certain rows (called filtering) with a SQL SELECT Statement
• Update row(s) with a specific condition
• Delete row(s) with a specific condition

The SQL WHERE clause is used as follows.

SQL WHERE Clause Syntax

<main-statement> WHERE <conditions>

Each condition tests column(s) using comparison operator(s). The following basic comparison operators are supported:

Operator	Description
=	Equal
<>	Not Equal
>	Greater Than
<	Less Than
>=	Greater Than Or Equal
<=	Less Than Or Equal

The comparison may involve literal value(s) that are constants like:

• 10
• 'Minnesota'
• -5006.3

Alphanumeric literals are enclosed in single quotes ('XXX').

SQL WHERE Clause Example 1

The product table contains four rows, including one row with a product_status_code with a value of Inactive. We will use the SQL WHERE clause to filter for Active rows:

product_nbr	product_name	product_status_code
1001	SQL Tool 1.0	Inactive
2001	SQL Tool 2.0 Light	Active
2002	SQL Tool 2.0 Professional	Active
2003	SQL Tool 2.0 Enterprise	Active

SELECT product_nbr, product_name, product_status_code FROM product WHERE product_status_code = 'Active'

Results from the execution of the SQL SELECT statement with the WHERE clause are as follows:

product_nbr	product_name	product_status_code
2001	SQL Tool 2.0 Light	Active
2002	SQL Tool 2.0 Professional	Active
2003	SQL Tool 2.0 Enterprise	Active

SQL WHERE Clause Example 2

The customer table contains five rows, including two rows that have credit_balance_amt greater than credit_limit_amt. We will use the SQL WHERE clause to filter for Active rows:

customer_nbr	customer_name	credit_limit_amt	credit_balance_amt
200-8889	Sandy Shores	7000.00	2100.00
301-7772	Pat Portabello	1000.00	1020.00
305-9999	Douglas Donovan	1000.00	999.00
400-1234	Edward Engle	2000.00	2000.00
500-1234	Fran Farckle	2000.00	2001.00

SELECT customer_nbr, customer_name, credit_limit_amt, credit_balance_amt FROM customer WHERE credit_balance_amt > credit_limit_amt

Results from the execution of the SQL SELECT statement with the WHERE clause are as follows: