The SQL WHERE clause filters data that meet some criteria.
WHERE only returns the rows you're interested in.
A WHERE condition returns either true or false.
List all suppliers in France.
SELECT *
FROM Supplier
WHERE Country = 'France'
Syntax of WHERE on a SELECT statement.
SELECT column-names FROM table-name WHERE condition
Syntax of WHERE on an UPDATE statement.
UPDATE table-name SET column-name = value WHERE condition
Syntax of WHERE on a DELETE statement.
DELETE table-name WHERE condition
CUSTOMER |
---|
Id |
FirstName |
LastName |
City |
Country |
Phone |
SELECT Id, FirstName, LastName, City, Country, Phone
FROM Customer
WHERE Country = 'Sweden'
Id | FirstName | LastName | City | Country | Phone |
---|---|---|---|---|---|
5 | Christina | Berglund | Luleå | Sweden | 0921-12 34 65 |
24 | Maria | Larsson | Bräcke | Sweden | 0695-34 67 21 |
SUPPLIER |
---|
Id |
CompanyName |
ContactName |
City |
Country |
Phone |
Fax |
UPDATE Supplier
SET City = 'Sydney'
WHERE CompanyName = 'Pavlova, Ltd.'
PRODUCT |
---|
Id |
ProductName |
SupplierId |
UnitPrice |
Package |
IsDiscontinued |
DELETE FROM Product
WHERE UnitPrice > 50
Note: Referential integrity may prevent this deletion.
A better approach may be to discontinue the product, that is, set the column IsDiscontinued to true.
Be sure that the columns included in the WHERE clause are properly indexed.
If not, a table scan is performed, which, with large tables, can be very slow.
Note: A table scan is an operation in which each table record is read to check if there is a match with the WHERE clause. Table scans are a common source of application performance problems.
A query can only have one WHERE
clause.
However, multiple conditions can be specified within this WHERE
clause.
This requires the use of AND, OR, and NOT logical operators.
In addition, a query can have subqueries with their own WHERE
clauses.
Yes, the WHERE
clause can be used in an UPDATE statement.
It helps filter for the rows that need updating.
A WHERE
clause filters rows, and
A HAVING clause filters groups, i.e., aggregated rows.
Yes, WHERE and HAVING clauses can be used together in a query.
Each serves a different purpose:
The WHERE clause filters rows.
The HAVING clause filters groups.
First, the WHERE clause will filter individual rows. These rows are then grouped by aggregate functions. Finally, the HAVING clause will filter the groups.