SELECT

List of Contents

The SELECT statement is used to find, filter and modify data from log table, volatile table and lookup table.

Syntax:

SELECT target_list 
FROM table_name 
WHERE condition 
GROUP BY expression HAVING expression
ORDER BY expression [DESC]
LIMIT N[,N]
DURATION time_expression;

TARGET LIST

This is a list of column or subquery target of SELECT statement.

The subquery that is used in the target list is considered an error if more than two values are used in WHERE clause or more than two result columns exist.

Syntax:

SELECT column_name1, column_name2, ...
SELECT column_name1, (SELECT AVG(column_name10) FROM table_name), column_name2

CASE Statement

In searched_case_expression, it returns the first satisfied conditional clause. That is, it returns the return value 100 and the second clause is not executed.
It is a conditional statement to support IF...THEN...ELSE block which can be found in other programming languages.

It is executed to return "return_expr" if a value of simple_case_expression is matches with a value of comparision_expr which is followed by WHEN. In WHEN..THEN clause,

you can write a condition repeatedly as much as required.
In t he case of "searched_case_expression", it doesn't specify an expression after CASE, and write a conditional clause including the comparison operator in WHERE clause. If each result from comparison operations turns out to be true, it returns the value of THEN clause. ELSE clause returns else_value if it doesn't satisfied the values in WHEN clause or the results of the expression is NULL.

Syntax:

CASE {simple_case_expression|searched_case_expression} [else_clause] END

simple_case_expression:
expr WHEN comparision_expression THEN return_expression [WHEN comparision_expression THEN return_expression ...]

searched_case_expression:
WHEN condtion_expression THEN return_expression [WHEN condtion_expr THEN return_expression ...]

else_clause:
ELSE else_value_expr

Example 1:

mach> SELECT * FROM select_table; 
C1          C2
---------------------------
2           2
1           1
[2] row(s) selected.

mach> SELECT CASE c1 WHEN 1 THEN 100  END FROM select_table;
case c1 when 1 then 100  end
-------------------------------
NULL
100
[2] row(s) selected.

In the example of "simple_case_expression", it returns NULL if there is no value on the condition that the value of i1 column is 2.

Example 2:

mach> SELECT CASE WHEN C1 > 0 THEN 100 WHEN C1 > 1 THEN 200 END FROM select_table;
CASE WHEN C1 > 0 THEN 100 WHEN C1 > 1 T
------------------------------------------
100
100
[2] row(s) selected.

FROM Clause

It specifies a name of a table or inline view. To join tables, list the comma separated tables or inline view.

Syntax:

FROM table_name

INLINE VIEW

It searches for the data within the parentheses below.

Syntax:

FROM (SELECT statement)

JOIN (INNER JOIN)

INNER JOIN is available when there are more than 3 tables and it specifies conditions of both JOIN and WHERE clauses.

Syntax:

FROM table_name_1, table_name_2

Example:

mach> CREATE TABLE join_table1 (id1 int, id2 int);
Created successfully.

mach> INSERT INTO join_table1 values (1, 2);
1 row(s) inserted.

mach> INSERT INTO join_table1 values (3, 4);
1 row(s) inserted.

mach> INSERT INTO join_table1 values (5, 3);
1 row(s) inserted.

mach> CREATE TABLE join_table2 (id1 int, id2 int);
Created successfully.

mach> INSERT INTO join_table2 values (1, 2);
1 row(s) inserted.

mach> INSERT INTO join_table2 values (3, 4);
1 row(s) inserted.

mach> INSERT INTO join_table2 values (5, 3);
1 row(s) inserted.

mach> SELECT join_table1.id1, join_table2.id1 FROM join_table1, join_table2 WHERE join_table1.id1 = 
join_table2.id1 AND join_table1.id1 > 1 AND join_table1.id2 = 3;

id1         id1
---------------------------
5           5
[1] row(s) selected.

INNER JOIN and OUTER JOIN

ANSI-style such as INNER JOIN, LEFT OUTER JOIN, and RIGHT OUTER JOIN are supported, but FULL OUTER JOIN is not.

