The purpose of the SELECT statement is to return rows (result set) from one or more tables in a database. Seldom do you need everything. As shown in the previous blog, the WHERE clause can help limit or filter the rows returned. Today, I will talk about ways to refine the selection criteria even more. SQL provides some keywords (AND, OR, NOT) that allow you describe multiple conditions that must exist before a row is selected.
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)
INSERT INTO @ORDER (ORDERID, CUSTOMERID, FREIGHTAMT, DOCAMT) VALUES ('ORD004','CUST002',40,75)
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)
INSERT INTO @ORDERDETAIL (ORDERID, ITEMNMBR, UNITPRICE, QUANTITY, EXTENDEDPRICE) VALUES ('ORD003','ITEM002',2.00, 3, 6.00)
INSERT INTO @ORDERDETAIL (ORDERID, ITEMNMBR, UNITPRICE, QUANTITY, EXTENDEDPRICE) VALUES ('ORD003','ITEM001',1.00, 1, 1.00)
INSERT INTO @ORDERDETAIL (ORDERID, ITEMNMBR, UNITPRICE, QUANTITY, EXTENDEDPRICE) VALUES ('ORD004','ITEM002',2.00, 3, 6.00)
INSERT INTO @ORDERDETAIL (ORDERID, ITEMNMBR, UNITPRICE, QUANTITY, EXTENDEDPRICE) VALUES ('ORD004','ITEM003',3.00, 5, 15.00)
INSERT INTO @ORDERDETAIL (ORDERID, ITEMNMBR, UNITPRICE, QUANTITY, EXTENDEDPRICE) VALUES ('ORD004','ITEM004',4.00, 7, 28.00)
As we have seen before, we can use SQL commands to retrieve rows from the database tables:
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:
That is great, but If you had a huge database with thousands of customers and orders, you would not want to look at them one at a time. What if you want to work with a subset of the database? Good news! SQL gives you way! Enter the Boolean logic.
As discussed before, the WHERE clause is used to describe what conditions the data in a row must meet before it is returned in the result set. This is done by comparing the value in a column with the given test condition. Rows are only returned if the overall result of all of the comparisons made in the WHERE clause are TRUE.
SQL has 3 keywords that help in selecting the rows to return to the result set (there are actually more, but those are for a later blog): AND, OR, NOT.
A little on Boolean logic. The two main logical operations are AND and OR. These are used to chain multiple conditions together. With AND, in order for a row to be returned, ALL conditions must be true. With OR, at least one condition needs to be true for the row to be returned. NOT will be discussed later.
Let’s start with AND. When you use AND, EVERY condition in the WHERE clause must be TRUE before the row is returned. So, how would you phrase the WHERE clause to return rows for ORDERID is ORD001 AND the UNITPRICE was greater than $1? Is this what you came up with:
SELECT ORDERID, ITEMNMBR, UNITPRICE, QUANTITY, EXTENDEDPRICE FROM @ORDERDETAIL WHERE ORDERID = 'ORD001' AND UNITPRICE > 1.00
If so, you should have received the following result set:
Notice that the result set consisted of rows where the ORDERID was ‘ORD001’ and the UNITPRICE was greater than $1. Both of the conditions are true in only 1 row, the one returned.
Now let’s say we want to look at the order details for orders ORD002 and ORD003. How would you form that SQL statement? Did you come up with:
SELECT ORDERID, ITEMNMBR, UNITPRICE, QUANTITY, EXTENDEDPRICE FROM @ORDERDETAIL WHERE ORDERID = 'ORD002' AND ORDERID = 'ORD003'
SELECT ORDERID, ITEMNMBR, UNITPRICE, QUANTITY, EXTENDEDPRICE FROM @ORDERDETAIL WHERE ORDERID = 'ORD002' AND ORDERID = 'ORD003'
If you did, here is the result set:
To paraphrase Clara on the old Wendy’s commercials, “Where’s the data?”
Well, think about what you asked for. You asked for all the rows where the ORDERID was 'ORD002' AND the ORDERID was 'ORD003'. Remember, with AND, ALL conditions must be TRUE. Kind of hard for an ORDERID to have 2 values, isn’t it? What you wanted was all the rows for ORD002 and ORD003, but to SQL, you need to ask for ORDERID = 'ORD002' OR ORDERID = 'ORD003'. When you use OR, at least 1, not all, of the conditions must be TRUE to be in the result set. When you change the AND to OR, you get the following, which is what you wanted.
SELECT ORDERID, ITEMNMBR, UNITPRICE, QUANTITY, EXTENDEDPRICE FROM @ORDERDETAIL WHERE ORDERID = 'ORD002' OR ORDERID = 'ORD003'
The problem with computers is that they do exactly what you tell them to do, which is not necessarily what you want them to do! You need to think like a computer and think about what you are asking. Consider the following:
A programmer is going to the grocery store and his wife tells him, "Buy a gallon of milk, and if there are eggs, buy a dozen." So the programmer goes, buys everything, and drives back to his house. Upon arrival, his wife angrily asks him, "Why did you get 13 gallons of milk?" The programmer says, "There were eggs!"
Well, it looks like I am out of time and I didn’t get to the NOT keyword. Next time, we will continue with the WHERE clause, including the NOT keyword, and we will talk about order of evaluation. Until then, enjoy your milk.