OUTER/CROSS APPLY Subquery without FROM clause

Question

Most online documentation or tutorials discussing OUTER|CROSS APPLY describe something like:

SELECT columns
FROM table OUTER|CROSS APPLY (SELECT … FROM …);

The subquery is normally a full SELECT … FROM … query.

I must have read somewhere that the subquery doesn’t need a FROM in which case the columns appear to come from the main query:

SELECT columns
FROM table OUTER|CROSS APPLY (SELECT … );

because I have used it routinely as a method to pre-calculate columns.

The question is what is really happening if the FROM is omitted from the sub query? Is it short for something else? I found that it does not mean the same as from the main table.

I have a sample here: http://sqlfiddle.com/#!18/0188f7/4/1

Answer 1

Omitting the FROM statement is not specific to a CROSS/OUTER APPLY; any valid SQL select statement can omit it. By not using FROM you have no source for your data, so you can't specify columns within that source. Rather you can only select values that already exist; be that constants defined in the statement itself, or in some cases (e.g. subqueries) columns referenced from other parts of the query.

This is simpler to understand if you're familiar with Oracle's Dual table; a table with 1 row. In MS SQL that table would look like this:

-- Ref: https://blog.sqlauthority.com/2010/07/20/sql-server-select-from-dual-dual-equivalent/
CREATE TABLE DUAL
(
    DUMMY VARCHAR(1) NOT NULL 
    , CONSTRAINT CHK_ColumnD_DocExc  CHECK (DUMMY = 'X') -- ensure this column can only hold the value X 
    , CONSTRAINT PK_DUAL PRIMARY KEY (DUMMY) -- ensure we can only have unique values... combined with the above means we can only ever have 1 row
)  
GO
INSERT INTO DUAL (DUMMY)
VALUES ('X')
GO

You can then do select 1 one, 'something else' two from dual. You're not really using dual; just ensuring that you have a table which will always return exactly 1 row.

Now in SQL anywhere you omit a FROM statement consider that statement as if it said FROM DUAL / it has the same meaning, only SQL allows this more shorthand approach.

Update

You mention in the comments that you don't see how you can reference columns from the original statement when in a subquery (e.g. of the kind you may see when using APPLY). The below code shows this without the APPLY scenario. Admittedly the demo code here's not somehting you'd ever use (since you could just to where Something like '%o%' on the original statement without needing the subquery/in statement), but for illustrative purposes it shows exactly the same sort of scenario as you've got with your APPLY scenario; i.e. the statement is just returning the value of SOMETHING for the current row.

declare @someTable table (
    Id bigint not null identity(1,1)
    , Something nvarchar(32) not null
)
insert @someTable (Something) values ('one'), ('two'), ('three')

select * 
from @someTable x
where x.Something in 
(
    -- this subquery references the SOMETHING column from above, but doesn't have a FROM statement
    -- note: there is only 1 value at a time for something here; not all 3 values at once; it's the same single value as Something as we have before the in keyword above
    select Something 
    where Something like '%o%'
)

Answer 2

What you have mentioned is not SUBQUERY. It is separate table expression. Whether you use FROM clause in the right expression or not problem.

1. If you use FROM clause in right table expression then you have got a source for the data in right table expression.
2. If you dont use FROM clause in the right expression, your source of data comes from left table expression.

First we will see what is APPLY operator. Reference BOL

Using APPLY

Both the left and right operands of the APPLY operator are table expressions. The main difference between these operands is that the right_table_source can use a table-valued function that takes a column from the left_table_source as one of the arguments of the function. The left_table_source can include table-valued functions, but it cannot contain arguments that are columns from the right_table_source.

The APPLY operator works in the following way to produce the table source for the FROM clause:

1. Evaluates right_table_source against each row of the left_table_source to produce rowsets.

   The values in the right_table_source depend on left_table_source. right_table_source can be represented approximately this way: TVF(left_table_source.row), where TVF is a table-valued function.

2. Combines the result sets that are produced for each row in the evaluation of right_table_source with the left_table_source by performing a UNION ALL operation.

   The list of columns produced by the result of the APPLY operator is the set of columns from the left_table_source that is combined with the list of columns from the right_table_source.

Based on the way you are using APPLY operator, it will behave as correlated subquery or CROSS JOIN

1. Using values of the left table expression in right table expression

--  without FROM (similar to Correlated Subquery)
    SELECT id, data, value
    FROM test OUTER APPLY(SELECT data*10 AS value) AS sq;

2. Not using values of left table expression in right table expression

--  FROM table (Similar to cross join)
    SELECT id, data, value
    FROM test OUTER APPLY(SELECT data*10 AS value FROM test) AS sq;

Answer 3

First consider

SELECT o.name, o.type
FROM sys.objects o

Now consider

SELECT o.name, (SELECT o.type) AS type
FROM sys.objects o

A SELECT without a FROM is as though selecting from an imaginary single row table. The above doesn't change the results the scalar subquery just acts as a correlated sub query and uses the value from the outer query.

APPLY behaves in the same way. References to columns from the outer query are just passed in as correlated parameters. So this is the same as

SELECT o.name, ca.type
FROM sys.objects o
CROSS APPLY (SELECT o.type) AS ca

But APPLY in general is more capable than a scalar subquery in the SELECT (in that it can act to expand a row out or remove rows from the result)