The SELECT statement is the most common statement used in SQL. It is used to retrieve rows of data from one or more tables and store the results in a table, called the result set. In later blogs, we will discuss how to be selective, or filter, what rows are returned, and how to sort and group the result set.
In the previous blog, we used the following code to create and populate 2 tables and display their contents.
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',0,100)
INSERT INTO @ORDER (ORDERID, CUSTOMERID, FREIGHTAMT, DOCAMT) VALUES ('ORD002','CUST002',0,100)
INSERT INTO @ORDER (ORDERID, CUSTOMERID, FREIGHTAMT, DOCAMT) VALUES ('ORD003','CUST003',0,100)
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)
SELECT * FROM @ORDER
SELECT * FROM @ORDERDETAIL
Another way to display the data from all of the columns in the result set is to list each column name in the SELECT statement. Notice that the column names are listed before the table name.
SELECT ORDERID, CUSTOMERID, FREIGHTAMT, DOCAMT FROM @ORDER
SELECT ORDERID, ITEMNMBR, UNITPRICE, QUANTITY, EXTENDEDPRICE FROM @ORDERDETAIL
Using the ‘*’ is much easier if you want all of the columns in the order they are in the result set. If you want only a subset of the columns, or display the columns in a different order, you must list each one individually, in the desired order.
Let’s say that you want to see only the FREIGHTAMT, ORDERID, and DOCAMT from the @ORDER table, in that order. The following command will produce this output (result set).
SELECT FREIGHTAMT, ORDERID, DOCAMT FROM @ORDER
If we don’t care about the ORDERID and want only the ITEMNMBR, UNITPRICE, QUANTITY, EXTENDEDPRICE from the ORDERDETAIL table, we would use this command to produce the following result set
SELECT ITEMNMBR, UNITPRICE, QUANTITY, EXTENDEDPRICE FROM @ORDERDETAIL
These simple SELECT statements are returning all of the rows from the database to the result set. That is often more than you need. Next time, I will talk about controlling the selection of the rows that are returned from a query.