WHERE a clause in SQL (Structured Query Language) is a keyword that is used to specify a condition when fetching the data from a single or multiple tables. If the given condition in WHERE the clause is satisfied, the query returns specific rows from the table that we are interested in. We can use WHERE conditions while doing SELECT, INSERT, UPDATE, or DELETE operations.
A WHERE condition in SQL returns either true or false. Also, we can use only one WHERE clause with SQL. But we can have multiple conditions associated with the WHERE clause by using AND, OR, and NOT logical operators. Also, we can have a WHERE clause within the sub-query as well.
Syntax of WHERE Clause
Below is the basic syntax of WHERE clause.
SELECT col, col1, ...colN
FROM table_name
WHERE
[condition]In the above syntax, WHERE is the keyword that filters the data from the table based on the condition provided. Various types of filters like single value/sub query or range can be used. We can also specify conditions using comparison or logical operators.
Examples used in this tutorial are based on this SQL file that you can find on my GitHub page.
WHERE Clause using one Condition
Let’s take an example of a customer’s table in which WHERE the condition will be applied on one column.
SELECT * FROM OT.ORDERS_DATA WHERE ORDER_ID=105;
ORDER_ID CUS_ID STATUS SLSMAN_ID ORDER_DATE
105 101 Pending 54 2016-11-17 00:00:00.000
As we see, we are getting only a row back for Order id 105.
WHERE Clause using Multiple conditions(AND Condition)
We can use the AND condition the WHERE clause and add filters for more than one table. In this case, as AND the logical operator is used, both the conditions need to be satisfied for the query to return the records.
SELECT * FROM OT.ORDERS_DATA WHERE ORDER_ID=105 AND STATUS='Pending';
ORDER_ID CUS_ID STATUS SLSMAN_ID ORDER_DATE
105 101 Pending 54 2016-11-17 00:00:00.000
WHERE Clause using Multiple conditions(OR Condition)
We can use the OR condition in the WHERE clause to test multiple conditions in which the record is returned when any one of the conditions are met
SELECT * FROM OT.ORDERS_DATA WHERE ORDER_ID=105 OR STATUS='Pending';
Below is the result that we got from the above query that uses the OR conditional operator.
ORDER_ID CUS_ID STATUS SLSMAN_ID ORDER_DATE
105 101 Pending 54 2016-11-17 00:00:00.000
44 102 Pending 55 2017-02-20 00:00:00.000
101 103 Pending 55 2017-01-03 00:00:00.000
1 104 Pending 56 2017-10-15 00:00:00.000
WHERE Clause for Text Fields vs. Numeric Fields
Use of WHERE clause is different when the filter operation has Text fields and Numerical fields. When the table column has a text field as values, it needs to be enclosed in single or double quotes. But when the column has a numerical value, it does not need to be enclosed in quotes.
SELECT * FROM OT.CUSTOMERS_DATA WHERE CUS_ID =107;
SELECT * FROM OT.CUSTOMERS_DATA WHERE NAME='Norfolk Southern';
WHERE Clause for Numeric comparison
To use WHERE clause for comparing numerical values, we can use the SQL comparator operator less than <, greater than >, greater than or equal to >= and less than or equal to <=.
Let’s take an example where we will check the value of the Credit Limit column in the customer table. We will check all the records for which the Credit Limit is greater than or equal to 1000.
/*
* WHERE Clause with Greater than Clause to check Credit Limit Greater than 1000
*/
SELECT * FROM OT.CUSTOMERS_DATA WHERE CRDT_LMT >=1000;
CUS_ID NAME ADDRESS WEBSITE CRDT_LMT
101 State Street Corp. 1607 Abwdrts St, Bangalore, Kar http://www.statestreet.com 3,500
102 Unum Group 1608 Amadeus St, Bangalore, Kar http://www.unum.com 3,500
103 Reynolds American 1610 Betrayal Crt, Bangalore, Kar http://www.reynoldsamerican.com 5,000
104 Group 1 Automotive 1614 Crackers Rd, Bangalore - India, Kar http://www.group1auto.com 2,300
105 Henry Schein 1615 Crackers Crt, Bangalore - India, Kar http://www.henryschein.com 5,000
The above results show all the rows for which the credit limit is greater than or equal to 1000.
Let’s look at the condition where we will filter all the records for which the credit limit is less than or equal to 1000.
/*
* WHERE Clause with Smaller than Clause to check Credit Limit Less than 1000
*/
SELECT * FROM OT.CUSTOMERS_DATA WHERE CRDT_LMT <=1000;
CUS_ID NAME ADDRESS WEBSITE CRDT_LMT
106 Norfolk Southern 1618 Footloose St, Bangalore - India, Kar http://www.nscorp.com 900
107 Reinsurance Group of America 1619 Footloose Rd, Bangalore - India, Kar http://www.rgare.com 900
108 Public Service Enterprise Group 1621 Gargon! Blvd, Bangalore - India, Kar http://www.pseg.com 600
109 DTE Energy 8110 Jackson Rd, Ann Arbor, MI http://www.dteenergy.com 200
110 Assurant 101 N Falahee Rd, Jackson, MI http://www.assurant.com 300
