You can use an asterisk (*) in a nested select statement that is not an exists subquery as long as the asterisk:
Is the only item in the select statement.
Resolves to a single table column for the nested query.
In addition, you can:
Restrict the selected columns in your nested query to only those belonging to a specific table by using the qualifier.* format, where qualifier is one of the tables in the from clause.
Use the asterisk in a nested query that includes a group by clause.
When an asterisk resolves to a single table column for the nested query, the query is equivalent to explicitly using a single table column.
This is a valid nested query, because t2 only has one column:
1> create table t1(c1 int, c2 int) 2> create table t2(c1 int) 3>go 1>select * from t1 where c1 in (select * from t2) 2>go
The nested select statement is equivalent to:
1> select * t1 where c1 in (select c1 from t2) 2> go
In versions of Adaptive Server earlier than 15.7, you could use an asterisk only in a nested exists subquery.
