Tham khảo tài liệu 'oracle database 10g the complete reference phần 3', công nghệ thông tin, cơ sở dữ liệu phục vụ nhu cầu học tập, nghiên cứu và làm việc hiệu quả | Chapter 13 When One Query Depends upon Another 253 A third option full outer join returns all rows from both tables. Rows that do not satisfy the on condition return NULL values. In this example there are no rows to satisfy this condition so the query returns the same 31 rows as the right outer join. select MAX - Most Days Out from BOOKSHELF_ChECKOUT BC full outer join BOOKSHELF B on group by Prior to Oracle9 Z you can generate the full outer join results by performing two separate outer joins using each table as the outer table and using a union operation to combine the results in a single query. Replacing NOT IN with an Outer Join The various logical tests that can be done in a where clause all have their separate performance measures. A NOT IN test may force a full read of the table in the subquery select. For example what books were not checked out You could write a query like this select Title from BOOKSHELF where Title not in select Title from BOOKSHELF_CHECKOUT order by Title TITLE BOX SOCIALS CHARLOTTE S WEB COMPLETE POEMS OF JOHN KEATS EMMA WHO SAVED MY LIFE GOSPEL JOURNALS OF LEWIS AND CLARK KIERKEGAARD ANTHOLOGY LETTERS AND PAPERS FROM PRISON PREACHING TO HEAD AND HEART RUNAWAY BUNNY SHOELESS JOE THE COST OF DISCIPLESHIP THE GOOD BOOK TRUMPET OF THE SWAN UNDER THE EYE OF THE CLOCK This is typically the way such a query would be written even though experienced Oracle users know it may be slow you may be forcing Oracle to perform a time-intensive full table scan on the BOOKSHELF_CHECKOUT table. The optimizer may internally transform that NOT IN to one of the following functionally identical approaches. The following query uses an outer 254 Part II SQL and SQL Plus join and produces the same result. The difference is that this one will be efficient because the optimizer can take advantage of indexes on the join columns select distinct from BOOKSHELF_CHECKOUT BC right outer join .
