Overview

A query (SQL) is a method for retrieving data from a database. It can be combined with clauses such as WHERE (to limit conditions) and ORDER BY (to sort results) to obtain query results. This section describes the basic operations and components of queries.

Basic operations of queries

At its core, a query is a relational operation. In a database, relationships are stored in tables, where each row represents a tuple (an element of the set), and each column represents an attribute (a property of the set).

The basic operations of relational algebra include:

• Selection: This operation selects tuples that meet specified conditions from a relation. In a database, this corresponds to selecting specific rows from a table.

  obclient> SELECT * FROM t WHERE c1 <10;
  

• Projection: This operation selects attributes that meet specified conditions from a relation. In a database, this corresponds to selecting specific columns from a table.

  obclient> SELECT c1,c2 FROM t;
  

• Join: This operation selects tuples that meet specified conditions from the Cartesian product of two relations. In a database, this corresponds to selecting tuples from the Cartesian product of two tables that meet specified conditions.

  obclient> SELECT t1.c2,t2.c4 FROM t1 JOIN t2 WHERE t1.c1 = t2.c3;
  

Components of queries

The basic syntax of a query is as follows, where att_name represents the attribute name and rel_name represents the relation name.

SELECT att_name FROM rel_name;

A relation consisting of a single tuple is called a single-tuple relation, and a tuple consisting of a single element is called a single-element tuple. In other words, a single element can also be considered a relation, so the simplest query can be a single element. The following query is a valid example.

obclient> SELECT 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set

In the query result, the 1 above is the attribute name of the element, and the 1 below is the value of the element.

OceanBase Database supports the FROM DUAL syntax, where DUAL is a virtual table used to construct the syntax rules of SELECT.

obclient> SELECT 1 FROM DUAL;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set

FROM clause

The FROM clause specifies the name of the relation to query. Since the result of a query is also a relation, you can reference the result of a query as the relation specified in the FROM clause.

obclient> SELECT * FROM (SELECT 1 FROM DUAL);
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set

WHERE clause

The WHERE clause specifies the conditions for the tuples to be queried. The WHERE statement compares each attribute of the tuples to determine whether it meets the conditions and returns the tuples for which the comparison result is TRUE. For example, in the following query, if the WHERE condition is FALSE or NULL, the result will always be an empty set.

obclient> SELECT * FROM (SELECT 1 FROM DUAL) WHERE TRUE;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set

obclient> SELECT * FROM (SELECT 1 FROM DUAL) WHERE FALSE;
Empty set

obclient> SELECT * FROM (SELECT 1 FROM DUAL) WHERE NULL;
Empty set

ORDER BY clause

The ORDER BY clause is used to sort the query results. You can specify several columns c1,c2,... for sorting, and the query results will be sorted in ascending order based on these columns.

obclient> CREATE TABLE t (c1 INTEGER,c2 INTEGER);
obclient> SELECT * FROM t ORDER BY c1,c2;

OceanBase Database supports using the numbers 1,2,... to represent the first, second, and subsequent columns of the relation t, respectively. Therefore, you can rewrite the above query as:

obclient> SELECT * FROM t ORDER BY 1,2;

GROUP BY clause

The GROUP BY clause is used to group the query results. You can specify several columns c1,c2,... for grouping, and the query results will be grouped in ascending order based on these columns.

obclient> CREATE TABLE t (c1 INTEGER,c2 INTEGER);
obclient> SELECT * FROM t GROUP BY c1,c2;

OceanBase Database supports using the numbers 1,2,... to represent the first, second, and subsequent columns of the relation t, respectively. Therefore, you can rewrite the above query as:

obclient> SELECT * FROM t GROUP BY 1,2;

LIMIT clause

The LIMIT clause is used to limit the number of rows in the query results. The LIMIT clause has two syntaxes:

• LIMIT N: Returns only the first N rows of the query results.

• LIMIT N,M: Returns M rows starting from the N+1th row of the query results.

HAVING clause

The HAVING clause is similar to the WHERE clause, but the HAVING clause can be used with aggregate functions.