Retrieving data (simple select statement)
Here is the generic SELECT statement, as it is defined by the SQL99 standard, for selecting data from a single table.
SELECT [DISTINCT] [<qualifier>.]<column_name> |
* |
<expression>
[AS <column_alias>],...
FROM <table_or_view_name> |
<inline_view>
[[AS] <table_alias>]
[WHERE <predicate>]
[GROUP BY [<qualifier>.]<column_name>,...
[HAVING <predicate>]
]
[ORDER_BY <column_name> |
<column_number>
[ASC | DESC],...
];
SELECT Clause: What Do We Select?
In the relational databases the SELECT statement selects values in the columns, literal values, or expressions. The returned values themselves could be of any valid data types. These values can be displayed in the client application, or written into a file, used in the intermediate calculations, or entered into database tables.
Single-column select
SELECT cust_name_s
FROM customer
Multi-column select
SELECT cust_id_n,
cust_status_s,
cust_name_s
FROM customer
Selecting all columns
SELECT *
FROM customer
Selecting distinct values
SELECT DISTINCT payterms_discpct_n
FROM payment_terms
Using literals, functions, and calculated columns
SELECT SYSDATE
FROM dual
SELECT (5+5)
FROM sysibm.sysdummy1
SELECT cust_name_s,
100 AS NUMERIC_CONSTANT,
'ABC' AS STRING_CONSTANT
FROM customer
Using subqueries in a SELECT clause
SELECT prod_num_s,
prod_price_n,
(SELECT stax_amt_n
FROM sales_tax
WHERE stax_state_s = 'WA') AS TAX_RATE,
FROM product
FROM Clause: Select from What?
The database objects you should be able to select from are tables and views. These come in many flavors — temporary tables, inline views, materialized views, to name just a few — but the truth is that there is nothing else in the RDBMS world to select from.
Using aliases in a FROM clause
SELECT status_id_n,
s.status_code_s,
s.status_desc_s
FROM status s
Using subqueries in a FROM clause (inline views)
SELECT cust.id,
cust.active
FROM (SELECT cust_id_n AS id,
cust_status_s AS active,
cust_name_s,
cust_alias_s AS alias
FROM customer) cust
WHERE Clause: Setting Horizontal Limits
The SQL WHERE clause allows you to limit the number of rows in result sets returned by a query through specifying some condition or set of conditions. The search criteria specified in the WHERE clause evaluate to TRUE or FALSE, and all the rules of Boolean algebra are fully applicable there.
Using comparison operators
SELECT cust_name_s
FROM customer
WHERE cust_id_n = 7
Using compound operators: AND, OR
SELECT phone_salesmanid_fn
FROM phone
WHERE phone_custid_fn IS NULL
AND phone_type_s = 'PHONE'
Using the BETWEEN operator
SELECT prod_description_s
FROM product
WHERE prod_price_n BETWEEN 23.10 AND 30
Using the IN operator
SELECT cust_name_s
FROM customer
WHERE cust_alias_s IN
('MNGA71396', 'MNGA71398', 'MNGA71400')
Using the IS NULL operator
SELECT phone_salesmanid_fn
FROM phone
WHERE phone_custid_fn IS NULL
Using subqueries in a WHERE clause
SELECT ordhdr_nbr_s,
ordhdr_orderdate_d
FROM order_header
WHERE ordhdr_custid_fn =
(SELECT cust_id_n
FROM customer
WHERE cust_name_s = 'WILE ELECTRONICS INC.')
GROUP BY and HAVING Clauses: Summarizing Results
Grouping records in the result set based on some criteria could provide a valuable insight into data that has accumulated in the table.
SELECT ordline_ordhdrid_fn,
SUM(ordline_ordqty_n) AS TOT_QTY_PER_ORDER
FROM order_line
GROUP BY ordline_ordhdrid_fn
The HAVING clause used exclusively with the GROUP BY clause provides a means of additional selectivity.
SELECT ordline_ordhdrid_fn,
SUM(ordline_ordqty_n) TOT_QTY_PER_ORDER
FROM order_line
GROUP BY ordline_ordhdrid_fn
HAVING SUM(ordline_ordqty_n) > 750
ORDER BY Clause: Sorting Query Output
The query returns results matching the criteria unsorted — i.e., in the order they've been found in the table. To produce sorted output — alphabetically or numerically — you would use an ORDER BY clause.
SELECT cust_name_s,
cust_alias_s,
cust_status_s
FROM customer
ORDER BY cust_name_s;
Last updated