Oracle single-row subquery and multi-row subquery instances

Oracle single-row subquery and multi-row subquery instances

1. the query is divided into primary queries and subqueries;

2. The records returned are divided into single-row subqueries and multi-row subqueries. single-row subqueries use single-row comparison operators = connections. multi-row subqueries use multi-row comparison operators in connections;

3. The content of the subquery can be placed after FROM, WHERE, or HAVING;

4. The complete SELECT statement can have group by and HAVING clauses, or use GROUP functions. It can also be queried from multiple tables;

5. The content of the subquery must be defined in parentheses;

6. Example 1: subquery (clause) following FROM:

SELECT first_name, last_nameFROM (SELECT first_name, last_nameFROM s_empWHERE 1 = 1AND first_name = 'Guo fan ') WHERE 1 = 1AND last_name = 'zeng ';

Simplified as follows:

SELECT first_name, last_nameFROM (subquery) WHERE 1 = 1AND last_name = 'zeng ';

This is like an Attribute Clause. Here (subquery) is replaced with another select statement. Note that the subquery statement does not contain a semicolon indicating the end; the range of the primary query must be smaller than that of the subquery. Otherwise, an error is returned because no data is found;

7. Example 2: subquery (clause) following WHERE

SELECT first_name, last_name FROM s_empWHERE dept_id = (SELECT dept_idFROM s_empWHERE last_name = 'zeng ');

Here, we query the names of all people whose names are the same (only one name is Zeng) as the ID of the department where 'zeng 'is located;

Here, someone asked me what to do if there are several people with the same name as Zeng. I want to find out the names of all the people with the same name as Zeng's department. Here is multi-row subqueries; replace the equals sign with in;

Oracle Database SQL development-subquery type

There are two basic types of subqueries:

Single-row subquery: returns only one row instead of External SQL statements.

Multi-row subquery: returns one or more rows to an external SQL statement.

There are three other seed types, which can return one or more rows:

Multi-column subquery: multiple columns are returned to External SQL statements.

Join subquery: reference one or more columns in an External SQL statement. Use the same columns to associate with External SQL statements.

Nested subquery: It is located in another self-query. Up to 255 layers can be nested in subqueries.

The data types that subqueries can return are divided into the following four types:

Single Row and Single Column: the content of a specific column is returned, which can be understood as a single-value data.

Single Row and multiple columns: return the content of multiple columns in a row of data.

Multiple rows and one column: returns the content of the same column in multiple rows, which is equivalent to providing an operation range.

Multiple rows and multiple columns: the query returns a temporary table.

Subqueries can appear almost anywhere in a query statement. However, there are many subqueries in the from, where, and having clauses, and the "()" statement must be used in subqueries.

Results returned by the subquery:

Where clause: At this time, the results returned by the subquery are generally single-row, single-row, multi-row, single-column (not a table ).

Having clause: At this time, the subquery returns a single row of Single Column data (that is, one data), and in order to use the statistical function operation.

From clause: At this time, the results returned by the subquery are generally multiple rows and multiple columns, and can be operated in the form of a data table (temporary table.