FROM TABLE_1 [INNER|LEFT OUTER|RIGHT OUTER] JOIN TABLE_2 ON expression

In the ON clause of ANSI-style JOIN, a conditional clause is used. In outer join query, if the inner table has conditions in WHERE clause, the query will be converted into the inner join.
Inner table will be filled with NULL if it doesn't satisfy ON condition.

SELECT t1.i1 t2.i1 FROM t1 LEFT OUTER JOIN t2 ON (t1.i1 = t2.i1) WHERE t2.i2 = 1;

The query above is converted to inner join due to the following t2.i2 = 1 of WHERE clause.

WHERE Clause

SUBQUERY

A subquery can be used in the conditional clause. It doesn't support if a subquery returns more than two records or it has result columns of a subquery are more than two except IN clause.
Use subquery by putting parentheses to the right side of the conditional operator.
The Machbase server doesn't support correlated subquery. Therefore, outer query cannot refer to a column in the subquery.

Syntax:

WHERE column_name = (SELECT MAX(column_name) FROM table_name)

Example:

mach> SELECT * FROM join_table1 WHERE id1=(SELECT MAX(id1) FROM join_table2);
ID1         ID2
---------------------------
5           3
[1] row(s) selected.

IN Statement

It returns TRUE when it satisfies the list of value in the parenthesis. It is same with the statement that is connected with OR.

Syntax:

column_name IN (value1, value2,...)

Example:

mach> CREATE TABLE customers_table(CustomerID INTEGER, CustomerName VARCHAR(20), city VARCHAR(20));
Created successfully.
mach> INSERT INTO customers_table VALUES(1, 'Thomas', 'London');
1 row(s) inserted.
mach> INSERT INTO customers_table VALUES(2, 'Victoria', 'Paris');
1 row(s) inserted.
mach> INSERT INTO customers_table VALUES(3, 'Simon', 'Seoul');
1 row(s) inserted.

mach> SELECT * FROM customers_table WHERE city IN ('Paris', 'London');
CUSTOMERID  CUSTOMERNAME          CITY
.-----------------------------------------------------------
2           Victoria              Paris
1           Thomas                London
[2] row(s) selected.

IN and Subquery

You can also use a subquery to the right side of IN statement in a WHERE clause.However, if it specifies more than two columns on the left side of the IN, it is treated as an error.
Examine the result set returned from the subquery on the right whether there is a value in the left column.

Syntax:

WHERE i1 IN (SELECT c1 FROM ...)

The Machbase server does not support a correlated subquery. Therefore, outer query cannot refer to a column in the subquery.

BETWEEN Statement

If the value of the column is in the range of value1 and value2, BETWEEN statement returns TRUE.

Syntax:

column_name BETWEEN value1 AND value2

Example:

mach> SELECT * FROM customers_table WHERE customerid BETWEEN 2 AND 3;
CUSTOMERID  CUSTOMERNAME          CITY
------------------------------------------------------------
3           Simon                 Seoul
2           Victoria              Paris

RANGE Statement

This specifies the time duration for the specified column. If the RANGE operator does not set the specific point in time (as specified point for BEFORE keyword), it specify the range from the current point in time to the target condition of the operation. With this, you can easily search the results within the time range.

Syntax:

column_name RANGE duration_spec;
    duration_spec : number {YEAR | WEEK | HOUR | MINUTE | SECOND}

Example:

mach> SELECT * FROM customers_table WHERE customerid  < 2 and _ARRIVAL_TIME  RANGE 1 HOUR;
CUSTOMERID  CUSTOMERNAME          CITY
------------------------------------------------------------
1           Thomas                London
[1] row(s) selected.

SEARCH Statement

The syntax of SEARCH statement is the same with conventional database.
This allows additional search operation by adding "SEARCH", which is a keyword search.

Syntax:

SELECT column_name FROM table_name WHERE column_name SEARCH pattern;

Example:

