What kind of SQL query is this?-CodePudding

I understand what a subquery is (aka inner query / nested query).

A subquery, also known as a nested query or subselect, is a SELECT query embedded within the WHERE or HAVING clause of another SQL query.

Example -

SELECT * FROM customers WHERE cust_id IN (SELECT DISTINCT cust_id FROM orders 
                      WHERE order_value > 5000);

I am more concerned about understanding the kind (name) of below query -

SELECT ta.col_a1, ta.col_a2, temp.col_tmp_a FROM table_a ta, (
    SELECT tb.col_b1, tb.col_b2, tc.col_c1 FROM table_b tb, table_c tc 
        WHERE tb.col_a1 = tc.col_c2 ) AS temp
    WHERE temp.col_b1 = ta.col_a1

If am right, the above query can be better written with inner joins for better performance. But performance is not my concern, i just want to know the name of this kind of query. If some one knows the name, then plz answer.

CodePudding user response：

That type of query is called "Table Expression", and also known as "Derived Table", or "Inline View", depending on the lingo and database documentation. They take the place of a table/view in a query.

Just to complement the question, the types of subqueries I've identified so far are:

Scalar subquery: a query that takes the place of a scalar in a SELECT list.
Table Expression/Derived Table/Inline View: described above.
Independent [Recursive] CTE: A query definition specified before the main query itself.
Dependent [Recursive] CTE: A query definition specified before the main query itself that depends on another CTE(s).
Non-correlated subquery: A subquery that can be run independently of the rest of the query.
Correlated subquery: A subquery that depends on values from another table and needs to be executed accordingly.
Lateral Subquery: A query placed in the same location of a Table Expression, but that is correlated to the previous tables.

See How many types of SQL subqueries are there?.

CodePudding user response：

it is still just called a Subquery, just instead as it being used to get specific clause value to filter the list, it is used effectivly as a table and allows you to select columns from the nested query just as you would from a table. Hope that answers your question.

CodePudding user response：

It is called a derived table and below are the details

Derived tables are the tables which are created on the fly with the help of the Select statement. Derived table expression appears in the FROM clause of a query. In derived table server create and populate the table in the memory, we can directly use it and we also don’t require to drop the table. But scope of derived table is limited to the outer Select query who created it. Derived table can’t be used further outside the scope of outer select query.