ORDER BY emp_lname,
emp_fname;
SELECT "#",
full_name
FROM #t
ORDER BY letter,
"#";
Here’s what the final SELECT produces; there might be better ways to accom
-
plish this particular task, but this example does demonstrate how NUMBER(*)
can be used to preserve ordering after the original data used for sorting has been
discarded:
# full_name
= =================
1 Jo Ann Davidson
2 Emilio Diaz
3 Marc Dill
4 Kurt Driscoll
1 Melissa Espinoza
2 Scott Evans
For more information about DEFAULT AUTOINCREMENT and SELECT
INTO temporary tables, see Chapter 1, “Creating.” For more information about
the INSERT statement, see Chapter 2, “Inserting.”
NUMBER(*) can also be used as a new value in the SET clause of an
UPDATE statement; for more information, see Section 4.4, “Logical Execution
of a Set UPDATE.”
3.21 INTO Clause
The select INTO clause can be used for two completely different purposes: to
create and insert rows into a temporary table whose name begins with a number
sign (#), or to store values from the select list of a single-row result set into pro
-
gram variables. This section talks about the program variables; for more
information about creating a temporary table, see Section 1.15.2.3, “SELECT
INTO #table_name.”
<select_into> ::= INTO <temporary_table_name>
| INTO <select_into_variable_list>
<temporary_table_name> ::= see <temporary_table_name> in Chapter 1, “Creating”
<select_into_variable_list> ::= <non_temporary_identifier>
{ "," <non_temporary_identifier> }
<non_temporary_identifier> ::= see <non_temporary_identifier> in
Chapter 1, “Creating”
Here is an example that uses two program variables to record the name and row
count of the table with the most rows; when run on the ASADEMO database it
displays “SYSPROCPARM has the most rows: 1632” in the server console
window:
BEGIN
DECLARE @table_name VARCHAR ( 128 );
DECLARE @row_count BIGINT;
CHECKPOINT;
SELECT FIRST
140 Chapter 3: Selecting
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
table_name,
count
INTO @table_name,
@row_count
FROM SYSTABLE
ORDER BY count DESC;
MESSAGE STRING (
@table_name,
' has the most rows: ',
@row_count ) TO CONSOLE;
END;
Note: The SYSTABLE.count column holds the number of rows in the table as
of the previous checkpoint. The explicit CHECKPOINT command is used in the
example above to make sure that SYSTABLE.count is up to date. The alternative,
computing SELECT COUNT(*) for every table in order to find the largest number
of rows, is awkward to code as well as slow to execute if the tables are large.
For more information about BEGIN blocks and DECLARE statements, see
Chapter 8, “Packaging.”
3.22 UNION, EXCEPT, and INTERSECT
Multiple result sets may be compared and combined with the UNION,
EXCEPT, and INTERSECT operators to produce result sets that are the union,
difference, and intersection of the original result sets, respectively.
<select> ::= [ <with_clause> ] WITH
<query_expression> at least one SELECT
[ <order_by_clause> ] ORDER BY
[ <for_clause> ] FOR
<query_expression> ::= <query_expression> <query_operator> <query_expression>
| <subquery>
| <query_specification>
<query_operator> ::= EXCEPT [ DISTINCT | ALL ]
| INTERSECT [ DISTINCT | ALL ]
| UNION [ DISTINCT | ALL ]
The comparisons involve all the columns in the result sets: If every column
value in one row in the first result set is exactly the same as the corresponding
value in a row in the second result set, the two rows are the same; otherwise
they are different. This means the rows in both result sets must have the same
number of columns.
Note: For the purpose of comparing rows when evaluating the EXCEPT,
INTERSECT, and UNION operators, NULL values are treated as being the same.
The operation A EXCEPT B returns all the rows that exist in result set A and do
not exist in B; it could be called “A minus B.” Note that A EXCEPT B is not
the same as B EXCEPT A.
A INTERSECT B returns all the rows that exist in both A and B, but not
the rows that exist only in A or only in B.
A UNION B returns all the rows from both A and B; it could be called “A
plus B.”
Chapter 3: Selecting
141
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
The DISTINCT keyword ensures that no duplicate rows remain in the final
result set, whereas ALL allows duplicates; DISTINCT is the default. The only
way A EXCEPT ALL B could return duplicates is if duplicate rows already
existed in A. The only way A INTERSECT ALL B returns duplicates is if
matching rows are duplicated in both A and B. A UNION ALL B may or may
not contain duplicates; duplicates could come from one or the other or both A
and B.
Here is an example that uses the DISTINCT values of customer.state and
employee.state in the ASADEMO database to demonstrate EXCEPT,
INTERSECT, and UNION. Seven different selects are used, as follows:
n
Distinct values of customer.state.
n
Distinct values of employee.state.
n
Customer states EXCEPT employee states.
n
Employee states EXCEPT customer states.
n
The “exclusive OR” (XOR) of customer and employee states: states that
exist in one or the other table but not both.
n
Customer states INTERSECT employee states.
n
Customer states UNION employee states.
These selects use derived tables to compute the distinct state result sets, as well
as the EXCEPT, INTERSECT, and UNION operations. The LIST function pro-
duces compact output, and the COUNT function computes how many entries
are in each list.
SELECT COUNT(*) AS count,
LIST ( state ORDER BY state ) AS customer_states
FROM ( SELECT DISTINCT state
FROM customer )
AS customer;
SELECT COUNT(*) AS count,
LIST ( state ORDER BY state ) AS employee_states
FROM ( SELECT DISTINCT state
FROM employee )
AS employee;
SELECT COUNT(*) AS count,
LIST ( state ORDER BY state ) AS customer_except_employee
FROM ( SELECT state
FROM customer
EXCEPT
SELECT state
FROM employee )
AS customer_except_employee;
SELECT COUNT(*) AS count,
LIST ( state ORDER BY state ) AS employee_except_customer
FROM ( SELECT state
FROM employee
EXCEPT
SELECT state
FROM customer )
AS employee_except_customer;
SELECT COUNT(*) AS count,
LIST ( state ORDER BY state ) AS customer_xor_employee
142 Chapter 3: Selecting
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
FROM ( ( SELECT state
FROM customer
EXCEPT
SELECT state
FROM employee )
UNION ALL
( SELECT state
FROM employee
EXCEPT
SELECT state
FROM customer ) )
AS customer_xor_employee;
SELECT COUNT(*) AS count,
LIST ( state ORDER BY state ) AS customer_intersect_employee
FROM ( SELECT state
FROM customer
INTERSECT
SELECT state
FROM employee )
AS customer_intersect_employee;
SELECT COUNT(*) AS count,
LIST ( state ORDER BY state ) AS customer_union_employee
FROM ( SELECT state
FROM customer
UNION
SELECT state
FROM employee )
AS customer_intersect_employee;
Following are the results. Note that every SELECT produces a different count,
and that the two EXCEPT results are different. In particular, the presence and
absence of CA, AZ, and AB in the different lists illustrate the differences among
EXCEPT, INTERSECT, and UNION.
count LIST of states
===== ==============
36 AB,BC,CA,CO,CT,DC,FL,GA,IA,IL,IN,KS,LA,MA, customer_states
MB,MD,MI,MN,MO,NC,ND,NJ,NM,NY,OH,ON,OR,PA,
PQ,TN,TX,UT,VA,WA,WI,WY
16 AZ,CA,CO,FL,GA,IL,KS,ME,MI,NY,OR,PA,RI,TX, employee_states
UT,WY
23 AB,BC,CT,DC,IA,IN,LA,MA,MB,MD,MN,MO,NC,ND, customer_except_employee
NJ,NM,OH,ON,PQ,TN,VA,WA,WI
3 AZ,ME,RI employee_except_customer
26 AB,AZ,BC,CT,DC,IA,IN,LA,MA,MB,MD,ME,MN,MO, customer_xor_employee
NC,ND,NJ,NM,OH,ON,PQ,RI,TN,VA,WA,WI
13 CA,CO,FL,GA,IL,KS,MI,NY,OR,PA,TX,UT,WY customer_intersect_employee
39 AB,AZ,BC,CA,CO,CT,DC,FL,GA,IA,IL,IN,KS,LA, customer_union_employee
MA,MB,MD,ME,MI,MN,MO,NC,ND,NJ,NM,NY,OH,ON,
OR,PA,PQ,RI,TN,TX,UT,VA,WA,WI,WY
Of the three operators EXCEPT, INTERSECT, and UNION, UNION is by far
the most useful. UNION helps with the divide-and-conquer approach to prob
-
lem solving: Two or more simple selects are often easier to write than one
Chapter 3: Selecting
143
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
complex select. A UNION of multiple selects may also be much faster than one
SELECT, especially when UNION is used to eliminate the OR operator from
boolean expressions; that’s because OR can be difficult to optimize but UNION
is easy to compute, especially UNION ALL.
Tip: UNION ALL is fast, so use it all the time, except when you can’t. If you
know the individual result sets don’t have any duplicates, or you don’t care about
duplicates, use UNION ALL. Sometimes it’s faster to eliminate the duplicates in
the application than make the server do it.
Here is an example that displays a telephone directory for all customers and
employees whose last name begins with “K.” String literals 'Customer' and 'Em
-
ployee' are included in the result sets to preserve the origin of the data in the
final UNION ALL.
SELECT STRING ( customer.lname, ', ', customer.fname ) AS full_name,
STRING ( '(', LEFT ( customer.phone, 3 ), ') ',
SUBSTR ( customer.phone, 4, 3 ), '-',
RIGHT ( customer.phone,4)) ASphone,
'Customer' AS relationship
FROM customer
WHERE customer.lname LIKE 'k%'
UNION ALL
SELECT STRING ( employee.emp_lname, ', ', employee.emp_fname ),
STRING ( '(', LEFT ( employee.phone, 3 ), ') ',
SUBSTR ( employee.phone, 4, 3 ), '-',
RIGHT ( employee.phone,4)),
'Employee'
FROM employee
WHERE employee.emp_lname LIKE 'k%'
ORDER BY 1;
Here is the final result:
full_name phone relationship
================ ============== ============
Kaiser, Samuel (612) 555-3409 Customer
Kelly, Moira (508) 555-3769 Employee
King, Marilyn (219) 555-4551 Customer
Klobucher, James (713) 555-8627 Employee
Kuo, Felicia (617) 555-2385 Employee
The INTO #table_name clause may be used together with UNION, as long as
the INTO clause appears only in the first SELECT. Here is an example that cre
-
ates a temporary table containing all the “K” names from customer and
employee:
SELECT customer.lname AS last_name
INTO #last_name
FROM customer
WHERE customer.lname LIKE 'k%'
UNION ALL
SELECT employee.emp_lname
FROM employee
WHERE employee.emp_lname LIKE 'k%';
SELECT *
FROM #last_name
ORDER BY 1;
144 Chapter 3: Selecting
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Here are the contents of the #last_name table:
last_name
=========
Kaiser
Kelly
King
Klobucher
Kuo
For more information about creating temporary tables this way, see Section
1.15.2.3, “SELECT INTO #table_name.”
The first query in a series of EXCEPT, INTERSECT, and UNION opera
-
tions establishes the alias names of the columns in the final result set. That’s not
true for the data types, however; SQL Anywhere examines the corresponding
select list items in all the queries to determine the data types for the final result
set.
Tip: Be careful with data types in a UNION. More specifically, make sure each
select list item in each query in a series of EXCEPT, INTERSECT, and UNION
operations has exactly the same data type as the corresponding item in every
other query in the series. If they aren’t the same, or you’re not sure, use CAST to
force the data types to be the same. If you don’t do that, you may not like what
you get. For example, if you UNION a VARCHAR ( 100 ) with a VARCHAR ( 10 )
the result will be (so far, so good) a VARCHAR ( 100 ). However, if you UNION a
VARCHAR with a BINARY the result will be LONG BINARY; that may not be what
you want, especially if you don’t like case-sensitive string comparisons.
3.23 CREATE VIEW
The CREATE VIEW statement can be used to permanently record a select that
can then be referenced by name in the FROM clause of other selects as if it
were a table.
<create_view> ::= CREATE VIEW [ <owner_name> "." ] <view_name>
[ <view_column_name_list> ]
AS
[ <with_clause> ] WITH
<query_expression> at least one SELECT
[ <order_by_clause> ] ORDER BY
[ <for_xml_clause> ]
[ WITH CHECK OPTION ]
<view_column_name_list> ::= "(" [ <alias_name_list> ] ")"
Views are useful for hiding complexity; for example, here is a CREATE VIEW
that contains a fairly complex SELECT involving the SQL Anywhere system
tables:
CREATE VIEW v_parent_child AS
SELECT USER_NAME ( parent_table.creator ) AS parent_owner,
parent_table.table_name AS parent_table,
USER_NAME ( child_table.creator ) AS child_owner,
child_table.table_name AS child_table
FROM SYS.SYSFOREIGNKEY AS foreign_key
INNER JOIN
( SELECT table_id,
creator,
table_name
Chapter 3: Selecting
145
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
FROM SYS.SYSTABLE
WHERE table_type = 'BASE' ) no VIEWs, etc.
AS parent_table
ON parent_table.table_id = foreign_key.primary_table_id
INNER JOIN
( SELECT table_id,
creator,
table_name
FROM SYS.SYSTABLE
WHERE table_type = 'BASE' ) no VIEWs, etc.
AS child_table
ON child_table.table_id = foreign_key.foreign_table_id;
The SYSTABLE table contains information about each table in the database,
SYSFOREIGNKEY is a many-to-many relationship table that links parent and
child rows in SYSTABLE, and USER_NAME is a built-in function that con
-
verts a numeric user number like 1 into the corresponding user id 'DBA'. The
v_parent_child view produces a result set consisting of the owner and table
names for the parent and child tables for each foreign key definition in the data
-
base. The INNER JOIN operations are required because SYSFOREIGNKEY
doesn’t contain the table names, just numeric table_id values; it’s SYSTABLE
that has the names we want.
Note: Every SQL Anywhere database comes with predefined views similar to
this; for example, see SYSFOREIGNKEYS.
Following is a SELECT using v_parent_child to display all the foreign key rela-
tionships involving tables owned by 'DBA'. This SELECT is simple and easy to
understand, much simpler than the underlying view definition.
SELECT parent_owner,
parent_table,
child_owner,
child_table
FROM v_parent_child
WHERE parent_owner = 'DBA'
AND child_owner = 'DBA'
ORDER BY 1, 2, 3, 4;
Here is the result set produced by that SELECT when it’s run against the
ASADEMO database:
parent_owner parent_table child_owner child_table
============ ============ =========== =================
DBA customer DBA sales_order
DBA department DBA employee
DBA employee DBA department
DBA employee DBA sales_order
DBA fin_code DBA fin_data
DBA fin_code DBA sales_order
DBA product DBA sales_order_items
DBA sales_order DBA sales_order_items
146 Chapter 3: Selecting
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Tip: Don’t get carried away creating views. In particular, do not create a view
for every table that simply selects all the columns with the aim of somehow iso
-
lating applications from schema changes. That approach doubles the number of
schema objects that must be maintained, with no real benefit. A schema change
either doesn’t affect an application or it requires application maintenance, and
an extra layer of obscurity doesn’t help. And don’t create views just to make col
-
umn names more readable, use readable column names in the base tables
themselves; hokey naming conventions are a relic of the past millennium and
have no place in this new century.
Tip:
Watch out for performance problems caused by excessive view complex
-
ity. Views are evaluated and executed from scratch every time a query that uses
them is executed. For example, if you use views containing multi-table joins to
implement a complex security authorization scheme that affects every table and
every query, you may pay a price in performance. Views hide complexity from
the developer but not the query optimizer; it may not be able to do a good job
on multi-view joins that effectively involve dozens or hundreds of table references
in the various FROM clauses.
A view can be used to UPDATE, INSERT, and DELETE rows if that view is
updatable, insertable, and deletable, respectively. A view is updatable if it is
possible to figure out which rows in the base tables must be updated; that means
an updatable view cannot use DISTINCT, GROUP BY, UNION, EXCEPT,
INTERSECT, or an aggregate function reference. A view is insertable if it is
updatable and only involves one table. The same thing applies to a deletable
rule: It must only have one table and be updatable.
The optional WITH CHECK OPTION clause applies to INSERT and
UPDATE operations involving the view; it states that these operations will be
checked against the view definition and only allowed if all of the affected rows
would qualify to be selected by the view itself. For more information, see the
SQL Anywhere Help; this book doesn’t discuss updatable views except to pres
-
ent the following example:
CREATE TABLE parent (
key_1 INTEGER NOT NULL PRIMARY KEY,
non_key_1 INTEGER NOT NULL );
CREATE VIEW v_parent AS
SELECT *
FROM parent;
CREATE TABLE child (
key_1 INTEGER NOT NULL REFERENCES parent ( key_1 ),
key_2 INTEGER NOT NULL,
non_key_1 INTEGER NOT NULL,
PRIMARY KEY ( key_1, key_2 ) );
CREATE VIEW v_child AS
SELECT *
FROM child;
CREATE VIEW v_family (
parent_key_1,
parent_non_key_1,
child_key_1,
child_key_2,
Chapter 3: Selecting
147
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
child_non_key_1 ) AS
SELECT parent.key_1,
parent.non_key_1,
child.key_1,
child.key_2,
child.non_key_1
FROM parent
INNER JOIN child
ON child.key_1 = parent.key_1;
INSERT v_parent VALUES ( 1, 444 );
INSERT v_parent VALUES ( 2, 555 );
INSERT v_parent VALUES ( 3, 666 );
INSERT v_child VALUES ( 1, 77, 777 );
INSERT v_child VALUES ( 1, 88, 888 );
INSERT v_child VALUES ( 2, 99, 999 );
INSERT v_child VALUES ( 3, 11, 111 );
UPDATE v_family
SET parent_non_key_1 = 1111,
child_non_key_1 = 2222
WHERE parent_key_1 = 1
AND child_key_2 = 88;
DELETE v_child
WHERE key_1 = 3
AND key_2 = 11;
SELECT * FROM v_family
ORDER BY parent_key_1,
child_key_2;
The INSERT and DELETE statements shown above work because the v_parent
and v_child views are insertable, deletable, and updatable. However, the v_fam-
ily view is only updatable, not insertable or deletable, because it involves two
tables. Note that the single UPDATE statement changes one row in each of two
different tables. Here is the result set from the final SELECT:
parent_key_1 parent_non_key_1 child_key_1 child_key_2 child_non_key_1
============ ================ =========== =========== ===============
1 1111 1 77 777
1 1111 1 88 2222
2 555 2 99 999
3.24 WITH Clause
The WITH clause may be used to define one or more local views. The WITH
clause is appended to the front of a query expression involving one or more
selects, and the local views defined in the WITH clause may be used in those
selects. The RECURSIVE keyword states that one or more of the local views
may be used in recursive union operations. The topic of recursive unions is cov
-
ered in the next section.
<select> ::= [ <with_clause> ] WITH
<query_expression> at least one SELECT
[ <order_by_clause> ] ORDER BY
[ <for_clause> ] FOR
<with_clause> ::= WITH [ RECURSIVE ] <local_view_list>
<local_view_list> ::= <local_view> { "," <local_view> }
148 Chapter 3: Selecting
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
<local_view> ::= <local_view_name>
[ <local_view_column_name_list> ]
AS <subquery>
<local_view_name> ::= <identifier>
<local_view_column_name_list> ::= "(" [ <alias_name_list> ] ")"
Note: The SQL Anywhere Help uses the term “temporary view” instead of
“local view.” Unlike temporary tables, however, these views may only be refer
-
enced locally, within the select to which the WITH clause is attached. The word
“temporary” implies the view definition might persist until the connection drops.
There is no such thing as CREATE TEMPORARY VIEW, which is why this book uses
the phrase “local view” instead.
The WITH clause may be used to reduce duplication in your code: A single
local view defined in the WITH clause may be referenced, by name, more than
once in the FROM clause of the subsequent select. For example, the v_par
-
ent_child example from the previous section may be simplified to replace two
identical derived table definitions with one local view called base_table. Note
that there is no problem with having a WITH clause inside a CREATE VIEW;
i.e., having a local view defined inside a permanent view.
CREATE VIEW v_parent_child AS
WITH base_table AS
( SELECT table_id,
creator,
table_name
FROM SYS.SYSTABLE
WHERE table_type = 'BASE' )
SELECT USER_NAME ( parent_table.creator ) AS parent_owner,
parent_table.table_name AS parent_table,
USER_NAME ( child_table.creator ) AS child_owner,
child_table.table_name AS child_table
FROM SYS.SYSFOREIGNKEY AS foreign_key
INNER JOIN base_table
AS parent_table
ON parent_table.table_id = foreign_key.primary_table_id
INNER JOIN base_table
AS child_table
ON child_table.table_id = foreign_key.foreign_table_id;
You can only code the WITH clause in front of the outermost SELECT in a
SELECT, CREATE VIEW, or INSERT statement. That isn’t much of a restric
-
tion because you can still refer to the local view names anywhere down inside
nested query expressions; you just can’t code more WITH clauses inside
subqueries.
3.24.1 Recursive UNION
The recursive union is a special technique that uses the WITH clause to define a
local view based on a UNION ALL of two queries:
n
The first query inside the local view is an “initial seed query” that provides
one or more rows to get the process rolling.
n
The second query contains a recursive reference to the local view name
itself, and it appends more rows to the initial result set produced by the first
query. The RECURSIVE keyword must appear in the WITH clause for the
recursion to work.
Chapter 3: Selecting
149
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Không có nhận xét nào:
Đăng nhận xét