Oracle SQL Jumpstart with Examples- P6

Oracle SQL Jumpstart with Examples- P6: 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...
Nội dung trích xuất từ tài liệu:
Oracle SQL Jumpstart with Examples- P6 220 10.3 Examining Different Types of Joins Figure 10.17 Oracle Format Left Outer Join of ARTIST and GUESTAPPEARA NCE Tables. Refer back to Figure 10.15 to validate artists who do not have guest appearances on any songs. You will see that these artists (starting with Sheryl Crow and ending with James Taylor) appear in Figure 10.17 with a blank space in the SONG_ID and GUESTARTIST_ID. The query could not match any row in the GUESTAPPEARANCE table with these artists in the ARTIST table. Oracle Database 10g automatically returns a null value as a placeholder in the results for the unmatched rows. Look at the last five rows in Figure 10.17. These are the artists who do make guest appearances. Notice that the ARTIST_ID column and the GUESTARTIST_ID column contain the same number in every row. This makes sense because the query equates the values in the two columns. These rows are finding themselves in the ARTIST table. Any row in the GUE- STAPPEARANCE table must match a row in the ARTIST table. The second left outer join query, shown following, is the ANSI version of the first left outer join query. The result is shown in Figure 10.18. One difference between the Oracle format join in Figure 10.17 and the ANSI format join in Figure 10.18 is the sorted order of null values. SELECT A.NAME, GA.SONG_ID, A.ARTIST_ID, GA.GUESTARTIST_ID FROM ARTIST A LEFT OUTER JOIN GUESTAPPEARANCE GA ON (A.ARTIST_ID = GA.GUESTARTIST_ID);Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. 10.3 Examining Different Types of Joins 221 Figure 10.18 ANSI Format Left Outer Join of the ARTIST and GUESTAPPEARA NCE Tables. The third and last left outer join query is a more complex variation of the first two using the ANSI format and the DECODE function. Note: The DECODE function is an embedded case statement (see Chapter 9). The following query lists all of the artists in the ARTIST table. It returns one of two phrases, depending on whether the artist makes a guest appearance on a song or not. If not, the phrase “ is an Artist.” follows the artist’s name. If otherwise, the phrase “ made a guest appearance on …” fol- lows the artist’s name, including the appropriate song title. The result as shown in Figure 10.19 is a left outer join between all three ARTIST, GUE- STAPPEARANCE, and SONG tables. SELECT A.NAME|| DECODE (S.TITLE, NULL, is an Artist. , made a guest appearance on ||S.TITLE||. ) as What they did FROM ARTIST A LEFT OUTER JOIN GUESTAPPEARANCE GA ON (A.ARTIST_ID = GA.GUESTARTIST_ID) LEFT OUTER JOIN SONG S ON (S.SONG_ID = GA.SONG_ID) ORDER BY A.NAME, S.TITLE; Chapter 10Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. 222 10.3 Examining Different Types of Joins Figure 10.19 Left Outer Join Between ARTIST, GUESTAPPEAR ANCE, and SONG Tables. Notice in the Oracle-formatted query in Figure 10.19 that the two left outer joins are identified by the (+) symbol next to the appropriate columns in the WHERE clause. Here is another variation that returns the same result. In the following query, the Oracle format uses an embedded subquery statement (see Chap- ter 12) rather than a WHERE clause addition using the SONG and ...