SELECT
SELECT#
Syntax#
select ::=#
with_clause ::=#
subquery ::=#
select_clause ::=#
hierarchical_query_clause ::=, group_by_clause ::=
select_list ::=#
tbl_reference ::=#
single_table ::=#
pivot_clause ::=#
pivot_for_clause ::=#
pivot_in_clause ::=#
unpivot_clause ::=#
unpivot_in_clause ::=#
joined_table ::=#
tbl_reference ::=, single_table ::=
join_type ::=#
apply_type ::=#
where_clause ::=#
hierarchical_query_clause ::=#
group_by_clause ::=#
rollup_cube_clause ::=#
grouping_sets_clause ::=#
grouping_expression_list ::=#
expression_list ::=#
order_by_clause ::=#
limit_clause ::=#
for_update_clause ::=#
Prerequisites#
The SYS user, the owner of the schema containing the table, users having the SELECT ANY TABLE system privilege, and users having the SELECT privilege for the specified table can select data tables using this statement.
Description#
A SELECT statement or subquery is used to retrieve data from one or more tables or views.
with_clause#
The WITH query_name clause bestows a name to the subquery that follows the AS clause. The name bestowed to the subquery is referenced from various positions within the main query.
Altibase processes with_clause as an inline view or a temporary table to optimize its performance results. In general, the WITH query_name clause gives a name to the subquery that follows the AS clause. The name given to the subquery is referenced from various positions within the main query.
However, in a recursive WITH clause (hereinafter, called 'recursive with' or 'a recursive query'), column alias should be specified followed by the query_name and the query using a set operator can be repeatedly executed within a subquery. In the subquery, two queries exist on the basis of a set operator, the former query is the main query(a reference query), and the latter query becomes a recursive query. The recursive query can output the maximum value(default value:1000) specified in the RECURSION_LEVEL_MAXIMUM property. Refer to the Altibase Properties in General Reference manual for in-depth information.
The user can specify this clause in the main query and most types of subqueries.
Restrictions:#
- Only one with_clause can be specified for each SQL statement. Query_name is not allowed in the subquery that recursively defines itself. When defining multiple query names within the win_clause, however, a previously defined query name is allowed in the following subquery.
- The position of a main query and a recursive query should not be modified within the RECURSIVE WITH clause. Moreover, the query_name should be explicitly referenced. However, aggregate functions, DISTINCT keywords, and GROUP BY clause cannot be used in the recursive query.
- A subquery referencing the query_name cannot be used in the main query in the RECURSIVE WITH clause.
- A view referencing the query_name or a table located in the right of an outer join cannot be used in a recursive query in the RECURSIVE WITH clause.
TOP (expr)#
This clause specifies the number of rows to be returned from the query result set. Only integers are valid for expr.
select_list Clause#
The DISTINCT keyword is used to specify that duplicate records are to be removed from the result set before it is returned.
If a SELECT statement contains a GROUP BY clause, then only constants, aggregate functions, the expressions in the GROUP BY clause, and expressions that are combinations of the foregoing can be specified in the SELECT list.
If the SELECT list is composed of expressions or constants that do not include columns, the FROM clause can be omitted.
The specification of only the asterisk symbol(*) in the SELECT list represents every column of every table and view in the FROM clause. What the asterisk symbol represents does not change, even if it is specified with a column or expression.
FROM Clause#
An alias cannot be used more than once in a FROM clause. When the same table is used more than once in a FROM clause, different aliases must be specified.
A maximum of 32 different tables or views can be cited in a FROM clause.
OUTER JOIN#
This is an extended SQL form of JOIN for processing data that do not satisfy the join condition. Unlike an (INNER) JOIN, which only returns records having corresponding key values from two tables, an OUTER JOIN returns all of the data from one of the two tables. When a record in the returned result set consists of a row from one table that does not have a matching row from the other table, empty columns are filled with NULL values.
In-line Views#
A subquery in a FROM clause is called an "inline view".
Lateral View#
An inline view in a FROM clause cannot reference another object in the FROM clause or a query of a higher level. Yet, if the inline view in the FROM clause is defined as a lateral view, it becomes possible to reference objects outside the inline view.
To define an inline view as a lateral view, the LATERAL or APPLY keyword must be specified in front of the inline view. However, a lateral view can only reference objects that are specified on the left side of the lateral view. Even if an inline view is defined as a lateral view, if the lateral view does not reference an external object, the Altibase server processes the lateral view as a normal inline view.
The APPLY keyword defines an inline view as a lateral view and also joins the object on the left of the APPLY keyword with the lateral view. Unlike a normal join statement which specifies join conditions after the ON keyword, join statements using the APPLY keyword do not need join conditions.
The following join types can be specified using the APPLY keyword. - CROSS APPLY specifies an inner join operation between the left object and the lateral view. - OUTER APPLY specifies a left outer join operation between the left object and the lateral view.
An error message is returned if a lateral view is used in the following circumstances.
- If a fixed table is referenced in a lateral view.
- If the PIVOT clause or the UNPIVOT clause is used in a lateral view.
- If an object on the right of a lateral view is referenced within the lateral view.
- If a right outer join or full outer join operation is performed between an object referenced by a lateral view and the lateral view.
- If the LATERAL keyword and the APPLY keyword are used together.
- If the APPLY keyword and the ON clause are used together.
pivot_clause#
pivot_clause simultaneously performs a data aggregation operation and rearranges the data in separate rows into columns. This presents the data in a format that is easier to read than when using two columns in a GROUP BY clause.
For convenience, pivot_clause is usually used with inline views to avoid the difficulty of naming certain columns that result from a large number of column outputs or transformation operations.
pivot_clause performs the following steps:
-
pivot_clause first performs a grouping operation, just like a GROUP BY clause. The results are grouped according to all of the columns that are not referred to in pivot_clause, and according to the values specified in pivot_in_clause.
-
pivot_clause then arranges the resulting grouping columns and aggregate values in cross-tabular form.
pivot_for_clause#
pivot_for_clause displays the name of the corresponding column when the value specified for pivot_in_clause is changed to columnar format.
pivot_in_clause#
pivot_in_clause is used to specify values found in the columns specified in pivot_for_clause. These values will be used as column names in the pivot operation.
unpivot_clause#
unpivot_clause returns column data as rows.
The INCLUDE|EXCLUDE NULLS option specifies whether or not to allow columns that are created with the UNPIVOT clause to contain NULL values. EXCLUDE NULL returns results with no NULLS; on omission, this is the default value.
column_name specifies the names of the columns that contain the values that correspond to the column to be generated with the UNPIVOT clause.
The number of columns used in pivot_for_clause and pivot_in_clause must be the same as the number of columns used in unpivot_clause. The number of aliases must also be the same.
unpivot ( ( column, column ) for ( column, column ) in ( ( column, column ) as (
column, column ) ) );
The following statement does not support different numbers of columns.
unpivot ( ( column, column ) for column in ( ( column, column ) as column ) );
Table Function#
The Table function outputs results by converting into a table format when the associative array type or record type are used in the user defined-function.
WHERE condition clause#
For information about the use of conditions in the WHERE clause, please refer to Chapter 8: SQL Conditions
Hierarchical Query clause#
A hierarchical query is a query statement that outputs data in a hierarchical manner. If a table contains data with a parent-child relationship, this query statement outputs the parent-child relationship in a hierarchical order. For root rows that satisfy a given search condition, a hierarchical condition is evaluated to retrieve rows among the rows and its child rows.
If hierarchical data is queried using the ORDER BY clause or the GROUP BY clause, caution is required as the hierarchical order specified by the CONNECT BY clause will be overridden. However, the use of the ORDER SIBLINGS BY clause preserves the hierarchy while ordering rows of siblings of the same parent
START WITH clause#
This clause specifies a condition that identifies the root row of a hierarchical query. All rows that satisfy this condition are used as root rows. On omission, Altibase treats every row of the table as root rows of the hierarchy.
The ROWNUM pseudocolumn cannot be used in this clause.
CONNECT BY clause#
This clause specifies a condition that identifies the relationship between parent rows and child rows of the hierarchy.
The CONNECT BY clause cannot include subqueries and cannot be used with a join.
The CONNECT BY clause must precede the ORDER BY, GROUP BY, HAVING clause after the WHERE clause.
The NOCYCLE keyword instructs the database to return all rows preceding the occurrence of a loop from a query, regardless of the existence of a loop in the result set of the hierarchical query.
The SYS_CONNECT_BY_PATH function can easily retrieve PATH information up to the current row in a hierarchical query. For further information on this function, refer to "Chapter6: SQL Functions".
PRIOR Operator#
The PRIOR operator is used to distinguish previously searched rows from the current row. Use of the PRIOR operator is mandatory in order to refer to the parent row.
The PRIOR operator is only valid in the SELECT list, WHERE clause or CONNECT BY clause of query statements that include the CONNECT BY clause.
If set operators (UNION, INTERSECT, etc) are used in the query, the PRIOR operator cannot be included in the ORDER BY clause.
CONNECT_BY_ROOT Operator#
CONNECT_BY_ROOT is a unary operator that is valid only in hierarchical queries. If a column is qualified with this operator, the column value of the root row is returned.
This operator cannot be used in the START WITH condition or the CONNECT BY condition.
CONNECT_BY_ISLEAF Pseudocolumn#
THE CONNECT_BY_ISLEAF pseudocolumn returns 1 if the current row is a leaf node of a tree defined by the CONNECT BY condition; if not, it returns 0.
LEVEL Pseudocolumn#
SELECT statements that contain hierarchical queries can contain the LEVEL pseudocolumn in select_list. The LEVEL pseudocolumn indicates the hierarchical distance between the root record and subordinate records that have parent-child relationships between them. In other words, LEVEL is 1 for a root record, 2 for a child record, 3 for a grandchild record, and so on.
In addition to select_list, the LEVEL pseudocolumn can also be used in the WHERE, ORDER BY, GROUP BY, and HAVING clauses. Additionally, the LEVEL pseudocolumn can be used in select_list even in a query that does not have a CONNECT BY clause, for example:
select level from t1;
IGNORE#
When the hierarchical relationships between records form a loop, Altibase returns an error. (In this context, the term "loop", in its simplest form, indicates the situation where one row is both the parent and child of another row.) However, if the IGNORE LOOP option is used, the formation of a loop during query execution does not raise an error; instead, the records that form the loop are removed from the query result set
GROUP BY clause#
The GROUP BY clause is used to group records that have the same value for one or more given expression(s) and return a single row of aggregate information for each group.
The groups that are returned cannot be limited using a WHERE condition. Instead, the HAVING clause is used to restrict the groups of returned rows to those groups for which the specified condition is TRUE. If the HAVING clause is omitted, one record will be returned for every group.
Locate the GROUP BY and HAVING clauses after the WHERE clause and hierarchical_clause. The ORDER BY clause, if present, must appear at the very end of a SQL statement.
rollup_cube_clause, grouping_sets_clause#
ROLLUP, CUBE and GROUPING SETS are extensions of the GROUP BY clause and can specify multiple grouping sets. ROLLUP, CUBE or GROUPING SETS generate the same results as combining a multiple number of queries which contain the GROUP BY clause with UNION ALL.
Restrictions:#
- ROLLUP, CUBE or GROUPING SETS can only be specified once in the GROUP BY clause.
- Aggregate functions that take subqueries as arguments are not valid as a SELECT target.
- ROLLUP, CUBE or GROUPING SETS cannot be used with window functions.
- A maximum number of 15 expressions can be specified in the CUBE clause.
- GROUPING SETS and nested aggregate functions cannot be used together.
ROLLUP#
ROLLUP is used with the GROUP BY clause and returns detailed information on result sets grouped by the GROUP BY clause.
When ROLLUP is used with the SUM operator, it calculates not only the grand total, but also the subtotals of the grouping columns it specifies.
ROLLUP executes GROUP BY for (n+1) times, where n is the number of grouping columns. For example, if ROLLUP(a,b,c) is specified in the GROUP BY clause, the following combination of groups is returned:
(a,b,c), (a,b), (a), ( )
Partial ROLLUP#
When ROLLUP is operated only partially on grouping columns of the GROUP BY clause as in the following example, this is called a 'Partial ROLLUP':
GROUP BY a, ROLLUP(b, c), d
In this case, the following combination of groups is returned:
(a, d, b, c), (a, d, b), (a, d)
Using Composite Columns#
A list of composite columns can be specified in the ROLLUP clause as below:
GROUP BY ROLLUP((a, b), (c, d))
In this case, the following combination of groups is returned:
(a, b, c, d), (a, b), ( )
CUBE#
CUBE generates groupings of all possible combinations on specified grouping columns. CUBE creates groupings on 2n combinations, where n is the number of grouping columns. For example, if GROUP BY CUBE(a,b,c) is specified, the following (23=8) combinations are returned:
(a,b,c), (a,b), (a,c), (b,c), (a), (b), (c), ( )
Partial CUBE#
Partial CUBE is similar to Partial ROLLUP; all possible combinations of columns specified for the CUBE operator are combined with the columns outside the CUBE to determine the group to be returned. The syntax for partial CUBE is as below:
GROUP BY a, CUBE(b, c), d
In this case, the subtotals for the following combination are returned:
(a, d, b, c), (a, d, b), (a, d, c), (a, d)
Using Composite Columns#
A list of composite columns can be specified in the CUBE clause as below:
GROUP BY CUBE((a, b), (c, d))
In this case, the following combination of groups is returned:
(a, b, c, d), (a, b), (c, d), ( )
GROUPING SETS#
If you only need some of the grouping sets generated by ROLLUP or CUBE, GROUPING SETS allows you to specify only the grouping sets you need.
When used with the SUM operation, it calculates the grand total of each group specified for GROUPING SETS.
GROUPING SETS executes GROUP BY for n times, where n is the number of grouping columns. For example, if GROUPING SETS(a, b, c) is specified in the GROUP BY clause, the following combination of groups are returned.
(a), (b), (c)
Partial GROUPING SETS#
When GROUPING SETS is operated only partially on grouping columns of the GROUP BY clause as in the following example, this is called a 'Partial GROUPING SETS':
GROUP BY a, GROUPING SETS(b, c), d
In this case, the following combination of groups is returned:
(a, b, d), (a, c, d)
Using Composite Columns#
A list of composite columns can be specified in the GROUPING SETS clause as below:
GROUP BY GROUPING SETS((a, b), (c, d))
When two or more columns exist in inner parentheses in the GROUPING SETS list as above, it is treated as one column. The following combination of groups are returned:
(a, b), (c, d)
Using Empty Groups#
An empty group can be specified with empty parentheses ("()") in the GROUPING SETS clause as below:
GROUP BY GROUPING SETS((), a, b, c)
An empty group can be used to calculate the grand total without groupings.
HAVING condition clause#
The HAVING clause can only comprise constants, aggregate functions, the expressions in the GROUP BY clause, and expressions that are combinations of the foregoing. If the HAVING clause is omitted, one record will be returned for every group.
This clause is used to restrict the returned rows to those pertaining to groups for which the specified condition is TRUE.
For more information about the use of conditions in the HAVING clause, please refer to Chapter 8: SQL Conditions.
UNION (ALL), INTERSECT, MINUS#
The set operators combine the rows returned by two SELECT statements into a single result. The number and data types of the columns returned by each of the queries must be the same, but the column lengths can be different. The names of the columns in the result set will be the names of the expressions in the select_list preceding the set operator.
For more about set operators, please refer to Chapter 5: Set Operators in this manual.
ORDER BY clause#
The ORDER BY clause is used to set the order in which the records returned by the statement are presented. The result set can be sorted in ascending or descending order. The default order is ascending order.
Without an ORDER BY clause, there is no guarantee that the resultant records will be returned in a consistent order when the same query is repeatedly executed.
An ORDER BY clause can be used only once in a SELECT statement. It cannot be used in a subquery.
If the elements in the ORDER BY are specified as expressions, the records are sorted according to the result of evaluation of the expressions. The expressions are based on columns in select_list, or in tables or views in the FROM clause. If the elements in the ORDER BY clause are specified according to their position in select_list, the search results are sorted according to the returned values. The positions must be indicated using integers.
When set operators (UNION, INTERSECT, etc.) are used, only the position or the alias of the search target can be used in the ORDER BY clause.
Multiple expressions can be specified in the ORDER BY clause. The result set is first sorted based on the values for the first expression. Records having the same values for the first expression are then sorted based on their values for the second expression, and so on. If an ascending index exists for the column, the database places NULL values after all others when sorting in ascending order and preceding all others when sorting in descending order.
If a descending index exists for the column, the database places NULL values preceding all others when sorting in ascending order and after all others when sorting in descending order. If no index exists for the column, the database places NULL values after all others, regardless of the sort order.
If the DISTINCT keyword is used in the SELECT statement, then only expressions that appear in the SELECT list or combinations of those expressions can be used in the ORDER BY clause.
If a GROUP BY clause is present, then the following expressions can be used in the ORDER BY clause:
- constants
- aggregate functions
- the expressions in the GROUP BY clause
- expressions that are combinations of the foregoing
With the NULLS FIRST or NULLS LAST keyword, the ORDER BY clause can be used to position NULL at the beginning or end of the order
LIMIT clause#
The LIMIT clause can be used to limit the number of rows returned by a query.
- row_offset: This is used to specify the first record to return. If omitted, the first record in the result set will be returned.
- row_count: This is used to specify the number of records to retrieve.
The LIMIT clause can also be used in subqueries.
FOR UPDATE clause#
This is used to lock the records in the result set so that other users cannot lock or edit the records until the transaction has been completed.
The WAIT option specifies how long to wait to acquire a table lock. Applicable time units are seconds, milliseconds (msec, 1/1000 second), microsecond (user,1/1000000 seconds), unless otherwise specified, seconds are applied. On the other hand, the NOWAIT option forces the table not to wait if the table to acquire the lock is already locked by another transaction.
The FOR UPDATE clause can only be used with the main query of a SQL statement; it cannot be used with subqueries. Therefore, the following usage is invalid:
select eno from employees where (select eno from departments for update);
The FOR UPDATE clause cannot be used together with the DISTINCT or GROUP BY clauses, aggregate functions, or set operators (UNION, INTERSECT, etc).
Joins#
A join is a statement which combines rows from two or more tables, views or materialized views. A join is performed when multiple tables exist in the FROM clause of a query.
A join condition defines the method with which to combine two tables. A join condition can be specified in either the FROM clause or the WHERE clause. A column in the join condition need not exist in the SELECT list. A join condition typically compares two columns from separate tables; Altibase combines rows whose join condition is TRUE from separate tables and returns them.
When joining three or more tables, Altibase joins two tables first and then joins the result with the remaining tables. The query optimizer determines the join order by referencing join conditions, indexes, statistical information and etc.
Note: LOB columns cannot be used for join conditions.
Altibase supports the following joins:
- Cross Join
- Inner Join
- Outer Join
- Semi Join
- Anti Join
Cross Join#
A cross join combines two tables without a join condition. Each row of a table is combined with the respective row of another table. This is also known as Cartesian Products.
The following is an example of a cross join query.
SELECT * FROM T1, T2;
Inner Join#
An inner join indicates a general join which combines only the rows matching the join condition from two tables and returns them.
The following is an example of an inner join query.
SELECT * FROM T1, T2 WHERE T1.i1 = T2.i1;
SELECT * FROM T1 INNER JOIN T2 ON T1.i1 = T2.i1;
SELECT * FROM T1, T2, T3 WHERE T1.i1 = T2.i1 AND T2.i1 < T3.i2;
Outer Join#
An outer join distinguishes the rows matching the join condition from two separate tables and returns them. The difference between an inner join and an outer join is that, when only one table has a row matching the join condition and another table does not, an inner join does not return the given row while the outer join returns the value of the table with no rows matching the join condition as NULL.
Outer joins are composed of the following three types:
Left Outer Join#
When Table A is specified to the left, and Table B is specified to the right of the LEFT OUTER JOIN keyword, all rows matching the join condition from Table A are returned. If Table B has no rows matching the join condition, NULL is returned for the corresponding row in the result set.
The following is an example of a left outer join query.
SELECT * FROM A LEFT OUTER JOIN B ON A.c1 = B.c1;
SELECT * FROM A, B WHERE A.c1 = B.c1(+);
Right Outer Join#
When Table A is specified to the left, and Table B is specified to the right of the RIGHT OUTER JOIN keyword, all rows matching the join condition from Table B are returned. If Table A has no rows matching the join condition, NULL is returned for the corresponding row in the result set.
The following is an example of a right outer join query.
SELECT * FROM A RIGHT OUTER JOIN B ON A.c1 = B.c1;
SELECT * FROM A, B WHERE A.c1(+) = B.c1;
Full Outer Join#
When Table A is specified to the left, and Table B is specified to the right of the FULL OUTER JOIN keyword, the rows matching the join condition are returned, regardless of whether or not the rows of both tables match the join condition. For tables with no rows matching the join condition, NULL is returned for the corresponding row in the result set.
The following is an example of a full outer join query
SELECT * FROM A FULL OUTER JOIN B ON A.c1 = B.c1;
Semi Join#
A semi join between Table A and Table B returns all rows of Table A existing in Table B. A row is returned only once, regardless of whether or not multiple rows in Table B match the row of Table A. The following is an example of a semi join query.
SELECT * FROM T1 WHERE EXISTS ( SELECT i1 FROM T2 WHERE T1.i1 = T2.i1 );
SELECT * FROM T1 WHERE i1 IN ( SELECT i1 FROM T2 );
Anti Join#
An anti-join between Table A and Table B returns only the rows of Table A which are nonexistent in Table B.
SELECT * FROM T1 WHERE NOT EXISTS ( SELECT i1 FROM T2 WHERE T1.i1 = T2.i1 );
SELECT * FROM T1 WHERE i1 NOT IN ( SELECT i1 FROM T2 );
Examples#
The results of the joins described above are demonstrated with the tables employee and dept.
CREATE TABLE employee(name VARCHAR(10), empid INTEGER, deptname VARCHAR(20));
CREATE TABLE dept(deptname VARCHAR(20), manager VARCHAR(10));
INSERT INTO employee VALUES('Harry', 3415, 'Finance');
INSERT INTO employee VALUES('Sally', 2241, 'Sales');
INSERT INTO employee VALUES('George', 3401, 'Finance');
INSERT INTO employee VALUES('Harriet', 2202, 'Production');
INSERT INTO dept VALUES('Sales','Bob');
INSERT INTO dept VALUES('Sales','Thomas');
INSERT INTO dept VALUES('Production','Katie');
INSERT INTO dept VALUES('Production','Mark');
The following is a corss join query and its results.
iSQL> SELECT * FROM employee, dept;
NAME EMPID DEPTNAME DEPTNAME MANAGER
--------------------------------------------------------------------------------------
Harry 3415 Finance Sales Bob
Harry 3415 Finance Sales Thomas
Harry 3415 Finance Production Katie
Harry 3415 Finance Production Mark
Sally 2241 Sales Sales Bob
Sally 2241 Sales Sales Thomas
Sally 2241 Sales Production Katie
Sally 2241 Sales Production Mark
George 3401 Finance Sales Bob
George 3401 Finance Sales Thomas
George 3401 Finance Production Katie
George 3401 Finance Production Mark
Harriet 2202 Production Sales Bob
Harriet 2202 Production Sales Thomas
Harriet 2202 Production Production Katie
Harriet 2202 Production Production Mark
16 rows selected.
The following is an inner join query and its results.
iSQL> SELECT * FROM employee A, dept B WHERE A.deptname = B.deptname;
NAME EMPID DEPTNAME DEPTNAME MANAGER
--------------------------------------------------------------------------------------
Sally 2241 Sales Sales Thomas
Sally 2241 Sales Sales Bob
Harriet 2202 Production Production Mark
Harriet 2202 Production Production Katie
4 rows selected.
The following is a left outer join query and its results.
iSQL> SELECT * FROM employee A LEFT OUTER JOIN dept B ON A.deptname = B.deptname;
NAME EMPID DEPTNAME DEPTNAME MANAGER
--------------------------------------------------------------------------------------
Harry 3415 Finance
Sally 2241 Sales Sales Thomas
Sally 2241 Sales Sales Bob
George 3401 Finance
Harriet 2202 Production Production Mark
Harriet 2202 Production Production Katie
6 rows selected.
The following is a right outer join query and its results.
iSQL> SELECT * FROM employee A RIGHT OUTER JOIN dept B ON A.deptname = B.deptname;
NAME EMPID DEPTNAME DEPTNAME MANAGER
--------------------------------------------------------------------------------------
Sally 2241 Sales Sales Bob
Sally 2241 Sales Sales Thomas
Harriet 2202 Production Production Katie
Harriet 2202 Production Production Mark
4 rows selected.
The following is a semi outer join query and its results.
iSQL> SELECT * FROM employee A WHERE EXISTS ( SELECT deptname FROM dept B WHERE A.deptname = B.deptname );
NAME EMPID DEPTNAME
--------------------------------------------------
Sally 2241 Sales
Harriet 2202 Production
2 rows selected.
The following is an anti outer join query and its results.
iSQL> SELECT * FROM employee A WHERE NOT EXISTS ( SELECT deptname FROM dept B WHERE A.deptname = B.deptname );
NAME EMPID DEPTNAME
--------------------------------------------------
Harry 3415 Finance
George 3401 Finance
2 rows selected.
HINTS Clause#
Please refer to "Hint Syntax" and "Hint List" for more detailed information.
Restrictions#
Altibase has the following restrictions on SQL query and stored procedure execution:
-
A maximum of 65536 internal tuples1 can be used to process one query.
-
A maximum of 32 tables or views can be used in a FROM clause.
-
A maximum of 32 tables or views can be used in operations within clauses such as the WHERE, GROUP BY, and ORDER BY clauses.
If the above restrictions are violated, one of the following errors will be returned.
- qpERR_ABORT_QTC_TUPLE_SHORTAGE
There are too many DML statements in the stored procedure, or the SQL query is too long. - qpERR_ABORT_QTC_TOO_MANY_TABLES
Too many tables are referenced in a phrase.
Examples#
Simple Query#
<Query> Retrieve the names, hiring dates, and salaries of all employees.
iSQL> SELECT e_firstname, e_lastname, join_date, salary
FROM employees;
E_FIRSTNAME E_LASTNAME JOIN_DATE SALARY
-----------------------------------------------------------------------
Chan-seung Moon
Susan Davenport 18-NOV-2009 1500
Ken Kobain 11-JAN-2010 2000
.
.
.
20 rows selected.
<Query> Retrieve the name, hiring date and salary of the employee with the highest salary.
iSQL> SELECT TOP (1) e_firstname, e_lastname, join_date, salary
2 FROM employees ORDER BY salary;
E_FIRSTNAME E_LASTNAME JOIN_DATE SALARY
--------------------------------------------------------------------------
Gottlieb Fleischer 24-JAN-2004 500
1 row selected.
<Query> This is an example of a query in which from the FROM clause is omitted.
iSQL> SELECT cos(0), 256;
COS(0) 256
--------------------------------------
1 256
1 row selected.
<Query> Retrieve the current date and every value of every column of the table departments.
iSQL> SELECT sysdate, *
FROM departments;
SYSDATE DNO DNAME DEP_LOCATION MGR_NO
--------------------------------------------------------------------------------------------
26-JUN-2013 1001 RESEARCH DEVELOPMENT DEPT 1 New York 16
26-JUN-2013 1002 RESEARCH DEVELOPMENT DEPT 2 Sydney 13
26-JUN-2013 1003 SOLUTION DEVELOPMENT DEPT Osaka 14
26-JUN-2013 2001 QUALITY ASSURANCE DEPT Seoul 17
26-JUN-2013 3001 CUSTOMERS SUPPORT DEPT London 4
26-JUN-2013 3002 PRESALES DEPT Peking 5
26-JUN-2013 4001 MARKETING DEPT Brasilia 8
26-JUN-2013 4002 BUSINESS DEPT Palo Alto 7
8 rows selected.
Using the WITH Subquery Clause#
<Query> Create query names dept_costs, avg_cost and use these names in the main query.
iSQL> WITH
2 dept_costs AS (
3 SELECT DNAME, SUM(salary) dept_total
4 FROM employees e, departments d
5 WHERE e.dno = d.dno
6 GROUP BY DNAME),
7 avg_cost AS (
8 SELECT SUM(dept_total)/COUNT(*) avg
9 FROM dept_costs)
10 SELECT * FROM dept_costs
11 WHERE dept_total > (SELECT avg FROM avg_cost)
12 ORDER BY DNAME;
DEPT_COSTS.DNAME DEPT_COSTS.DEPT_TOTAL
---------------------------------------------------------
BUSINESS DEPT 4190
RESEARCH DEVELOPMENT DEPT 1 4300
SOLUTION DEVELOPMENT DEPT 9753
3 rows selected.
Searching a Partitioned Table#
CREATE TABLE T1 (I1 INTEGER)
PARTITION BY RANGE (I1)
(
PARTITION P1 VALUES LESS THAN (100),
PARTITION P2 VALUES LESS THAN (200),
PARTITION P3 VALUES DEFAULT
) TABLESPACE SYS_TBS_DISK_DATA;
INSERT INTO T1 VALUES (55);
INSERT INTO T1 VALUES (123);
SELECT * FROM T1 PARTITION (P1);
I1
----------
55
SELECT * FROM T1 PARTITION (P2);
I1
----------
123
SELECT * FROM T1 PARTITION (P3);
No rows selected.
Using Search Conditions#
<Query> Display the name, title, and wage for all employees whose wage is less than $1500 USD per month, sorted by wage in descending order.
iSQL> SELECT e_firstname, e_lastname, emp_job, salary
FROM employees
WHERE salary < 1500
ORDER BY 4 DESC;
E_FIRSTNAME E_LASTNAME EMP_JOB SALARY
------------------------------------------------------------------------
Takahiro Fubuki PM 1400
Curtis Diaz planner 1200
Jason Davenport webmaster 1000
Mitch Jones PM 980
Gottlieb Fleischer manager 500
5 rows selected.
Using a Hierarchical Query#
<Query> The following query uses a CONNECT BY clause to define a hierarchical relationship in which the value of id in the parent record is equal to the value of parent_id in the child record, starting with records for which the value in the id column is 0 as the root of the hierarchy.
iSQL> CREATE TABLE hier_order(id INTEGER, parent INTEGER);
Create success.
iSQL> INSERT INTO hier_order VALUES(0, NULL);
1 row inserted.
iSQL> INSERT INTO hier_order VALUES(1, 0);
1 row inserted.
iSQL> INSERT INTO hier_order VALUES(2, 1);
1 row inserted.
iSQL> INSERT INTO hier_order VALUES(3, 1);
1 row inserted.
iSQL> INSERT INTO hier_order VALUES(4, 1);
1 row inserted.
iSQL> INSERT INTO hier_order VALUES(5, 0);
1 row inserted.
iSQL> INSERT INTO hier_order VALUES(6, 0);
1 row inserted.
iSQL> INSERT INTO hier_order VALUES(7, 6);
1 row inserted.
iSQL> INSERT INTO hier_order VALUES(8, 7);
1 row inserted.
iSQL> INSERT INTO hier_order VALUES(9, 7);
1 row inserted.
iSQL> INSERT INTO hier_order VALUES(10, 6);
1 row inserted.
iSQL> SELECT ID, parent, LEVEL
FROM hier_order START WITH id = 0 CONNECT BY PRIOR id = parent ORDER BY level;
ID PARENT LEVEL
------------------------------------------------
0 1
6 0 2
5 0 2
1 0 2
10 6 3
4 1 3
7 6 3
3 1 3
2 1 3
8 7 4
9 7 4
11 rows selected.
[Figure 4‑1] Hierarchically Structured Data
<Query> The START WITH clause is omitted from the following query, meaning that all rows in the table are used as root rows. This query also returns all records that satisfy the condition (PRIOR id = parent).
iSQL> SELECT id, parent, level
FROM hier_order CONNECT BY PRIOR id = parent ORDER BY id;
ID PARENT LEVEL
------------------------------------------------
0 1
1 0 1
1 0 2
2 1 1
2 1 3
2 1 2
3 1 2
3 1 1
3 1 3
4 1 1
4 1 2
4 1 3
5 0 1
5 0 2
6 0 2
6 0 1
7 6 1
7 6 2
7 6 3
8 7 3
8 7 1
8 7 2
8 7 4
9 7 2
9 7 3
9 7 4
9 7 1
10 6 1
10 6 2
10 6 3
30 rows selected.
<Query> The following hierarchical query uses the IGNORE LOOP clause to remove records that formed loops during query execution from the result set and return the rest of the result set, rather than returning an error.
iSQL> CREATE TABLE triple(
num INTEGER,
tri INTEGER,
PRIMARY KEY(num, tri));
Create success.
iSQL> CREATE OR REPLACE PROCEDURE proc_tri
AS
v1 INTEGER;
BEGIN
FOR v1 IN 1 .. 1000 LOOP
INSERT INTO triple VALUES(v1, v1 * 3);
END LOOP;
INSERT INTO triple VALUES(1, 1);
END;
/
Create success.
iSQL> EXEC proc_tri;
Execute success.
iSQL> SELECT num, tri, level
FROM triple
WHERE num < 3001
START WITH num = 1
CONNECT BY PRIOR tri = num
IGNORE LOOP;
NUM TRI LEVEL
------------------------------------------------
1 1 1
1 3 2
3 9 3
9 27 4
27 81 5
81 243 6
243 729 7
729 2187 8
1 3 1
3 9 2
9 27 3
27 81 4
81 243 5
243 729 6
729 2187 7
15 rows selected.
<Query> Display the root node of each id using the CONNECT_BY_ROOT operator in a hierarchical query.
CREATE TABLE hier_order(id INTEGER, name varchar(10), parent INTEGER);
INSERT INTO hier_order VALUES(0, 'Moon', NULL);
INSERT INTO hier_order VALUES(1, 'Davenport', 0);
INSERT INTO hier_order VALUES(2, 'Kobain', 1);
INSERT INTO hier_order VALUES(3, 'Foster', 1);
INSERT INTO hier_order VALUES(4, 'Ghorbani', 1);
INSERT INTO hier_order VALUES(5, 'Momoi', 0);
INSERT INTO hier_order VALUES(6, 'Fleischer', 0);
INSERT INTO hier_order VALUES(7, 'Wang', 6);
INSERT INTO hier_order VALUES(8, 'Diaz', 7);
INSERT INTO hier_order VALUES(9, 'Liu', 7);
INSERT INTO hier_order VALUES(10, 'Hammond', 6);
iSQL> SELECT id, CONNECT_BY_ROOT id "Root_Id"
FROM hier_order
WHERE LEVEL > 1
START WITH id = 0
CONNECT BY PRIOR id = parent
ORDER BY id, "Root_Id";
ID Root_Id
---------------------------
1 0
2 0
3 0
4 0
5 0
6 0
7 0
8 0
9 0
10 0
10 rows selected.
<Query> Display whether each row is a leaf node or not using the CONNECT_BY_ISLEAF pseudocolumn in a hierarchical query.
iSQL> SELECT id, CONNECT_BY_ISLEAF "IsLeaf",
LEVEL
FROM hier_order
START WITH id = 0
CONNECT BY PRIOR id = parent
ORDER BY id, "IsLeaf";
ID IsLeaf LEVEL
----------------------------------------------------------
0 0 1
1 0 2
2 1 3
3 1 3
4 1 3
5 1 2
6 0 2
7 0 3
8 1 4
9 1 4
10 1 3
11 rows selected.
<Query> Using the ORDER SIBLINGS BY clause, preserve the hierarchy and order by name.
iSQL> SELECT name, id, parent, LEVEL
FROM hier_order
START WITH id = 0
CONNECT BY PRIOR id = parent
ORDER SIBLINGS BY name;
NAME ID PARENT LEVEL
--------------------------------------------------------------
Moon 0 1
Davenport 1 0 2
Foster 3 1 3
Ghorbani 4 1 3
Kobain 2 1 3
Fleischer 6 0 2
Hammond 10 6 3
Wang 7 6 3
Diaz 8 7 4
Liu 9 7 4
Momoi 5 0 2
11 rows selected.
Recursive Query#
<Query> The hierarchical query for obtaining rows rooted in a row with the ID value 0 is as follows. (circular data).
iSQL> INSERT INTO hier_order VALUES(7, 9);
1 row inserted.
iSQL> WITH q1 (q1_i1,q1_i2, lvl) as
2 (
3 SELECT id,parent,1 FROM hier_order WHERE id = 0
4 UNION ALL
5 SELECT a.id,a.parent,lvl+1 from hier_order a, q1 b where a.parent = b.q1_i1
6 )
7 select * from q1 limit 18;
Q1_I1 Q1_I2 LVL
----------------------------------------
0 1
1 0 2
5 0 2
6 0 2
2 1 3
3 1 3
4 1 3
7 6 3
10 6 3
8 7 4
9 7 4
7 9 5
8 7 6
9 7 6
7 9 7
8 7 8
9 7 8
7 9 9
18 rows selected.
Using GROUP BY#
<Query> Calculate the average salary for each department.
iSQL> SELECT dno, AVG(salary) AS avg_sal
FROM employees
GROUP BY dno;
DNO AVG_SAL
---------------------------
1001 2150
1002 1340
1003 2438.25
2001 1400
3001 1800
3002 2500
4001 1550
4002 1396.66667
1500
9 rows selected.
- All columns in the SELECT list where the aggregate fucntion is not used must be in the GROUP BY clause.
- If the user wants to give the column a nickname, or if the user wants to use a nickname that is different from the column name, write the nickname to use after the column name, as in AS avg_sal above. The AS keyword can be omitted when creating column aliases.
- If two hypens ("-") are given, all subsequent parts of the line are treated as comments.
<Query> Use the GROUP BY clause in multiple columns to output the total amount of salary paid for each position within each department.
iSQL> SELECT dno, emp_job, COUNT(emp_job) num_emp, SUM(salary) sum_sal
FROM employees
GROUP BY dno, emp_job;
DNO EMP_JOB NUM_EMP SUM_SAL
-------------------------------------------------------------------
3002 CEO 1
designer 1 1500
1001 engineer 1 2000
3001 PL 1 1800
3002 PL 1 2500
1002 programmer 1 1700
4002 manager 1 500
4001 manager 1
4001 planner 2 3100
1003 programmer 1 4000
1003 webmaster 2 3750
4002 sales rep 3 3690
1002 PM 1 980
1003 PM 1 2003
1001 manager 1 2300
2001 PM 1 1400
16 rows selected.
<Query> Display the average salary for a department whose average salary exceeds $1500 USD.
iSQL> SELECT dno, AVG(salary)
FROM employees
WHERE AVG(salary) > 1500
GROUP BY dno;
[ERR-31061 : An aggregate function is not allowed here.
0003 : WHERE AVG(SALARY) > 1500000
^ ^
]
<Query> The error shown above can be corrected by using the HAVING clause.
iSQL> SELECT dno, AVG(salary)
FROM employees
GROUP BY dno
HAVING AVG(salary) > 1500;
DNO AVG(SALARY)
---------------------------
1001 2150
1003 2438.25
3001 1800
3002 2500
4001 1550
5 rows selected.
<Query> Display the product number of more than two orders and the total number of items.
iSQL> SELECT gno, COUNT(*)
FROM orders
GROUP BY gno
HAVING COUNT(*) > 2;
GNO COUNT
------------------------------------
A111100002 3
C111100001 4
D111100008 3
E111100012 3
4 rows selected.
<Query> Display the order numbers of two or more items and their average order quanity in December in order of average order quantity.
iSQL> SELECT gno, AVG(qty) month_avg
FROM orders
WHERE order_date BETWEEN '01-Dec-2011' AND '31-Dec-2011'
GROUP BY gno
HAVING COUNT(*) > 1
ORDER BY AVG(qty);
GNO MONTH_AVG
---------------------------
A111100002 35
D111100003 300
D111100004 750
C111100001 1637.5
D111100010 1750
D111100002 1750
E111100012 4233.33333
D111100008 5500
8 rows selected.
<Query> Use ROLLUP in the GROUP BY clause to subtotal salaries for the following three combination: (dno, sex), (dno), (total).
iSQL> select dno, sex, sum(SALARY) from employees group by rollup( dno, sex);
DNO SEX SUM(SALARY)
---------------------------------
1001 F 2300
1001 M 2000
1001 4300
1002 M 2680
1002 2680
1003 F 4000
1003 M 5753
1003 9753
2001 M 1400
2001 1400
3001 M 1800
3001 1800
3002 M 2500
3002 2500
4001 M 3100
4001 3100
4002 F 1890
4002 M 2300
4002 4190
F 1500
1500
31223
22 rows selected.
<Query> Use CUBE in the GROUP BY clause to subtotal salaries for all comination of grouping columns: (dno, sex), (dno), (sex), (total).
iSQL> select dno, sex, sum(SALARY) from employees group by cube( dno, sex);
DNO SEX SUM(SALARY)
---------------------------------
31223
1001 F 2300
1001 M 2000
1001 4300
1002 M 2680
1002 2680
1003 F 4000
1003 M 5753
1003 9753
2001 M 1400
2001 1400
3001 M 1800
3001 1800
3002 M 2500
3002 2500
4001 M 3100
4001 3100
4002 F 1890
4002 M 2300
4002 4190
F 1500
1500
F 9690
M 21533
24 rows selected.
<Query> Use the GROUPING SETS in the GROUP BY clause to calculate salary subtotals for the following three groupings: (dno,sex), (dno), ().
iSQL> SELECT dno, sex, SUM(salary)
FROM employees
GROUP BY GROUPING SETS( (dno, sex), dno, () );
DNO SEX SUM(SALARY)
---------------------------------
3002 M 2500
F 1500
1001 M 2000
3001 M 1800
1002 M 2680
4002 M 2300
4001 M 3100
1003 F 4000
1003 M 5753
4002 F 1890
1001 F 2300
2001 M 1400
3002 2500
1500
1001 4300
3001 1800
1002 2680
4002 4190
4001 3100
1003 9753
2001 1400
31223
22 rows selected.
Using ORDER BY#
<Query> Display the names, department numbers, and wages of all employees. Sort them according to department number and then according to wage in descending order.
iSQL> SELECT e_firstname, e_lastname, dno, salary
FROM employees
ORDER BY dno, salary DESC;
E_FIRSTNAME E_LASTNAME DNO SALARY
-------------------------------------------------------------------------
Wei-Wei Chen 1001 2300
Ken Kobain 1001 2000
Ryu Momoi 1002 1700
Mitch Jones 1002 980
Elizabeth Bae 1003 4000
.
.
.
20 rows selected.
<Query> Display the names and wages of all employees, sorted first by department number and then by wage in descending order. (Note that it is possible to sort the results on the basis of columns that do not appear in select_list.)
iSQL> SELECT e_firstname, e_lastname, salary
FROM employees
ORDER BY dno, salary DESC;
E_FIRSTNAME E_LASTNAME SALARY
------------------------------------------------------------
Wei-Wei Chen 2300
Ken Kobain 2000
Ryu Momoi 1700
Mitch Jones 980
Elizabeth Bae 4000
.
.
.
20 rows selected.
Using an Operator#
<Query> Display the name and the total value of all inventory for each inventory item.
iSQL> SELECT gname, (stock*price) inventory_value
FROM goods;
GNAME INVENTORY_VALUE
-------------------------------------
IM-300 78000000
IM-310 9800000
NT-H5000 27924000
.
.
.
30 rows selected.
Using alias_name#
<Query> Search by specifying an alias (region name) for the department location.
iSQL> SELECT dname, 'District Name', dep_location location
FROM departments;
DNAME 'District Name' LOCATION
------------------------------------------------
Applied Technology Team District Name Mapo
Engine Development Team District Name Yeoido
Marketing Team District Name Gangnam
Planning & Management Team District Name Gangnam
Sales Team District Name Shinchon
5 rows selected.
Using LIMIT#
<Query> Display only five employees from the third record in the table employees.
iSQL> SELECT e_firstname first_name, e_lastname last_name
FROM employees
LIMIT 3, 5;
FIRST_NAME LAST_NAME
-----------------------------------------------
Ken Kobain
Aaron Foster
Farhad Ghorbani
Ryu Momoi
Gottlieb Fleischer
5 rows selected.
<Query> Display the name and salary of the employee corresponding to the first record in the manager table.
iSQL> CREATE TABLE managers(
mgr_no INTEGER PRIMARY KEY,
m_lastname VARCHAR(20),
m_firstname VARCHAR(20),
address VARCHAR(60));
Create success.
iSQL> INSERT INTO managers VALUES(7, 'Fleischer', 'Gottlieb', '44-25 YouIDo-dong Youngdungpo-gu Seoul Korea');
1 row inserted.
iSQL> INSERT INTO managers VALUES(8, 'Wang', 'Xiong', '3101 N Wabash Ave Brooklyn NY');
1 row inserted.
iSQL> INSERT INTO managers VALUES(12, 'Hammond', 'Sandra', '130 Gongpyeongno Jung-gu Daegu Korea');
1 row inserted.
iSQL> SELECT e_firstname, e_lastname, salary FROM employees WHERE eno = (SELECT mgr_no FROM managers LIMIT 1);
E_FIRSTNAME E_LASTNAME SALARY
------------------------------------------------------------
Gottlieb Fleischer 500
1 row selected.
Using FOR UPDATE#
| Transaction A | Time Point | Transaction B |
|---|---|---|
| iSQL> AUTOCOMMIT OFF; Set autocommit off success. |
iSQL> AUTOCOMMIT OFF; Set autocommit off success. |
|
| (request X lock on employees) iSQL> LOCK TABLE employees IN EXCLUSIVE MODE; Lock success. (acquire X lock on employees) iSQL> SELECT e_lastname FROM employees WHERE eno = 15; E_LASTNAME ------------------------ Davenport 1 row selected. |
1 | |
| 2 | iSQL> SELECT e_lastname FROM employees WHERE eno = 15 FOR UPDATE; (request conflicts with the X lock already held by transaction A) wait wait wait |
|
| iSQL> UPDATE employees SET ENO = 30 WHERE eno = 15; 1 row updated. iSQL> SELECT e_lastname FROM employees WHERE eno = 30; E_LASTNAME ------------------------ Davenport 1 row selected. |
3 | |
| iSQL> COMMIT; Commit success. |
4 | |
| 5 | (resume) E_LASTNAME ------------------------ No rows selected. |
Using HINTS#
Table Access Method Hints : full scan, index scan, index ascending order scan, index descending order scan, no index scan#
The following query retrieves the employee number, name, and position of all female employees.
SELECT eno, e_firstname, e_lastname, emp_job FROM employees WHERE sex = 'F';
For example, assume that an index has been defined for the gender column of the employees table, which contains many records, and that the value of the column can be 'M' or 'F'. If the number of male employees is similar to the number of female employees, querying the entire table using a full scan will be much faster than using an index scan. However, if the number of female employees is substantially lower than the number of male employees, using an index scan will be faster than scanning the entire table.
In other words, when a column contains only two different values, the optimizer assumes that each value accounts for 50% of the rows in a table, and therefore, when using a cost-based approach to find records that match one of the two values for that column, opts to perform a full table scan rather than an index scan.
In the following queries, it can be seen that the table is accessed 20 times and 4 times in order to perform a full scan and an index scan, respectively.
iSQL> SELECT /*+ FULL SCAN(employees) */ eno, e_firstname, e_lastname, emp_job
FROM employees
WHERE sex = 'F';
ENO E_FIRSTNAME E_LASTNAME EMP_JOB
------------------------------------------------
.
.
.
------------------------------------------------
PROJECT ( COLUMN_COUNT: 4, TUPLE_SIZE: 65 )
SCAN ( TABLE: EMPLOYEES, FULL SCAN, ACCESS: 20, SELF_ID: 2 )
------------------------------------------------
iSQL> CREATE INDEX gender_index ON employees(sex);
Create success.
iSQL> SELECT /*+ INDEX(employees, gender_INDEX) use gender_index because there are few female ployees */ eno, e_firstname, e_lastname, emp_job
FROM employees
WHERE sex = 'F';
ENO E_FIRSTNAME E_LASTNAME EMP_JOB
------------------------------------------------
.
.
.
------------------------------------------------
PROJECT ( COLUMN_COUNT: 4, TUPLE_SIZE: 65 )
SCAN ( TABLE: EMPLOYEES, INDEX: GENDER_INDEX, ACCESS: 4, SELF_ID: 2 )
------------------------------------------------
create view orders as
select ono, order_date, eno, cno, gno, qty from orders_01
union all
select ono, order_date, eno, cno, gno, qty from orders_02
union all
select ono, order_date, eno, cno, gno, qty from orders_03;
create index order1_gno on orders_01(gno);
create index order2_gno on orders_02(gno);
create index order3_gno on orders_03(gno);
iSQL> select /*+ index( orders,
orders1_gno, orders2_gno,orders3_gno ) */
ONO, GNO, QTY
from orders;
ONO GNO QTY
-------------------------------------------------
.
.
.
------------------------------------------------
PROJECT ( COLUMN_COUNT: 3, TUPLE_SIZE: 24 )
VIEW ( ORDERS, ACCESS: 14, SELF_ID: 6 )
PROJECT ( COLUMN_COUNT: 6, TUPLE_SIZE: 48 )
VIEW ( ACCESS: 14, SELF_ID: 5 )
BAG-UNION
PROJECT ( COLUMN_COUNT: 6, TUPLE_SIZE: 48 )
SCAN ( TABLE: ORDERS_01, INDEX: ORDERS1_GNO, ACCESS: , SELF_ID: 0 )
PROJECT ( COLUMN_COUNT: 6, TUPLE_SIZE: 48 )
SCAN ( TABLE: ORDERS_02, INDEX: ORDERS2_GNO, ACCESS: 4, SELF_ID: 1 )
PROJECT ( COLUMN_COUNT: 6, TUPLE_SIZE: 48 )
SCAN ( TABLE: ORDERS_03, INDEX: ORDERS3_GNO, ACCESS: 7, SELF_ID: 4 )
------------------------------------------------
Join Order Hints (ordered, optimized)#
<Query> Retrieve the employee number and last name and the customer's last name for every ordered product. (Use the ORDERED hint to join the employees table with the customers table and then join the result set with the orders table.)
iSQL> SELECT /*+ ORDERED */ DISTINCT o.eno, e.e_lastname, c.c_lastname
FROM employees e, customers c, orders o
WHERE e.eno = o.eno AND o.cno = c.cno;
ENO E_LASTNAME C_LASTNAME
------------------------------------------------
.
.
.
------------------------------------------------
PROJECT ( COLUMN_COUNT: 3, TUPLE_SIZE: 48 )
DISTINCT ( ITEM_SIZE: 40, ITEM_COUNT: 21, BUCKET_COUNT: 1024, ACCESS: 21, SELF_ID: 4, REF_ID: 3 )
JOIN
JOIN
SCAN ( TABLE: EMPLOYEES E, FULL SCAN, ACCESS: 20, SELF_ID: 1 )
SCAN ( TABLE: CUSTOMERS C, FULL SCAN, ACCESS: 400, SELF_ID: 2 )
SCAN ( TABLE: ORDERS O, FULL SCAN, ACCESS: 12000, SELF_ID: 3 )
------------------------------------------------
<Query> Retrieve the employee number and last name and the customer's last name for every ordered product. (Allow the optimizer to set the order in which tables are joined without considering the order in which the tables appear in the FROM clause.)
iSQL> SELECT DISTINCT o.eno, e.e_lastname, c.c_lastname
FROM employees e, customers c, orders o
WHERE e.eno = o.eno AND o.cno = c.cno;
ENO E_LASTNAME C_LASTNAME
------------------------------------------------
.
.
.
------------------------------------------------
PROJECT ( COLUMN_COUNT: 3, TUPLE_SIZE: 48 )
DISTINCT ( ITEM_SIZE: 40, ITEM_COUNT: 21, BUCKET_COUNT: 1024, ACCESS: 21, SELF_ID: 4, REF_ID: 1 )
JOIN
JOIN
SCAN ( TABLE: CUSTOMERS C, FULL SCAN, ACCESS: 20, SELF_ID: 2 )
SCAN ( TABLE: ORDERS O, INDEX: ODR_IDX2, ACCESS: 30, SELF_ID: 3 )
SCAN ( TABLE: EMPLOYEES E, INDEX: __SYS_IDX_ID_366, ACCESS: 30, SELF_ID: 1 )
------------------------------------------------
Optimizer Mode Hints (rule, cost)#
iSQL> SELECT /*+ RULE */ * FROM t1, t2 WHERE t1.i1 = t2.i1;
iSQL> SELECT /*+ COST */ * FROM t1, t2 WHERE t1.i1 = t2.i1;
Normal Form Hints (CNF, DNF)#
iSQL> SELECT /*+ CNF */ * FROM t1 WHERE i1 = 1 OR i1 = 2;
iSQL> SELECT /*+ DNF */ * FROM t1 WHERE i1 = 1 OR i1 = 2;
Join Method Hints (nested loop, hash, sort, sort merge)#
iSQL> SELECT /*+ USE_NL (t1,t2) */ * FROM t1, t2 WHERE t1.i1 = t2.i1;
iSQL> SELECT /*+ USE_HASH (t1,t2) */ * FROM t1, t2 WHERE t1.i1 = t2.i1;
iSQL> SELECT /*+ USE_SORT (t1,t2) */ * FROM t1, t2 WHERE t1.i1 = t2.i1;
iSQL> SELECT /*+ USE_MERGE (t1,t2) */ * FROM t1, t2 WHERE t1.i1 = t2.i1;
Hash Bucket Size Hints (hash bucket count, group bucket count, set bucket count)#
iSQL> SELECT /*+ HASH BUCKET COUNT (20) */ DISTINCT * FROM t1;
iSQL> SELECT * FROM t1 GROUP BY i1, i2;
iSQL> SELECT /*+ GROUP BUCKET COUNT (20) */ * FROM t1 GROUP BY i1, i2;
iSQL> SELECT * FROM t1 INTERSECT SELECT * FROM t2;
iSQL> SELECT /*+ SET BUCKET COUNT (20) */ * FROM t1 INTERSECT SELECT * FROM t2;
Push Predicate Hints#
<Query> For all orders placed during the first quarter for which the number of items ordered at one time was more than 10000, display the customer name and the product number. (Use the Push Predicate hint to join the customers table with the orders table.)
create view orders as
select ono, order_date, eno, cno, gno, qty from orders_01
union all
select ono, order_date, eno, cno, gno, qty from orders_02
union all
select ono, order_date, eno, cno, gno, qty from orders_03;
iSQL> select /*+ PUSH_PRED(orders) */ c_lastname, gno
2 from customers, orders
3 where customers.cno = orders.cno
4 and orders.qty >= 10000;
C_LASTNAME GNO
-------------------------------------
.
.
.
------------------------------------------------
PROJECT ( COLUMN_COUNT: 2, TUPLE_SIZE: 34 )
JOIN
SCAN ( TABLE: CUSTOMERS, FULL SCAN, ACCESS: 20, SELF_ID: 2 )
FILTER
[ FILTER ]
AND
OR
ORDERS.QTY >= 10000
VIEW ( ORDERS, ACCESS: 1, SELF_ID: 8 )
PROJECT ( COLUMN_COUNT: 6, TUPLE_SIZE: 48 )
VIEW ( ACCESS: 1, SELF_ID: 7 )
BAG-UNION
PROJECT ( COLUMN_COUNT: 6, TUPLE_SIZE: 48 )
SCAN ( TABLE: ORDERS_01, INDEX: ODR1_IDX2, ACCESS: 3, SELF_ID: 3 )
[ VARIABLE KEY ]
OR
AND
[ FILTER ]
AND
OR
PROJECT ( COLUMN_COUNT: 6, TUPLE_SIZE: 48 )
SCAN ( TABLE: ORDERS_02, INDEX: ODR2_IDX2, ACCESS: 4, SELF_ID: 4 )
[ VARIABLE KEY ]
OR
AND
[ FILTER ]
AND
OR
PROJECT ( COLUMN_COUNT: 6, TUPLE_SIZE: 48 )
SCAN ( TABLE: ORDERS_03, INDEX: ODR3_IDX2, ACCESS: 7, SELF_ID: 6 )
[ VARIABLE KEY ]
OR
AND
[ FILTER ]
AND
OR
------------------------------------------------
Using OUTER JOIN#
<Query> Retrieve the department numbers and employee names for all departments. (Note that the department number 5001 is output even though there are no employees in that department.)
iSQL> INSERT INTO departments VALUES('5001', 'Quality Assurance', 'Jonglo', 22);
1 row inserted.
iSQL> SELECT d.dno, e.e_lastname
FROM departments d LEFT OUTER JOIN employees e ON d.dno = e.dno
ORDER BY d.dno;
DNO E_LASTNAME
-------------------------------------
...
6002
...
<Query> Retrieve the department numbers and employee names for all departments. (Note that "Davenport" is returned even though she does not belong to any department.)
iSQL> SELECT d.dno, e.e_lastname
FROM departments d RIGHT OUTER JOIN employees e ON d.dno = e.dno
ORDER BY d.dno;
DNO E_LASTNAME
-------------------------------------
...
Davenport
...
<Query> Retrieve the name and numbers of all departments and the numbers of all products. Where possible, indicate the department at which the products are located.
iSQL> INSERT INTO departments VALUES('6002', 'headquarters', 'CE0002', 100);
1 row inserted.
iSQL> SELECT d.dno, d.dname, g.gno
FROM departments d FULL OUTER JOIN goods g
ON d.dep_location = g.goods_location;
DNO DNAME GNO
------------------------------------------------------------
.
6002 headquarters E111100005
.
Using In-line Views#
<Query> Retrieve the name, wage, and department of every employee who earns a higher wage than the average wage in his or her department, along with the average wage in that department.
iSQL> SELECT e.e_lastname, e.salary, e.dno, v1.salavg
FROM employees e,
(SELECT dno, AVG(salary) salavg FROM employees GROUP BY dno) v1
WHERE e.dno = v1.dno
AND e.salary > v1.salavg;
ENAME SALARY DNO SALAVG
------------------------------------------------
.
.
.
Using Lateral View#
<Query> Retrieve the department name, salary total, and salary average for each department.
iSQL> SELECT DEPT.dname, LV.*
FROM departments DEPT, LATERAL ( SELECT sum(salary), avg(salary)
FROM employees EMP WHERE DEPT.dno = EMP.dno ) LV;
DNAME SUM(SALARY) AVG(SALARY)
-----------------------------------------------------------------------
RESEARCH DEVELOPMENT DEPT 1 4300 2150
RESEARCH DEVELOPMENT DEPT 2 2680 1340
SOLUTION DEVELOPMENT DEPT 9753 2438.25
QUALITY ASSURANCE DEPT 1400 1400
CUSTOMERS SUPPORT DEPT 1800 1800
PRESALES DEPT 2500 2500
MARKETING DEPT 3100 1550
BUSINESS DEPT 4190 1396.66666666667
8 rows selected.
<Query> Retrieve the name and department name of the person with the earliest employee number in each department., If there are no employees in the department, the department name should be displayed.
insert into departments values(8000, 'empty dept', 'seoul', 20);
iSQL> SELECT LV.e_firstname, LV.e_lastname, DEPT.dname
FROM departments DEPT OUTER APPLY ( SELECT TOP 1 e_firstname, e_lastname
FROM employees EMP WHERE DEPT.dno = EMP.dno ORDER BY eno ) LV;
E_FIRSTNAME E_LASTNAME DNAME
--------------------------------------------------------------------------------
Ken Kobain RESEARCH DEVELOPMENT DEPT 1
Ryu Momoi RESEARCH DEVELOPMENT DEPT 2
Elizabeth Bae SOLUTION DEVELOPMENT DEPT
Takahiro Fubuki QUALITY ASSURANCE DEPT
Aaron Foster CUSTOMERS SUPPORT DEPT
Chan-seung Moon PRESALES DEPT
Xiong Wang MARKETING DEPT
Gottlieb Fleischer BUSINESS DEPT
empty dept
9 rows selected.
Using the PIVOT/UNPIVOT clause#
<Query> Retrieve the number of men and women who work in each department.
iSQL> SELECT * FROM
(SELECT d.dname, e.sex
FROM departments d, employees e
WHERE d.dno = e.dno)
PIVOT (COUNT(*) FOR sex in ('M', 'F'))
ORDER BY dname;
DNAME 'M' 'F'
----------------------------------------------------------------------
BUSINESS DEPT 3 1
CUSTOMERS SUPPORT DEPT 1 0
MARKETING DEPT 3 0
PRESALES DEPT 2 0
QUALITY ASSURANCE DEPT 1 0
RESEARCH DEVELOPMENT DEPT 1 1 1
RESEARCH DEVELOPMENT DEPT 2 2 0
SOLUTION DEVELOPMENT DEPT 3 1
8 rows selected.
<Query> For comparison, note that the same information can be output using only GROUP BY and ORDER BY clauses, but that is it much harder to read:
iSQL> SELECT d.dname, e.sex, count(*) FROM departments d, employees e WHERE d.dno = e.dno GROUP BY d.dname, e.sex ORDER BY d.dname, e.sex DESC;
DNAME SEX COUNT
--------------------------------------------------------------
BUSINESS DEPT M 3
BUSINESS DEPT F 1
CUSTOMERS SUPPORT DEPT M 1
MARKETING DEPT M 3
PRESALES DEPT M 2
QUALITY ASSURANCE DEPT M 1
RESEARCH DEVELOPMENT DEPT 1 M 1
RESEARCH DEVELOPMENT DEPT 1 F 1
RESEARCH DEVELOPMENT DEPT 2 M 2
SOLUTION DEVELOPMENT DEPT M 3
SOLUTION DEVELOPMENT DEPT F 1
11 rows selected.
<Query> Print the telephone number and gender of each employee.
iSQL> SELECT eno, e_lastname, e_firstname, "info", "item"
FROM employees
UNPIVOT ("info" FOR "item" IN (emp_tel as 'telno',
sex as 'sex'))
;
ENO E_LASTNAME E_FIRSTNAME info item
------------------------------------------------------------------------
1 Moon Chan-seung 01195662365 telno
1 Moon Chan-seung M sex
2 Davenport Susan 0113654540 telno
2 Davenport Susan F sex
3 Kobain Ken 0162581369 telno
3 Kobain Ken M sex
4 Foster Aaron 0182563984 telno
4 Foster Aaron M sex
...
20 Blake William 01154112366 telno
20 Blake William M sex
40 rows selected.
Using Table Function#
<Query> Create a user-defined function 'func1', and search for 10 rows.
iSQL> CREATE TYPESET type1
AS
TYPE rec1 IS RECORD (c1 INTEGER, c2 INTEGER);
TYPE arr1 IS TABLE OF rec1 INDEX BY INTEGER;
END;
/
Create success.
iSQL> CREATE FUNCTION func1(i1 INTEGER)
RETURN type1.arr1
AS
v1 type1.arr1;
BEGIN
for i in 1 .. i1 loop
v1[i].c1 := i;
v1[i].c2 := i * i;
END LOOP;
RETURN v1;
END;
/
Create success.
iSQL> SELECT * FROM TABLE( func1(10) );
C1 C2
---------------------------
1 1
2 4
3 9
4 16
5 25
6 36
7 49
8 64
9 81
10 100
10 rows selected.
-
Internal tuples are the units of memory that Altibase assigns internally for query processing. ↩