Derived tables are temporary tables created within a SQL query. They exist only during the execution of the query and are not stored permanently in the database. Derived tables are useful for simplifying complex queries by breaking them into smaller, manageable parts.<\/p>\n\n\n\n
<\/p> <\/div> <\/div>\n\n\n\n
A derived table is created using a subquery in the Explanation: The inner query calculates total sales per employee. The outer query filters employees with sales greater than 10,000.<\/p>\n\n\n\n Explanation: The derived table calculates total sales per employee, which is then joined with the employees table to get employee names.<\/p>\n\n\n\nFROM<\/code> clause. Example:<\/p>\n\n\n\nSELECT dt.column1, dt.column2
FROM (
SELECT column1, column2
FROM table_name
WHERE condition
) AS dt;<\/pre>\n\n\n\n\n
AS dt<\/code> gives a name (alias) to the derived table for reference in the outer query.<\/li>\n<\/ul>\n\n\n\nKey Points<\/h2>\n\n\n\n
\n
Example 1: Using Derived Table for Filtering<\/h2>\n\n\n\n
SELECT dt.employee_id, dt.total_sales
FROM (
SELECT employee_id, SUM(sales_amount) AS total_sales
FROM sales
GROUP BY employee_id
) AS dt
WHERE dt.total_sales > 10000;<\/pre>\n\n\n\nExample 2: Using Derived Table with Join<\/h2>\n\n\n\n
SELECT e.employee_name, dt.total_sales
FROM employees e
JOIN (
SELECT employee_id, SUM(sales_amount) AS total_sales
FROM sales
GROUP BY employee_id
) AS dt
ON e.employee_id = dt.employee_id;<\/pre>\n\n\n\nAdvantages<\/h2>\n\n\n\n
\n
Best Practices<\/h2>\n\n\n\n
\n