SQL WHERE

SQL WHERE Clause

A 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.

WHERE can be used with SELECT, UPDATE, and DELETE.

Example

#

List all suppliers in France.

SELECT *
  FROM Supplier
 WHERE Country = 'France'

Syntax

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

More Examples

SQL SELECT WHERE

CUSTOMER
Id
FirstName
LastName
City
Country
Phone
Problem: List all customers in Sweden.
SELECT Id, FirstName, LastName, City, Country, Phone
  FROM Customer
 WHERE Country = 'Sweden'
Result:  2 records
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

SQL UPDATE WHERE

SUPPLIER
Id
CompanyName
ContactName
City
Country
Phone
Fax
Problem: For supplier Pavlova, Ltd, change the city to Sydney.
UPDATE Supplier
   SET City = 'Sydney'
 WHERE CompanyName = 'Pavlova, Ltd.'
Result:  1 record updated.

SQL DELETE WHERE

PRODUCT
Id
ProductName
SupplierId
UnitPrice
Package
IsDiscontinued
Problem: Delete all products with unit price higher than $50.
DELETE FROM Product
 WHERE UnitPrice > 50
Result:  7 records deleted.

Note: Referential integrity may prevent this deletion. A better approach may be to discontinue the product, that is, set the column IsDiscontinued to true.


SQL WHERE Performance

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.


You may also like



Guides