mach> CREATE TABLE search_table (id1 INTEGER, id2 VARCHAR(20), id3 VARCHAR(20));
Created successfully.

mach> CREATE KEYWORD INDEX idx1 ON search_table (id2);
Created successfully.

mach> CREATE KEYWORD INDEX idx2 ON search_table (id3);
Created successfully.

mach> INSERT INTO search_table VALUES(1, 'time time2', 'series series2');
1 row(s) inserted.

mach> INSERT INTO search_table VALUES(1, 'ininmach', 'series series2');
1 row(s) inserted.

mach> INSERT INTO search_table VALUES(1, 'time time2', 'DBMS');
1 row(s) inserted.

mach> SELECT * FROM search_table;
ID1         ID2                   ID3
------------------------------------------------------------
1           time time2            DBMS
1           ininiflux             series series2
1           time time2            series series2
[3] row(s) selected.

mach> SELECT * FROM search_table WHERE id2 SEARCH 'time';
ID1         ID2                   ID3
------------------------------------------------------------
1           time time2            DBMS
1           time time2            series series2
[2] row(s) selected.

mach> SELECT * FROM search_table WHERE id3 SEARCH 'series' ;
ID1         ID2                   ID3
------------------------------------------------------------

ID1         ID2                   ID3
------------------------------------------------------------
1           ininiflux             series series2
1           time time2            series series2
[2] row(s) selected.

mach> SELECT * FROM search_table WHERE id2 SEARCH 'time' and id3 SEARCH 'series' ;
ID1         ID2                   ID3
------------------------------------------------------------
1           time time2            series series2
[1] row(s) selected.

NOT SEARCH Statement

NOT SEARCH statement returns true against data records that are not searched from the SEARCH statement. NOT ESEARCH is not supported.

create table t1 (id integer, i2 varchar(10));
create keyword index t1_i2 on t1(i2);
insert into t1 values (1, 'aaaa');
insert into t1 values (2, 'bbbb');

select id from t1 where i2 not search 'aaaa';

id
--------------------------------------------
2
[1] row(s) selected.

ESEARCH Statement

ESEARCH allows search for extended ASCII characters. The % character performs a search for a pattern which can be very useful when looking for an error string or code.
If % comes before a LIKE operation, you have to check every record, but it has benefits that ESEARCH can find strings very quickly.

Syntax:

SELECT column_name FROM table_name WHERE column_name ESEARCH pattern;

Example:

mach> SELECT * FROM search_table;
ID1         ID2                   ID3
------------------------------------------------------------
1           time time2            DBMS
1           ininiflux             series series2
1           time time2            series series2
[3] row(s) selected.

mach> SELECT * FROM search_table WHERE id2 ESEARCH '%time';
ID1         ID2                   ID3
------------------------------------------------------------
1           time time2            DBMS
1           time time2            series series2
[2] row(s) selected.

mach> SELECT * FROM search_table WHERE id2 ESEARCH 'time%';
ID1         ID2                   ID3
------------------------------------------------------------
1           time time2            DBMS
1           time time2            series series2
[2] row(s) selected.

mach> SELECT * FROM search_table WHERE id3 ESEARCH '%DB';
ID1         ID2                   ID3
------------------------------------------------------------
[0] row(s) selected.

mach> SELECT * FROM search_table WHERE id3 ESEARCH 'DB%';
ID1         ID2                   ID3
------------------------------------------------------------
1           time time2            DBMS
[1] row(s) selected.

mach> SELECT * FROM search_table WHERE id3 ESEARCH '%se%';
ID1         ID2                   ID3
------------------------------------------------------------
1           ininiflux             series series2
1           time time2            series series2
[2] row(s) selected.

REGEXP Statement

This is used to perform a search for data using a regular expression. Generally, data will be filtered by using regular expressions against a pattern of a column.
It should be noted that when using REGEXP statements, you have to decrease the cost of the whole search by inserting index conditions against other columns. If you want to check a specific pattern, you have to take advantage of the index through SEARCH or ESEARCH. When the overall number of data becomes smaller, use REGEXP statements again. It greatly helps to improve the efficiency of overall system.

