returning a null value instead of no value Why are the last 2 selects (cases 5 and 6) returning a null value? This may seem like a trivial question but it actually caused a bug in one of my joins where I expected rows to be filtered out by cross applying to some value and instead it returned the row with a null value.

DROP TABLE IF EXISTS #temp;
CREATE TABLE #temp (t VARCHAR(1));


Cases 1-4 return expected "no value" result. 

-- CASE 1
SELECT *
FROM #temp;

-- CASE 2
SELECT (SELECT t)
FROM #temp;

-- CASE 3
SELECT (T)
FROM #temp;

-- CASE 4
SELECT CAST(value AS VARCHAR(MAX))
FROM STRING_SPLIT('t', ',') s
WHERE EXISTS (
        SELECT 1
        FROM #temp
        WHERE t = 't'
        );


Problem cases - returning "NULL".

-- CASE 5
SELECT (
        SELECT value
        FROM STRING_SPLIT('t', ',') s
        WHERE EXISTS (
                SELECT 1
                FROM #temp
                WHERE t = 't'
                )
        );

-- CASE 6
SELECT (
        SELECT T
        FROM #TEMP
        );
Your last 2 SELECTs are without a FROM clause. These will always return a single row of scalar values.
The fact that the subquery did not return any rows, caused the expression to be NULL - unknown.
Think about the following statement - how would you expect the engine to handle this:

 SELECT 1 AS FirstCol, (&lt;some SELECT that returns no rows&gt;) AS SecondCol


Note: This is a non standard T-SQL extension. In Oracle for example, you need to add the FROM DUAL or you will get ORA-00923: FROM keyword not found where expected. However, this same statement with FROM DUAL added run in Oracle will also result in a single row of scalar values.