Nested Sql query

Description:

         Here one query is nested into another i.e. result of one query is used to find another query result.

     Below query shows that where clause uses value retrieved by query which is written under where clause for satisfying condition.

      But it is always better to use join instead of Nested query. on large data performance of nested queries are not good as compare to join query.

SQL Query:

select ProgramName from AMSProgram
where ProgramId in (
select distinct ProgramId from AMSAgreement_transaction_master
where agreement_no in (101,243,250)
)