MySQL Derived Table

Structured Query Language (SQL) is a powerful tool for managing and querying relational databases, and MySQL is one of the most widely used database management systems. In MySQL, derived tables offer a flexible and efficient way to manipulate and analyze data within a query. In this article, we will get inside the concept of MySQL-derived tables, exploring their syntax, their use cases, and restrictions for Derived tables in My SQL.

In this article, we will learn how to use MySQL-derived tables to analyze dynamic data. We’ll show you how to use them with a real-world example. Discover important limitations that govern derived tables within MySQL, and learn best practices for optimizing your query strategies.

MySQL Derived Tables

Derived tables, also known as inline views or subqueries in the FROM clause, are temporary resulting tables created within the scope of a SQL query. These tables are not stored physically in the database but exist only for the duration of the query execution. Derived tables are particularly useful when we need to perform complex operations on a subset of data before combining it into the main query.

Syntax:

The basic syntax for using a derived table involves placing a subquery in the FROM clause of the main query and we assign it an alias. The outer query can then reference the derived table by its alias.

SELECT …… FROM (SUBQUERY) [AS] table_name WHERE Conditions;

Here the inner query inside the parenthesis creates a derived table on which the outer query can reference or apply the additional operations.

Example of MySQL Derived Tables

Example 1: Average Age of Customers in the USA

Suppose we have a Customer table whose data is shown below

Now, execute the below statement to understand how we can use a subquery to form a derived table.

mysql> select AVG(age) from(select first_name,age from Customers where country="USA") 

Here the subquery in bold creates a derived table with the Conditions in it. The derived table is shown below

Now the Outer Query will execute in this new table (i.e., Derived Table) and we get the Average of the ages in this derived table. The Output of the whole query will be

Output:

Explanation: The average age of the customers from the USA is calculated using a sub query to create a derived table. First names and ages are selected based on the “USA” country condition. This approach makes the query easier to read and allows for more targeted data analysis.

Example 2: Total Amount of Eligible Orders

For a Better Understanding of Derived Tables Let’s have another example of Table Order which is shown below

Order Table

Now to create a table derived from Order table we will execute a query with a subquery.

SELECT SUM(amount)
FROM 
(SELECT order_id,item,amount,customer_id FROM Orders
where amount between 270 and 700) 
where amount>=250;

Here the derived Table table will contain all the rows with amount between 270 and 700.

and the sum of all the amount in derived table with amount greater than 250 i.e., OUTPUT of our SQL Query will be

Output:

Output

Explanation: This MySQL query shows the use of a table derived from the “Orders” table. It takes the total of the sum of the amounts from a group of orders with the amount between 270 and 700. The outer query filters this table, taking into account only the rows with the amount greater than or less than 250. This approach allows for a more precise analysis of particular order amounts, improving query performance and readability.

Restrictions for Derived Table in MySQL

• In MySQL, a correlated subquery is a subquery that refers to columns from the outer query. Correlated subqueries are typically used to filter the results based on values from the outer query. However, when we use a correlated subquery within a derived table in the FROM clause, MySQL does not allow it.

• Within a derived table definition in the FROM clause, we cannot reference tables from the same SELECT statement directly. Each derived table operates independently within the scope of the specific SELECT statement in which it is defined. MySQL does not allow a derived table to use references to other tables of the same SELECT statement.

• We cannot use outer references in the derived table. An outer reference is a reference to a column from a table outside the subquery in which the reference occurs. In the context of derived tables, you cannot use references to columns from tables outside the derived table definition.

Conclusion

MySQL derived tables provide a valuable tool for enhancing the flexibility and efficiency of SQL queries. Through the use of subqueries in the FROM clause, developers can create temporary result sets that serve as derived tables , that can be used for further analysis within the main query but with some restrictions.