Syntax:

SELECT * FROM table_name WHERE column_name REGEXP pattern [AND column_name REGEXP pattern,...];

Example:

mach> CREATE TABLE regexp_table (id1 INTEGER, id2 VARCHAR(20), id3 VARCHAR(20));
Created successfully.

mach> INSERT INTO regexp_table VALUES(1, 'time1', 'series1 series11');
1 row(s) inserted.

mach> INSERT INTO regexp_table VALUES(2, 'time2', 'series2 series22');
1 row(s) inserted.

mach> INSERT INTO regexp_table VALUES(3, 'time3', 'series3 series33');
1 row(s) inserted.

mach> CREATE TABLE dual_table (id INTEGER);
Created successfully.

mach> INSERT INTO dual VALUES(1);
1 row(s) inserted.

mach> SELECT * FROM regexp_table WHERE id2 REGEXP 'time' ;
ID1         ID2                   ID3
------------------------------------------------------------
3           time3                 series3 series33
2           time2                 series2 series22
1           time1                 series1 series11
[3] row(s) selected.

mach> SELECT * FROM regexp_table WHERE id2 REGEXP 'time[12]' ;
ID1         ID2                   ID3
------------------------------------------------------------
2           time2                 series2 series22
1           time1                 series1 series11
[2] row(s) selected.

mach> SELECT * FROM regexp_table WHERE id2 REGEXP 'time[13]' ;
ID1         ID2                   ID3
------------------------------------------------------------
3           time3                 series3 series33
1           time1                 series1 series11
[2] row(s) selected.

mach> SELECT * FROM regexp_table WHERE id2 REGEXP 'time[13]' AND id3 REGEXP 'series[12]';
ID1         ID2                   ID3
------------------------------------------------------------
1           time1                 series1 series11
[1] row(s) selected.

mach> SELECT * FROM regexp_table WHERE id2 NOT REGEXP 'time[12]';
ID1         ID2                   ID3
------------------------------------------------------------
3           time3                 series3 series33
[1] row(s) selected.

mach> SELECT 'abcde' REGEXP 'a[bcd]{1,10}e' from dual_table;
'abcde' REGEXP 'a[bcd]{1,10}e'
---------------------------------
[0] row(s) selected.

mach> INSERT INTO dual_table VALUES(1);
1 row(s) inserted.

mach> SELECT 'abcde' REGEXP 'a[bcd]{1,10}e' from dual_table;
'abcde' REGEXP 'a[bcd]{1,10}e'
---------------------------------
1
[1] row(s) selected.
mach>

GROUP BY... HAVING Clause

GROUP BY is used to group search results based on a specific column in SELECT statements. It is also used for sorting data by group or using aggregation functions to calculate the aggregates by groups. A group refers to the records that have the same values for the columns specified in the GROUP BY clause.
Combining the HAVING clause after the GROUP BY clause allows you to set the condition for group selection.
That is, it searches only the groups that satisfy the condition specified in the HAVING clause of every group that is consisted with GROUP BY clauses.

Syntax:

GROUP BY { column_name | expression },...[ HAVING <search_condition> ]

Example:

SELECT id1, AVG(id2) FROM log_data GROUP BY id1 ORDER BY id1;

SELECT id1, AVG(id2) FROM log_data GROUP BY id1 HAVING AVG(id2) > 100 ORDER BY id1;

It calculates the average value of id2 based on the id1 column.

ORDER BY Clause

It sorts the results of a query in ascending or descending order. If not specified, data will be sorted in ascending order by default.
If ORDER BY is not specified, the order of records may vary depending on the query.

Syntax:

ORDER BY {column_name | expression} [ASC | DESC]

Example:

SELECT Id1, AVG(id2) FROM log_data GROUP BY id1 ORDER BY id1;

LIMIT Clause

It is used to limit the number of output records. You can specify an integer to display from a specific row to the last from the result sets.

Syntax:

LIMIT [offset,] row_count

