The SELECT statement by itself will return all rows from a database. Most times, we do not need all of the data, only a subset. The solution is to filter the selection of the rows returned in the result set. The WHERE clause allows us to tell the query how to select the rows to return.
Although T-SQL is not case-sensitive, I will use upper-case for all SQL commands and column names.
We are going to use our same tables from the previous articles, but with a few changes. The FREIGHTAMT and DOCAMT values have been changed in the @ORDER table.
DECLARE @order TABLE (ORDERID VARCHAR(20),CUSTOMERID VARCHAR(30), FREIGHTAMT NUMERIC(19,2), DOCAMT NUMERIC(19,2))
INSERT INTO @ORDER (ORDERID, CUSTOMERID, FREIGHTAMT, DOCAMT) VALUES ('ORD001','CUST001',10,100)
INSERT INTO @ORDER (ORDERID, CUSTOMERID, FREIGHTAMT, DOCAMT) VALUES ('ORD002','CUST002',20,150)
INSERT INTO @ORDER (ORDERID, CUSTOMERID, FREIGHTAMT, DOCAMT) VALUES ('ORD003','CUST003',30,200)
DECLARE @ORDERDETAIL TABLE (ORDERID VARCHAR(20), ITEMNMBR VARCHAR(20), UNITPRICE NUMERIC(19,2), QUANTITY NUMERIC(19,0), EXTENDEDPRICE NUMERIC(19,2))
INSERT INTO @ORDERDETAIL (ORDERID, ITEMNMBR, UNITPRICE, QUANTITY, EXTENDEDPRICE) VALUES ('ORD001','ITEM001',1.00, 1, 1.00)
INSERT INTO @ORDERDETAIL (ORDERID, ITEMNMBR, UNITPRICE, QUANTITY, EXTENDEDPRICE) VALUES ('ORD001','ITEM002',2.00, 3, 6.00)
INSERT INTO @ORDERDETAIL (ORDERID, ITEMNMBR, UNITPRICE, QUANTITY, EXTENDEDPRICE) VALUES ('ORD002','ITEM003',3.00, 5, 15.00)
INSERT INTO @ORDERDETAIL (ORDERID, ITEMNMBR, UNITPRICE, QUANTITY, EXTENDEDPRICE) VALUES ('ORD002','ITEM004',4.00, 7, 28.00)
The WHERE clause is used to describe what conditions the data must match before you want to have it returned in the data set. This is done by comparing the value in a column with the given test condition. Rows are only returned if the comparison made in the WHERE clause is TRUE (more on that later).
Suppose we want to see the information for only ORD001. We can look at the results from each table with the following commands.
SELECT ORDERID, CUSTOMERID, FREIGHTAMT, DOCAMT FROM @ORDER WHERE ORDERID = 'ORD001'
SELECT ORDERID, ITEMNMBR, UNITPRICE, QUANTITY, EXTENDEDPRICE FROM @ORDERDETAIL WHERE ORDERID = 'ORD001'
Executing these two commands results in the following output:
In this example, our test condition was: ORDERID = 'ORD001', in which the query asks “Is the value in the ORDERID column for this row 'ORD001'? If so, that row is returned in the data set. If not, it is skipped. I will talk about data types in a later blog, but for now, assume the values in a column are either strings (text) or numbers. String values must always be enclosed in single quotes (').
You can also do numerical comparisons. For example, if you wanted to see all orders where the QUANTITY is less than or equal to 5, you would use the following query:
SELECT ORDERID, ITEMNMBR, UNITPRICE, QUANTITY, EXTENDEDPRICE FROM @ORDERDETAIL WHERE QUANTITY <= '5'
The above query will provide the following result set:
“But, wait! You said that you only use single quotes to enclose strings and the number 5 is definitely not a string!”
OK, you caught me. It turns out that T-SQL knows that the QUANTITY column is a number, so it converts the string to a number. The more correct query would be:
SELECT ORDERID, ITEMNMBR, UNITPRICE, QUANTITY, EXTENDEDPRICE FROM @ORDERDETAIL WHERE QUANTITY <= 5
This is just a simple introduction to using the WHERE statement. Next time, I will talk about more complex searches using Boolean logic (gasp!)