SQL WHERE Clause

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

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

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

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.

DELETE WHERE

PRODUCT
Id
ProductName
SupplierId
UnitPrice
Package
IsDiscontinued
Problem: Delete products with a price higher than $50.
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.

Result:  7 records deleted.

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. Table scans are a common source of application performance problems.


Developers also Ask


Can you have multiple WHERE clauses in SQL?

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.

Can we use a WHERE clause in an UPDATE query?

Yes, the WHERE clause can be used in an UPDATE statement.

It helps filter for the rows that need updating.

Can we use a WHERE clause in an INSERT statement?

No, the WHERE clause cannot be used in an INSERT statement.

It's designed to be used in SELECT and UPDATE statements.

What is the difference between the WHERE and HAVING clause?

A WHERE clause filters rows, and

A HAVING clause filters groups, i.e., aggregated rows.

Can we use WHERE and HAVING clauses together?

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.

What are the clauses in SQL?

A clause is a part of a query that customizes the results.

SQL offers several clauses. These are the more common ones.


You may also like



Guides


vsn 3.1