Example:

SELECT id1, AVG(id2) FROM log_data GROUP BY id1 ORDER BY id1 LIMIT 10;

SELECT id1, AVG(id2) FROM log_data GROUP BY id1 ORDER BY id1 LIMIT 100, 10;

DURATION Clause

Syntax:

DURATION number TimeSpec [BEFORE|AFTER number TimeSpec]
TimeSpec : YEAR | MONTH | WEEK |  DAY | HOUR | MINUTE | SECOND

Example:

mach> CREATE TABLE DURATION_table (c1 INTEGER, c2 INTEGER);
Created successfully.

mach> INSERT INTO DURATION_table VALUES(1,2);
1 row(s) inserted.

mach> INSERT INTO DURATION_table VALUES(3,4);
1 row(s) inserted.

mach> INSERT INTO DURATION_table VALUES(5,6);
1 row(s) inserted.

mach> INSERT INTO DURATION_table VALUES(7,8);
1 row(s) inserted.

mach> INSERT INTO DURATION_table VALUES(9,10);
1 row(s) inserted.

mach> SELECT * FROM DURATION_table DURATION 2 second;
C1          C2
---------------------------
[0] row(s) selected.

mach> SELECT * from DURATION_table DURATION 1 hour;
C1          C2
---------------------------
9           10
7           8
5           6
3           4
1           2
[5] row(s) selected.

mach> SELECT * from DURATION_table DURATION 1 day;
C1          C2
---------------------------
9           10
7           8
5           6
3           4
1           2
[5] row(s) selected.

mach> SELECT * from DURATION_table DURATION 10 minute BEFORE 1 MINUTE;
C1          C2
---------------------------
[0] row(s) selected.

mach> SELECT * from DURATION_table DURATION 10 minute BEFORE 10 MINUTE;
C1          C2
---------------------------
9           10
7           8
5           6
3           4
1           2
[5] row(s) selected.

SET Operator

The SET operator combines the rows returned by several SELECT statements into a single result.
Machbase only supports the UNION ALL set operator. It is mandatory to satisfy two conditions when using this operator. An error message is displayed if one of them is not satisfied.
The two conditions to use UNION ALL,

  • The syntax of the SELECT statements must be same or compatible.
  • The number of results returned by each of the SELECT statement must be the same.

Verify data type conversion and compatibility based on the criteria as follows.

  • An integer data type can be signed or unsigned. A signed integer is not compatible with an unsigned integer.
  • An integer data type is compatible with a float data type, and the query result will be converted into and returned a float data type.
  • Character data types are compatible each other even though they vary in length.
  • IPv6 is not compatible with IPv4.
  • The column name of the SELECT statements on the left side is always used.

Syntax:

SELECT {cloumn_name1, column_name2,...} FROM table_name UNION ALL SELECT {cloumn_name3, column_name4,..} FROM table_name;

Example:

mach> CREATE TABLE union_table1 (id1 INTEGER, id2 INTEGER);
Created successfully.

mach> INSERT INTO union_table1 VALUES (101, 102);
1 row(s) inserted.

mach> CREATE TABLE union_table2 (id3 INTEGER, id4 INTEGER);
Created successfully.

mach> INSERT INTO union_table2 VALUES (103, 104);
1 row(s) inserted.

mach> SELECT id1, id2 FROM union_table1 UNION ALL SELECT id3, id4 FROM union_table2;
id1         id2
---------------------------
101         102
103         104
[2] row(s) selected.
Elapsed time: 0.001
mach>

Hint

PARALLEL

It specifies a parallel factor for parallel query execution.

Syntax:

PARALLEL(table_name, parallel_factor)

Example:

mach> EXPLAIN SELECT /*+ PARALLEL(test, 8) */ sensor, frequency, avg(value)
FROM test
WHERE ts >= TO_DATE('2007-07-01', 'YYYY-MM-DD') and ts <= TO_DATE('2007-07-31', 'YYYY-MM-DD')
GROUP BY sensor,frequency;

