nesting query

You can combine multiple queries to control the output of other queries with th output generated by one query. To put it a bit plainly, nesting queries so that the inner query generates a value that the outer query’s predicate evaluates to determine if it is true.

SELET　受注番号　FROM 受注表
　WHERE 商品コード = 
    (SELECT 商品コード　FROM 商品表
　　　　WHERE 商品名 = 'JUIICE');

入れ子になっていますね。