All Products
Search
Document Center

Overview of queries and subqueries

Last Updated: Jun 18, 2021

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 SELECT, FROM, and WHERE statements. The following queries are common in SQL statements:

  • Simple queries

  • Hierarchical queries

  • Sets

  • Joins

  • Subqueries

Simple queries

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 FROM clause.

Hierarchical queries

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.

Sets

You can use the following set operators to combine multiple queries: UNION, UNION ALL, INTERSECT, and 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.

Operator

Description

UNION

Returns the union of two result sets. The union does not include duplicates.

UNION ALL

Returns the union of two result sets. The union can include duplicates.

INTERSECT

Returns the intersection of two result sets.

MINUS

Returns the difference of two result sets.

Joins

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.

Join type

Representation

Description

Equi join

Equijoins

A join that has a join condition in which an equality operator is contained.

Self join

SELF-JOIN

A join of a table to itself.

Inner join

INNER JOIN

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 JOIN clause) and excludes the rows that do not match from the right table.

Right (outer) join

RIGHT [OUTER] JOIN

The result includes all the rows from the right table (that appears at the rightmost position of the JOIN clause) and excludes the rows that do not match from the left table.

Full (outer) join

FULL [OUTER] JOIN

The result includes all the rows from all the joined tables regardless of whether these rows are matched.

Semi join

SEMI-JOIN

You can obtain a semi join by only unnesting a subquery.

Anti join

ANTI-JOIN

Anti joins can be implemented by only subquery unnesting.

Cartesian product

Cartesian Products

If two tables are not joined, the data that is retrieved by querying the two tables is the Cartesian product of the two tables.

Subqueries

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.