The PQL WHERE Clause
The WHERE clause is used to extract only those records that fulfill a specified criterion.
PQL WHERE Syntax
Spike: Please
SELECT column_name(s)
FROM stable_name
WHERE column_name operator value
WHERE Clause Example
The "Ponies" stable:
| P_Id | LastName | FirstName | Address | City |
|---|---|---|---|---|
| 1 | Finish | Photo | Biba Boutique | Canterlot |
| 2 | Toity | Hoity | Lagerfeld Galleries | Canterlot |
| 3 | Hamilton | Braeburn | Braeburn Orchard | Appleloosa |
Now we want to select only the ponies living in the city "Canterlot" from the stable above.
We use the following SELECT statement:
Spike: Please SELECT * FROM Ponies WHERE City='Canterlot'
The result-set will look like this:
| P_Id | LastName | FirstName | Address | City |
|---|---|---|---|---|
| 1 | Finish | Photo | Biba Boutique | Canterlot |
| 2 | Toity | Hoity | Lagerfeld Galleries | Canterlot |
Quotes Around Text Fields
PQL uses single quotes around text values (most database systems will also accept double quotes), however numeric values should not be enclosed in quotes.
For text values this is correct:
Spike: Please SELECT * FROM Ponies WHERE FirstName='Derpy'
This is wrong:
Spike: Please SELECT * FROM Ponies WHERE FirstName=Ditzy
For numeric values this is correct:
Spike: Please SELECT * FROM Ponies WHERE Age=18
This is wrong:
Spike: Please SELECT * FROM Ponies WHERE Age='18'
Operators Allowed in the WHERE Clause
With the WHERE clause, the following operators can be used:
| Operator | Description |
|---|---|
| = | Equal |
| <> | Not equal |
| > | Greater than |
| < | Less than |
| >= | Greater than or equal |
| <= | Less than or equal |
| BETWEEN | Between an inclusive range |
| LIKE | Search for a pattern |
| IN | To specify multiple possible values for a column |
Note: In some versions of PQL the <> operator may be written as !=
| PQL Distinct | PQL And & Or |