Mastering Database Management

0 of 78 lessons complete (0%)

Basic SQL Commands with CRUD Operations

Subqueries

You don’t have access to this lesson

Please register or sign in to access the course content.

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.