Subqueries

Subqueries are queries embedded within other queries. They can be used in various parts of a SQL statement, such as SELECT, FROM, WHERE, and HAVING clauses.

Example 1: Subquery in SELECT Clause

-- Selecting books along with the count of authors for each book using a subquery in SELECT clause
SELECT Title, (SELECT COUNT(*) FROM Authors WHERE Authors.AuthorID = Books.AuthorID) AS AuthorCount
FROM Books;

Explanation:

• This query uses a subquery in the SELECT clause to calculate the count of authors for each book.
• The subquery is executed for each row in the Books table, providing the count of authors associated with each book.

Example 2: Subquery in WHERE Clause

-- Selecting authors who have written books published after 2000 using a subquery in WHERE clause
SELECT FirstName, LastName
FROM Authors
WHERE AuthorID IN (SELECT DISTINCT AuthorID FROM Books WHERE PublishYear > 2000);

• Explanation:
  • This query uses a subquery in the WHERE clause to filter authors who have written books published after the year 2000.
  • The subquery retrieves distinct AuthorID values from the Books table for books published after 2000.

Example 3: Subquery in FROM Clause (Derived Table)

-- Selecting the average publish year for each author using a subquery in FROM clause (derived table)
SELECT A.AuthorID, A.FirstName, A.LastName, AVG(B.PublishYear) AS AvgPublishYear
FROM Authors A
LEFT JOIN (SELECT AuthorID, PublishYear FROM Books) B ON A.AuthorID = B.AuthorID
GROUP BY A.AuthorID, A.FirstName, A.LastName;

Explanation:

• This query uses a subquery in the FROM clause to create a derived table (B) containing AuthorID and PublishYear from the Books table.
• The main query then performs a LEFT JOIN with the Authors table and calculates the average publish year for each author.
• The GROUP BY clause groups the results by AuthorID, FirstName, and LastName.