PostgreSQL - 子查詢

Hello there, aspiring database enthusiasts! Today, we're diving into the fascinating world of PostgreSQL subqueries. Think of subqueries as little helpers that assist your main query in retrieving or manipulating data. They're like the trusty sidekicks in your favorite superhero movies – not always in the spotlight, but essential for getting the job done!

在 SELECT 認句中使用子查詢

Let's start with the most common use of subqueries – within a SELECT statement. Imagine you're running a bookstore, and you want to find out which books are priced above the average. Here's how you'd do it:

SELECT title, price
FROM books
WHERE price > (SELECT AVG(price) FROM books);

In this query, the part (SELECT AVG(price) FROM books) is our subquery. It calculates the average price of all books, and then our main query uses this value to filter the results.

Let's break it down:

1. The subquery calculates the average price.
2. The main query then compares each book's price to this average.
3. Only books with a price higher than the average are returned.

Here's another example. Let's say you want to find all the customers who have placed orders:

SELECT customer_name
FROM customers
WHERE customer_id IN (SELECT DISTINCT customer_id FROM orders);

In this case, our subquery (SELECT DISTINCT customer_id FROM orders) returns a list of customer IDs who have placed orders. The main query then uses this list to filter the customers table.

在 INSERT 認句中使用子查詢

Now, let's look at how subqueries can be used with INSERT statements. Imagine you're creating a 'featured books' table, and you want to populate it with all books that have sold more than 1000 copies:

INSERT INTO featured_books (book_id, title, author)
SELECT book_id, title, author
FROM books
WHERE book_id IN (SELECT book_id FROM sales WHERE copies_sold > 1000);

Here, our subquery (SELECT book_id FROM sales WHERE copies_sold > 1000) finds all the books that have sold more than 1000 copies. The main query then inserts the details of these books into the featured_books table.

在 UPDATE 認句中使用子查詢

Subqueries can also be incredibly useful in UPDATE statements. Let's say you want to give a 10% discount to all books from authors who have written more than 5 books:

UPDATE books
SET price = price * 0.9
WHERE author_id IN (
SELECT author_id
FROM books
GROUP BY author_id
HAVING COUNT(*) > 5
);

In this example, our subquery identifies authors who have written more than 5 books. The main query then updates the prices of all books by these authors.

在 DELETE 認句中使用子查詢

Finally, let's look at how subqueries can be used with DELETE statements. Imagine you want to remove all orders from customers who haven't made a purchase in the last year:

DELETE FROM orders
WHERE customer_id IN (
SELECT customer_id
FROM customers
WHERE last_purchase_date < CURRENT_DATE - INTERVAL '1 year'
);

Here, our subquery finds all customers who haven't made a purchase in the last year. The main query then deletes all orders from these customers.

常用的子查詢方法

Here's a table of common subquery methods in PostgreSQL:

方法	描述	示例
IN	檢查一個值是否在子查詢的結果集中	WHERE id IN (SELECT id FROM table)
EXISTS	檢查子查詢是否有返回任何行	WHERE EXISTS (SELECT 1 FROM table WHERE condition)
ANY	如果子查詢的任何值滿足條件，則返回 true	WHERE column > ANY (SELECT column FROM table)
ALL	如果子查詢的所有值都滿足條件，則返回 true	WHERE column > ALL (SELECT column FROM table)

Remember, practice makes perfect! Try writing your own queries, experiment with different scenarios, and don't be afraid to make mistakes. That's how we learn and grow in the wonderful world of databases!

Before we wrap up, here's a little story from my teaching experience. I once had a student who was struggling with subqueries. He kept mixing up the main query and the subquery. So I told him to think of it like a Russian nesting doll - the smallest doll (subquery) fits inside the bigger one (main query). This visual helped him grasp the concept, and soon he was writing complex queries like a pro!

I hope this tutorial has been helpful in demystifying PostgreSQL subqueries for you. Keep querying, keep learning, and most importantly, have fun with databases!

Credits: Image by storyset