mysql
  1. mysql-derived-table
PreviousNext

Derived Table (MySQL Misc)

A derived table, also known as a subquery or inner query, is a SELECT statement that is used within another SQL statement, such as a SELECT, INSERT, UPDATE, or DELETE. It is called a derived table because it is derived from another table or subquery. In this tutorial, we'll discuss how to use derived tables in MySQL.

Syntax

The syntax for creating a derived table in MySQL is as follows:

SELECT column1, column2, ...
FROM (subquery) AS derived_table
WHERE condition;

The subquery is enclosed in parentheses and used as a table within the outer SELECT statement. The derived table is given an alias, which is used to refer to it in the outer SELECT statement.

Example

Let's say we have a table called "employees" that contains information about employees and their salaries. We want to find the average salary of employees who have been with the company for more than 5 years. Here's how we can use a derived table to do this:

SELECT AVG(salary) as avg_salary
FROM (
  SELECT salary
  FROM employees
  WHERE DATEDIFF(NOW(), hire_date) > 5 * 365
) AS t;

In the example above, we create a derived table by selecting all the employees who have been with the company for more than 5 years using a subquery. We then find the average salary of those employees in the outer SELECT statement.

Output

When we run the example code above, the output will be a single row with the average salary of the selected employees.

Explanation

In the example above, we use a derived table to find the average salary of employees who have been with the company for more than 5 years. We achieve this by using a subquery to select the salaries of those employees, and then finding the average of those salaries in the outer SELECT statement using the AVG function.

Use

Derived tables are useful when we need to perform complex queries that cannot be achieved by using a single query. By using a subquery to create a derived table, we can perform advanced filtering, grouping, or aggregation on the result set.

Important Points

  • Derived tables are enclosed in parentheses and used as a table within the outer query.
  • A derived table can be given an alias, which is used to refer to it in the outer query.
  • Derived tables can be used in any SQL statement, such as SELECT, INSERT, UPDATE, and DELETE.

Summary

In this tutorial, we discussed how to use derived tables in MySQL. We covered the syntax, example, output, explanation, use, and important points of derived tables in MySQL. With this knowledge, you can now use derived tables to perform complex queries that cannot be achieved by using a single query.

SubqueryUUID
Published on: