Subqueries

■Subqueries can be nested to an unlimited depth in a FROM clause but to "only" 255 levels in a WHERE clause;

■Can be used in clause:

SELECT ,FROM,WHERE ,HAVING;

■A subquery can be used to select rows for insertion but not in a VALUES clause of an INSERT statement.

eg: ● insert into dates select sysdate from dual;

　 × insert into dates (date_col) values (select sysdate fom dual);

■■Using NOT IN is fraught with problems because of the way SQL handles NULLs. As a general rule, do not use NOT IN unless you are certain that the　result set will not include a NULL.

NOT IN (!=all) ;IN (=any)

■■■Star Transformation

An extension of the use of subqueries as an alternative to a join is to enable the star transformation often needed in data warehouse applications. Consider a large table recording sales. Each sale is marked as being of a particular product to a particular buyer through a particular channel. These attributes are identified by codes, used as foreign keys to dimension tables with rows that describe each product, buyer, and channel. To identify all sales of books to buyers in Germany through Internet orders, one could run a query like this: select … from sales s, products p, buyers b, channels c where s.prod_code=p.prod_code and s.buy_code=b.buy_code and s.chan_code=c.chan_code and p.product=’Books’ and b.country=’Germany’ and c.channel=’Internet’; This query uses the WHERE clause to join the tables and then to filter the results. The following is an alternative query that will yield the same result: select … from sales where prod_code in (select prod_code from products where product=’Books’) and buy_code in (select buy_code from buyers where country=’Germany’) and chan_code in (select chan_code from channels where channel=’Internet); The rewrite of the first statement to the second is the star transformation. Apart from being an inherently more elegant structure (most SQL developers with any sense of aesthetics will agree with that), there are technical reasons why the database may be able to execute it more efficiently than the original query. Also, star queries are easier to maintain; it is very simple to add more dimensions to the query or to replace the single literals (‘Books,’ ‘Germany,’ and ‘Internet’) with lists of values. There is an instance initialization parameter, STAR_TRANSFORMATION_ ENABLED, which (if set to true) will permit the Oracle query optimizer to re-write code into star queries.