Oracle SQL Jumpstart with Examples- P7: Review "As a consultant with more than 12 years of experience working with Oracle databases on a daily basis, reviewing this book was a unique and enjoyable experience. The SQL language is without doubt one of the most critical database skills and it is best learned by example. This book addresses that crucial need. Mr. Powell does an excellent job of clarifying the concepts by using meaningful and easy to understand examples. Frankly, I have not come across any other book on SQL that is as good a compilation of SQL concepts in a single. | 270 Demonstrating Subqueries Figure Subquery Comparison Condition Syntax. Inequality can be represented by or A Syntactically subquery is equivalent to Simple subquery subquery expression Like subquery LIKE subquery Membership subquery NOT IN subquery Exists NOT EXISTS subquery Range subquery BETWEEN subquery AND subquery Group subquery AN Y SOME ALL subquery Allows single row subquery ONLY A subquery itself is generally syntactically equivalent to a SELECT statement. Chapters 4 5 6 and 11 apply to subqueries in this respect. So far in this chapter we have seen a lot of information. The easiest way to explain subqueries is simply to demonstrate. Demonstrating Subqueries This section demonstrates use of the different types of subqueries Single-row subqueries. Multiple-row subqueries. Multiple-column subqueries. Regular versus correlated subqueries. Nested subqueries. Inline views or FROM clause embedded subqueries. Subqueries can be used in numerous SQL code commands and their subset clauses. Single-Row Subqueries A single-row subquery is exactly as its name implies a subquery that returns a single row. If more than one row is returned an error will result ORA-01427 single-row subquery returns more than one row . Simple equality LIKE and Range BETWEEN comparison conditions are restricted to single-row subquery results. See the syntax diagram in Figure . Please purchase PDF Split-Merge on to remove this watermark. Demonstrating Subqueries 271 Here is an easy way of understanding the concept of the single-row subquery. You can ask if Apple Pie equals Apple Pie but you cannot ask if Apple Pie is equal to both Apple Pie and Pumpkin Pie because you get two different answers at once. Apple pie is equal to apple pie but not equal to pumpkin pie. The same applies to testing for a number say 10 being BETWEEN five other numbers because it does not make sense. For example 10 BETWEEN 5 AND 20 4 30 cannot be evaluated because it is
