{"id":37510,"date":"2024-08-19T08:11:44","date_gmt":"2024-08-18T22:11:44","guid":{"rendered":"https:\/\/database.guide\/?p=37510"},"modified":"2024-09-17T07:00:39","modified_gmt":"2024-09-16T21:00:39","slug":"using-a-sql-subquery-in-the-select-list","status":"publish","type":"post","link":"https:\/\/database.guide\/using-a-sql-subquery-in-the-select-list\/","title":{"rendered":"Using a SQL Subquery in the SELECT List"},"content":{"rendered":"\n

Let’s dive into subqueries for a moment. Specifically, how to use a SQL subquery in the SELECT<\/a><\/code> list. It’s a handy little trick that can help us fetch related data without resorting to complex joins.<\/p>\n\n\n\n\n\n\n\n

Sample Database<\/h2>\n\n\n\n

First, let’s set up some sample tables:<\/p>\n\n\n\n

-- Create the Authors table\nCREATE TABLE Authors (\n    AuthorID INT PRIMARY KEY,\n    AuthorName VARCHAR(100)\n);\n\n-- Create the Books table\nCREATE TABLE Books (\n    BookID INT PRIMARY KEY,\n    Title VARCHAR(200),\n    AuthorID INT,\n    PublicationYear INT,\n    FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID)\n);\n\n-- Create the Sales table\nCREATE TABLE Sales (\n    SaleID INT PRIMARY KEY,\n    BookID INT,\n    QuantitySold INT,\n    SaleDate DATE,\n    FOREIGN KEY (BookID) REFERENCES Books(BookID)\n);\n\n-- Populate the Authors table\nINSERT INTO Authors (AuthorID, AuthorName) VALUES\n(1, 'J.K. Rowling'),\n(2, 'George Orwell'),\n(3, 'Jane Austen'),\n(4, 'Stephen King');\n\n-- Populate the Books table\nINSERT INTO Books (BookID, Title, AuthorID, PublicationYear) VALUES\n(1, 'Harry Potter and the Philosopher''s Stone', 1, 1997),\n(2, '1984', 2, 1949),\n(3, 'Pride and Prejudice', 3, 1813),\n(4, 'The Shining', 4, 1977),\n(5, 'Harry Potter and the Chamber of Secrets', 1, 1998);\n\n-- Populate the Sales table\nINSERT INTO Sales (SaleID, BookID, QuantitySold, SaleDate) VALUES\n(1, 1, 100, '2023-01-15'),\n(2, 2, 50, '2023-02-20'),\n(3, 3, 75, '2023-03-10'),\n(4, 4, 60, '2023-04-05'),\n(5, 5, 90, '2023-05-12'),\n(6, 1, 120, '2023-06-18'),\n(7, 2, 40, '2023-07-22'),\n(8, 3, 80, '2023-08-30');<\/code><\/pre>\n\n\n\n
Now that we’ve got our tables set up and populated, let’s see how we can write a query that uses a subquery in the SELECT<\/code> list. <\/p>\n\n\n\n
Example 1<\/h2>\n\n\n\n
Imagine we want to display each book’s title along with its total sales. Here’s how we can do that:<\/p>\n\n\n\n
SELECT \n    b.Title,\n    b.PublicationYear,\n    (SELECT SUM(QuantitySold) \n     FROM Sales s \n     WHERE s.BookID = b.BookID) AS TotalSales\nFROM \n    Books b\nORDER BY \n    TotalSales DESC;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
Title                                     PublicationYear  TotalSales
----------------------------------------  ---------------  ----------
Harry Potter and the Philosopher's Stone  1997             220       
Pride and Prejudice                       1813             155       
1984                                      1949             90        
Harry Potter and the Chamber of Secrets   1998             90        
The Shining                               1977             60        <\/pre>\n\n\n\n
In this query, we’re using a SQL subquery in the SELECT<\/code> list to calculate the total sales for each book. The subquery is correlated, meaning it refers to the outer query. We can see this with the part that goes WHERE s.BookID = b.BookID<\/code> part. The b<\/code> part is the table from the outer query.<\/p>\n\n\n\n
Example 2<\/h2>\n\n\n\n
Here’s another example where we use a subquery in the SELECT<\/code> list to find the most recent publication year for each author:<\/p>\n\n\n\n
SELECT \n    a.AuthorName,\n    (SELECT MAX(PublicationYear) \n     FROM Books b \n     WHERE b.AuthorID = a.AuthorID) AS LatestPublicationYear\nFROM \n    Authors a\nORDER BY \n    LatestPublicationYear DESC;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
AuthorName     LatestPublicationYear
-------------  ---------------------
J.K. Rowling   1998                 
Stephen King   1977                 
George Orwell  1949                 
Jane Austen    1813                 <\/pre>\n\n\n\n
Here, the inner query got the most recent publication for each author, and the outer query returned the authors’ names and ordered the results.<\/p>\n\n\n\n
Some Considerations<\/h2>\n\n\n\n
A correlated subquery can’t be run independently of the outer query. In other words, we can’t copy and paste the subquery and run it by itself. That’s because it refers to outer query, and so it requires the outer query in order to run.<\/p>\n\n\n\n
Putting a subquery into the SELECT<\/code> list (and using subqueries in general) can have its advantages:<\/p>\n\n\n\n