A query is a method that is used to retrieve data from databases. It can be used with conditional clauses, such as
WHERE, and clauses for sorting (such as
ORDER BY), to retrieve query results. A subquery is a query that is nested in an upper-level query. An upper-level query is called a parent query or an outer query. The result value of the subquery is passed back as an input to the parent query or the outer query. The parent query uses this value in the computation to determine the final output. SQL supports multiple levels of nested queries. This indicates that you can nest other subqueries in a subquery. In addition, subqueries can appear in various clauses of SQL statements, such as
WHERE statements. The following queries are common in SQL statements:
A simple query is an operation that retrieves data from one or more columns of data from one or more select lists or views in ApsaraDB for OceanBase. The number of columns and their data type and length depend on the elements in the select lists. Select lists are the expression lists that appear after the
SELECT keyword and before the
A hierarchical query is a query statement that has special features. You can use this query to obtain the hierarchical data that is displayed based on hierarchical relationships. Hierarchical data indicates that the data in a relational table has the hierarchical relationship.
You can use the following set operators to combine multiple queries:
MINUS. All the set operators have equal precedence. If an
SQL statement contains multiple set operators, ApsaraDB for OceanBase checks them from left to right unless an order is specified in the parentheses. This section describes the following set operators.
Returns the union of two result sets. The union does not include duplicates.
Returns the union of two result sets. The union can include duplicates.
Returns the intersection of two result sets.
Returns the difference of two result sets.
A join is a query that combines the rows from two or more tables, views, or materialized views. ApsaraDB for OceanBase performs a join when multiple tables appear in the
FROM clause of the query. The select list of the query can select columns from all of the specified tables. If the same column name is used in the two tables, you must qualify all the references to these columns in the query process by using table names. This section describes the following joins.
A join that has a join condition in which an equality operator is contained.
A join of a table to itself.
An inner join. An inner join returns the matching rows in the two joined tables.
Left (outer) join
LEFT [OUTER] JOIN
The result includes all the rows from the left table (that appears at the leftmost position of the
Right (outer) join
RIGHT [OUTER] JOIN
The result includes all the rows from the right table (that appears at the rightmost position of the
Full (outer) join
FULL [OUTER] JOIN
The result includes all the rows from all the joined tables regardless of whether these rows are matched.
You can obtain a semi join by only unnesting a subquery.
Anti joins can be implemented by only subquery unnesting.
If two tables are not joined, the data that is retrieved by querying the two tables is the Cartesian product of the two tables.
A subquery is one or more
SELECT statements that are nested in a
SELECT query statement. A subquery can return a row, multiple rows, or no result. A subquery in the
FROM clause of a
SELECT statement is called an inline view. You can nest one or more subqueries in a nested view. A subquery in the
WHERE clause of a
SELECT statement is called a nested subquery.