T-SQL: Need Top N to always return N rows, even if null or blank

Question

T-SQL: Need Top N to always return N rows, even if null or blank

Typically, the command

Select Top 5 * FROM ourTable

will return up to 5 rows, but less, depending whether the rows exist.

I want to ensure that it always returns 5 rows, (or in general N rows). What is the syntax to achieve this?

The idea is to sort of generalize the LINQ concept of "FirstOrDefault" to "First_N_OrDefault", but using TSQL not LINQ.

Clearly, the 'extra' rows would have null or empty columns.

This is for Microsoft SQL Server 2014 using SSMS 14.0.17

I want to use the "TOP" syntax, if at all possible, therefore it is different than the possible duplicate. Also, as noted below, this is possibly something that could be solved at a different layer in the system, but it would be nice to have for TSQL as well.

Answer 1

You can use another dummy table with rows to generate empty rows of your table with a not matching JOIN. So you don't have to repeat the columns and rows in the UNION ALL part:

SELECT TOP 5 * FROM (
   SELECT 0 AS isDummy, * FROM table_name
   -- WHERE column_name = value
   UNION ALL
   SELECT 1 AS isDummy, t1.* FROM table_name t1 
      RIGHT JOIN INFORMATION_SCHEMA.COLUMNS ON t1.id = -1000 -- not valid condition so t1 columns are empty.
) t2
ORDER BY isDummy ASC

In this case the INFORMATION_SCHEMA.COLUMNS table is used to generate the additional rows. You can choose any other table with rows. You can use a TOP N value up to the count of rows in the right table (here: INFORMATION_SCHEMA.COLUMNS).

---

You can also generate a table with many rows (like on a calendar table):

SELECT TOP 5 * FROM (
    SELECT 0 isDummy, * FROM table_name
    -- WHERE column_name = value
    UNION ALL
    SELECT 1 isDummy, t1.* FROM table_name t1 RIGHT JOIN (
        SELECT * FROM 
            (SELECT 0 t0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t0,
            (SELECT 0 t1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1,
            (SELECT 0 t2 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2,
            (SELECT 0 t3 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t3,
            (SELECT 0 t4 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t4
    ) t2 ON t1.id = -1000 -- not valid condition so t1 columns are empty.
)x
ORDER BY isDummy ASC