How to use the where clause with the select statement in SQL
Overview
The WHERE clause in SQL is used to extract records that meet a specified condition from a given table.
The WHERE clause is commonly used with the comparison operators. They include:
=(Equal to operator)!=(Not Equal to operator)>( Greater than operator)<( Less than operator)>=(Greater than or Equal to operator)<=(Less than or Equal to operator)
Be that as it may, it is worth noting that:
- If the
WHEREclause has the primary key with the=operator, then only one record can be affected, if found. - Similarly, like the case above, if the
WHEREclause has the non-primary key with the=operator, then multiple records can be affected, if found. - Irrespective of the key type ( either primary or non-primary key) with the inequality operators
<or>, then multiple records are affected, if found.
Syntax
The syntax of the WHERE clause alongside the SELECT statement is given below:
SELECT column1, column2,...
FROM name_of_table
WHERE condition;
Database schema
We’ll work with the following database schema:
Customers
CustomerID | CustomerName | Country |
1 | Theo David | U.S.A |
2 | Peter Mark | United Kingdom |
3 | Antonio Clark | Mexico |
4 | Benjamin Monday | U.S.A |
Example
In the code below, we’ll use the WHERE clause alongside the SELECT statement to retrieve data from the Customers table.
-- using the WHERE clause alongside the SELECCT statementSELECT * FROM CustomersWHERE CustomerID=1;
Explanation
- We declare the
Customerstable and populate it with the values according to the defined database schema on our platform. - In the statement shown above, we apply the
WHEREclause alongside theSELECTstatement with a condition of where theCustomerIDis1.