Derived table
时间: 2024-08-16 22:00:38 浏览: 26
Derived tables, also known as subqueries or common table expressions (CTEs), are a feature in SQL that allow you to temporarily create a new virtual table based on the results of an existing query. This temporary result set can then be used within the main query as if it were a regular table, enabling complex queries with multiple steps.
Here's an example using derived tables:
Let's say we have two tables, `orders` and `customers`, with a relationship where each order has a customer ID. If we want to find all customers who have placed at least one order, we could use a derived table like this:
```sql
WITH customer_orders AS (
SELECT DISTINCT customer_id FROM orders
)
SELECT c.*
FROM customers c
WHERE c.customer_id IN (SELECT customer_id FROM customer_orders);
```
In this case, the `customer_orders` derived table contains unique customer IDs from the `orders` table. The main query then selects all columns from the `customers` table where the `customer_id` is present in the derived table.