SQL union queries and COUNT function

Task: show books that have more than one author

Solution

SELECT [books].[b_id],
                [books].[b_name],
                COUNT([m2m_books_authors].[a_id]) AS [authors_count]
FROM [books]
    JOIN [m2m_books_authors]
        ON [books].[b_id] = [m2m_books_authors].[b_id]
GROUP BY [books].[b_id],
                     [books].[b_name]
HAVING COUNT ([m2m_books_authors].[a_id]) > 1

Task: show how many copies of each book are currently in the library

Option 1: Use a correlated subquery

SELECT DISTINCT [b_id],
                                  [b_name],
                                   ( [b_quantity] - (SELECT COUNT([int].[sb_book])
                                                                               FROM [subscriptions] AS [int]
                                                                               WHERE [int].[sb_book] = [ext].[sb_book]
                                                                                    AND [int].[sb_is_active] = 'Y') )
AS
                                   [real_count]
FROM [books]    LEFT OUTER JOIN [subscriptions] AS [ext]
        ON [books].[b_id] = [ext].[sb_book]
ORDER BY [real_count] DESC

Option 1 starts by performing a merge. Next, the result of the correlated subquery is subtracted from the value of the b_quantity field for each book. If in such a request we substitute the value of the identifier instead of the expression [ext].[sb_book], then this code fragment can be executed as an independent request and get a specific number (for example, for a book with identifier 1, this will be the number 2, i.e. two copies The book is currently in the hands of readers.

Option 2: Use a common table expression and a correlated subquery

WITH [books_taken]
    AS (SELECT [sb_book] AS [b_id],
                           COUNT([sb_book]) AS [taken]
            FROM     [subscriptions]
            WHERE  [sb_is_active] = 'Y'
            GROUP BY [sb_book])
SELECT [b_id],
                [b_name],
                ( [b_quantity] - ISNULL((SELECT [taken]
                                                           FROM    [books_taken]
                                                           WHERE [books].[b_id] = [books_taken].[b_id]), 0
                                          ) ) AS
                [real_count]
FROM [books]
ORDER BY [real_count] DESC

Option 2 is based on the fact that the general table expression prepares information about the number of copies of books in the hands of readers.

Next, a correlated subquery is executed that returns, for each book, the number of copies handed out to readers, or NULL if no copies are handed out. To be able to correctly use such a result in an arithmetic expression, we use the ISNULL function, which converts NULL values to 0.

Option 3: Applying a common table expression and a subquery step by step

WITH [books_taken]
            AS (SELECT [sb_book]
                    FROM    [subscriptions]
                    WHERE [sb_is_active] = 'Y'),
            [real_taken]
            AS (SELECT [b_id],
                                    COUNT([sb_book]) AS [taken]
                   FROM [books]
                                LEFT OUTER JOIN [books_taken]
                                                            ON [b_id] = [sb_book]
                   GROUP BY [b_id])
SELECT [b_id],
                [b_name],
                ( [b_quantity] - (SELECT [taken]
                                           FROM [real_taken]
                                           WHERE [books].[b_id] = [real_taken].[b_id]) ) AS
                [real_count]
FROM [books]
ORDER BY [real_count] DESC

Option 3, based on stepping through two common table expressions, prepares a complete set of data for the correlated subquery. Based on the received data, the correlater calculates the actual number of book copies in the library. Since the data from the second common table expression comes with "ready-zeroes" for books for which no copies have been issued to readers, there is no need to use the ISNULL function here.

Option 4: without subqueries

WITH [books_taken]
            AS (SELECT [sb_book],
                                    COUNT([sb_book]) AS [taken]
                    FROM [subscriptions]
                    WHERE [sb_is_active] = 'Y'
                    GROUP BY [sb_book])
SELECT [b_id],
                [b_name],
                ( [b_quantity] - ISNULL([taken], 0) ) AS [real_count]
FROM [books]
                LEFT OUTER JOIN [books_taken]
                                            ON [b_id] = [sb_book]
ORDER BY [real_count] DESC

Option 4 is based on preliminary preparation in a general tabular expression of information on how many books are lent to readers, and then subtracting this number from the number of books registered in the library. Since when grouping books for which no instances are given to readers, the value taken will be NULL, we apply an ISNULL function that converts NULL values to 0.