PLAN                                                                             
------------------------------------------------------------------------------------
 QPX_NODE_TYPE_PROJ                                                              
  QPX_NODE_TYPE_GRAG                                                             
   PARALLEL INDEX SCAN                                                           
    *BITMAP RANGE (t:92, c:2, i:94)                                              
[4] row(s) selected.

NOPARALLEL

It prohibits a parallel execution.

Syntax:

NOPARALLEL(table_name)

Example:

mach> EXPLAIN SELECT /*+ NOPARALLEL(test) */ sensor, frequency, avg(value)
FROM test
WHERE ts >= TO_DATE('2007-07-01', 'YYYY-MM-DD') and ts <= TO_DATE('2007-07-31', 'YYYY-MM-DD')
GROUP BY sensor,frequency;

PLAN                                                                             
------------------------------------------------------------------------------------
 QPX_NODE_TYPE_PROJ                                                              
  QPX_NODE_TYPE_GRAG                                                             
   INDEX SCAN                                                                    
    *BITMAP RANGE (t:92, c:2, i:94)                                              
[4] row(s) selected.
Elapsed time: 0-000

NO_INDEX

It sets no use of the specified index.

Syntax:

NO_INDEX(table_name, index_name)

Example:

mach> CREATE TABLE test (col1 INTEGER, col2 VARCHAR(10));
Created successfully.

mach> CREATE INDEX idx_col1 ON test (col1);
Created successfully.

mach> CREATE INDEX idx_col2 ON test (col2);
Created successfully.

mach> EXPLAIN SELECT * FROM test WHERE col1 <= 100 AND col2 = 'hint test';
PLAN                                                                             
------------------------------------------------------------------------------------
 QPX_NODE_TYPE_PROJ                                                              
  INDEX SCAN                                                                     
   *BITMAP RANGE (t:52, c:1, i:53)                                               
   *BITMAP RANGE (t:52, c:2, i:54) with BLOOMFILTER                              
[4] row(s) selected.

mach> EXPLAIN SELECT /*+ NO_INDEX(test, idx_col1) */ * FROM test WHERE col1 <= 100 AND col2 = 'hint test';
PLAN                                                                             
------------------------------------------------------------------------------------
 QPX_NODE_TYPE_PROJ                                                              
  INDEX SCAN                                                                     
   *BITMAP RANGE (t:52, c:2, i:54) with BLOOMFILTER                              
[3] row(s) selected.

mach> EXPLAIN SELECT /*+ NO_INDEX(test, idx_col1) NO_INDEX(test, idx_col2) */ * FROM test WHERE col1 <= 100 AND col2 = 'hint test';
PLAN                                                                             
------------------------------------------------------------------------------------
 QPX_NODE_TYPE_PROJ                                                              
  FULL SCAN                                                                      
[2] row(s) selected.

FULL

It performs a full scan on the specified table.

Syntax:

FULL(table_name)

Example:

mach> CREATE TABLE test (col1 INTEGER, col2 VARCHAR(10));
Created successfully.

mach> CREATE INDEX idx_col1 ON test (col1);
Created successfully.

mach> CREATE INDEX idx_col2 ON test (col2);
Created successfully.

mach> EXPLAIN SELECT * FROM test WHERE col1 <= 100 AND col2 = 'hint test';
PLAN                                                                             
------------------------------------------------------------------------------------
 QPX_NODE_TYPE_PROJ                                                              
  INDEX SCAN                                                                     
   *BITMAP RANGE (t:44, c:1, i:45)                                               
   *BITMAP RANGE (t:44, c:2, i:46) with BLOOMFILTER                              
[4] row(s) selected.

mach> EXPLAIN SELECT /*+ FULL(test) */ * FROM test WHERE col1 <= 100 AND col2 = 'hint test';
PLAN                                                                             
------------------------------------------------------------------------------------
 QPX_NODE_TYPE_PROJ                                                              
  FULL SCAN                                                                      
[2] row(s) selected.
Elapsed time: 0.000

results matching ""

    No results matching ""