Using an Asterisk in Nested select Statements

You can use an asterisk (*) in a nested select statement that is not an exists subquery as long as the asterisk follows certain conditions.

The asterisk:

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

Use Table-Name Qualifiers
You can use an asterisk in the form of qualifier.* (qualifier <period> asterisk), to select only those columns that are in your specified table.
Use Nested Queries with group by
You can use an asterisk in a nested group by query as long as the group-by table has a single column.
Usage and Examples of Asterisks in select Statements
SAP ASE automatically replaces asterisks in queries with actual column names before saving new stored procedures, views, and triggers.

Parent topic: Subqueries: Queries Within Other Queries