Lesson 12: Multiple Conditions in SQL

You can add multiple criteria to your Where clauses by using "and" or "or."


Syntax:

Select */fieldname<mask> ... 
from tablename ... 
where fieldname =/!=/<> ... value
and/or
fieldname =/!=/<>... value
and/or
fieldname =/!=/<> ... value 
order by fieldname <desc> ...

Explanation:

• The and tells SQL to retrieve the record if both conditions are met.
• The or tells SQL to retrieve the record if either condition is met.
• The or is less restrictive and retrieves more records.
• If multiple ands and ors are used, the ands are evaluated first, and then the ors.
• Use parentheses to change precedence (the order of evaluation).

Examples:Retrieve all vendors who are located in Chicago and have a current balance over 1,500 dollars. Order the results by current balance.

1. In the SQL text box, type:

Select Name, City, CurrBal [-ZZ,ZZZ.99]
from TrnVendor
where City = 'Chicago'
and CurrBal > 1500
order by CurrBal;

2. Execute the SQL statement.

Results:
 

Name	City	CurrBal
Make Shift Tilts	Chicago	1,793.23
Music Maker	Chicago	4,109.14

---

Retrieve all vendors who are located in Chicago or who have a current balance over 1,500 dollars.

1. In the SQL text box, type:

Select Name, City, CurrBal [-ZZ,ZZZ.99]
from TrnVendor
where City = 'Chicago'
or CurrBal > 1500
order by CurrBal;

2. Execute the SQL statement.

Name	City	CurrBal
Distant Horizons	Chicago	26.98
BedMakers Linen	Chicago	500.00
Paltry Play	Chicago	1,000.00
Spot Out	Detroit	1,606.54
Narrow Nest	Toledo	1,609.00
No Waste Disposal	Park Ridge	1,720.98
Make Shift Tilts	Chicago	1,793.23
Legal Lookup	Houston	3,215.16
Music Maker	Chicago	4,109.14
Softer Software	Monroe	9,873.67

---

The next two SQL statements use exactly the same syntax except that the order of precedence is different (note parentheses in second example).
Retrieve all vendors from Chicago whose expense account is 4110 or any vendor with a current balance over 1200.

1. In the SQL text box, type:

Select City, CurrBal [-ZZZ,ZZZ.99], ExpAcct
from TrnVendor
where City = 'Chicago'
and ExpAcct = '4110'
or CurrBal < 1200 
order by City, ExpAcct;

2. Execute the SQL statement.

City	CurrBal	ExpAcct
Bayshore	498.05	4110
Burlington	51.00	4120
Chicago	4,109.14	4110
Chicago	500.00	4120
Chicago	1,000.00	6080
Chicago	26.98	7230

---

Retrieve all vendors whose expense account is 4110 or who have a current balance over 1200. The vendor must be located in Chicago.

1. In the SQL text box, type:

Select City, CurrBal [-ZZZ,ZZZ.99], ExpAcct
from Vendor
where City = 'Chicago'
and (ExpAcct = '4110'
or CurrBal > 1200) 
order by City, ExpAcct;

2. Execute the SQL statement.

Results:
 

City	CurrBal	ExpAcct
Chicago	0.00	4030
Chicago	4,135.24	4110
Chicago	1,309.95	4110
Chicago	100.00	6010
Chicago	500.00	6040
Chicago	47.00	7010
Chicago	106.00	7190