mysql-subquery, derived table, generic expression

mysql-Sub-query

A MySQL subquery is a query that is nested within another query.

MySQL subqueries can also be nested within another subquery.

A MySQL subquery is called an internal query, and a query that contains subqueries is called an external query.

The query returns employees who work in an office in the United States (USA). Select     lastName, Firstnamefrom    employeeswhere    officecode in (select             officecode            from Offices        WHERE            'USA');

mysql-derived type

Derived and subqueries are often used interchangeably. When SELECT FROM you use a standalone subquery in a clause in a statement, we call it a derived table

subqueries, derived tables must have aliases so that their names can be referenced later in the query.

Instance:

The Orders table and the OrderDetails table were awarded the top 5 products with the highest sales revenue in 2013: SELECT     productCode,     * priceeach)) Salesfrom    OrderDetails        INNER JOIN    orders USING (ordernumber) WHERE    =5;

mysql-Common expression (CTE)

A common table expression is a named temporary result set that exists only within the execution scope of a single SQL statement.

Query Customer information: with Customers_in_usa as (    SELECT         customerName, state    from        customers    WHERE         'USA') SELECT     customerName from    customers_in_usa WHERE     ' CA ' ORDER by CustomerName;

mysql-subquery, derived table, generic expression