Use of select to perform subqueries

Example 1

The following example retrieves the Name and Age columns from any record in the staff.employees table whose Name also exists in the Name column of the staff.managers table.

|phoenix:1.> select Name, Age from staff.employees
|phoenix:2.> where Name in
|phoenix:3.> ( ( select Name from staff.managers ) );
|phoenix:4.> go
..
{
        Name='Matt';
        Age=24;
}
{
        Name='Rob';
        Age=23;
}
( 2 record(s) : Transaction complete )

Example 2

The following example retrieves the Name and Age columns of the managers table where the value of the staff.managers.Age column matches one of the staff.employees.Age columns and is greater than 25.

|phoenix:1.> select Name, Age from staff.managers
|phoenix:2.> where Age in 
|phoenix:3.> (
|phoenix:4.>                            (
|phoenix:5.>                                select Age from staff.employees
|phoenix:6.>                                where Age > 25
|phoenix:7.>                            )
|phoenix:8.> );
|phoenix:9.> go
.
{
        Name='Matt';
        Age=28;
}
( 1 record(s) : Transaction complete )

The query returns only one record. Although two records from the managers table have ages that match the employees table, only one of the matches is also over 25.