================================================ SearchDatabase.com's Database Developer April 25, 2001 ================================================ Welcome to the searchDatabase.com Database Developer newsletter! Today's tip, "UNION, EXCEPT, and INTERSECT clauses" can also be viewed online at: http://www.searchDatabase.com/tip/1,289483,sid13_gci547284,00.html ------------------------------------------------ LEARNING ZONE FEATURED BOOK OF THE WEEK ------------------------------------------------ "Sams Teach Yourself Oracle PL/SQL in 21 Days, Second Edition" By Jonathan Gennick This book quickly empowers you to create your own Oracle solutions with PL/SQL. Completely revised to cover Oracles 8i, the book provides guidance and direction, leading you through a progression of topics that begin with the basic building blocks of PL/SQL, and ending with in-depth discussions of the more commonly used advanced features of Oracle's database programming environment. http://www.digitalguru.com/dgstore/product.asp?isbn=0672317982%20&ac_id=58 ************************************************ "UNION, EXCEPT, and INTERSECT clauses" By Bruce Momjian Regular expressions, aggregates, and joins are powerful SQL features that allow the construction of complex queries. In some cases, however, even these tools may prove inadequate. This tip, excerpted from Bruce Momjian's new book "PostgreSQL: Introduction and Concepts" (available at http://www.digitalguru.com/dgstore/product.asp?sku=0201703319&dept%5Fid=233&ac%5Fid=58), shows one method how SELECTs can be combined to create even more powerful queries. Sometimes a single SELECT statement cannot produce the desired result. UNION, EXCEPT, and INTERSECT allow SELECT statements to be chained together, enabling the construction of more complex queries. For example, suppose we want to output the friend table's firstname and lastname in the same column. Normally, two queries would be required, one for each column.With UNION, however, the output of two SELECTs can be combined in a single query, as shown in Figure . test=> SELECT firstname test-> FROM friend test-> UNION test-> SELECT lastname test-> FROM friend test-> ORDER BY 1; firstname ---------------------- Dean Dick Gleason Millstone Ned Sandy Tabor Victor Weber Yeager (10 rows) The query combines two columns into a single output column. UNION allows an unlimited number of SELECT statements to be combined to produce a single result. Each SELECT must return the same number of columns. If the first SELECT returns two columns, the other SELECTs must return two columns as well. The column types must also be similar. If the first SELECT returns an INTEGER value in the first column, the other SELECTs must return an INTEGER in their first columns, too. With UNION, an ORDER BY clause can be used only at the end of the last SELECT. The ordering applies to the output of the entire query. In Figure , the ORDER BY clause specifies the ordering column by number. Instead of a number, we could use ORDER BY firstname because UNION's output labels are the same as the column labels of the first SELECT. As another example, suppose we have two tables that hold information about various animals. One table holds information about aquatic animals, and the other contains data about terrestrial animals. Two tables are used because each records information specific to one class of animal. The aquatic_animal table holds information meaningful only for aquatic animals, like preferred water temperature. The terrestrial_animal table holds information meaningful only for terrestrial animals, like running speed. We could have included the animals in the same table, but keeping them separate was clearer. In most cases, we will deal with the animal types separately. Suppose we need to list all of the animals, both aquatic and terrestrial. No single SELECT can show the animals from both tables. We cannot join the tables because no join key exists; joining is not desired. Instead, we want rows from the terrestrial_animal table and the aquatic_animal table output together in a single column. Figure shows how these two tables can be combined with UNION. test=> INSERT INTO terrestrial_animal (name) VALUES ('tiger'); INSERT 19122 1 test=> INSERT INTO aquatic_animal (name) VALUES ('swordfish'); INSERT 19123 1 test=> SELECT name test-> FROM aquatic_animal test-> UNION test-> SELECT name test-> FROM terrestrial_animal; name -------------------------------- swordfish tiger (2 rows) By default, UNION prevents duplicate rows from being displayed. For example, Figure inserts penguin into both tables, but penguin is not duplicated in the output. test=> INSERT INTO aquatic_animal (name) VALUES ('penguin'); INSERT 19124 1 test=> INSERT INTO terrestrial_animal (name) VALUES ('penguin'); INSERT 19125 1 test=> SELECT name test-> FROM aquatic_animal test-> UNION test-> SELECT name test-> FROM terrestrial_animal; name -------------------------------- penguin swordfish tiger (3 rows) To preserve duplicates, you must use UNION ALL, as shown here: test=> SELECT name test-> FROM aquatic_animal test-> UNION ALL test-> SELECT name test-> FROM terrestrial_animal; name -------------------------------- swordfish penguin tiger penguin (4 rows) You can perform more complex operations by chaining SELECTs. EXCEPT allows all rows to be returned from the first SELECT except rows that appear in the second SELECT. Figure shows an EXCEPT query. test=> SELECT name test-> FROM aquatic_animal test-> EXCEPT test-> SELECT name test-> FROM terrestrial_animal; name -------------------------------- swordfish (1 row) Although the aquatic_animal table contains swordfish and penguin, the query above returns only swordfish. The penguin is excluded from the output because it is returned by the second query. While UNION adds rows to the first SELECT, EXCEPT subtracts rows from it. INTERSECT returns only rows generated by all SELECTs. The listing below uses INTERSECT to display only penguin. While several animals are returned by the two SELECTs, only penguin is returned by both SELECTs. test=> SELECT name test-> FROM aquatic_animal test-> INTERSECT test-> SELECT name test-> FROM terrestrial_animal; name -------------------------------- penguin (1 row) You can link any number of SELECTs using these methods. The previous examples allowed multiple columns to occupy a single result column. Without the ability to chain SELECTs using UNION, EXCEPT, and INTERSECT, it would be impossible to generate some of these results. SELECT chaining can enable other sophisticated operations, such as joining a column to one table in the first SELECT, then joining the same column to another table in the second SELECT. "PostgreSQL: Introduction and Concepts" is available at: http://www.digitalguru.com/dgstore/product.asp?sku=0201703319&dept%5Fid=233&ac%5Fid=58&accountnumber=&couponnumber= ----------------------- ABOUT THE AUTHOR: Bruce Momjian is Vice President, Database Development, at Great Bridge, Inc. He is a leader and co-founder of the PostgreSQL Global Development Team and has independently developed open source software since 1991. ------------------------------------------------ FOR MORE INFORMATION ------------------------------------------------ The Best Open Source Web Links: http://searchdatabase.techtarget.com/bestWebLinks/0,289521,sid13_tax284986,00.html Ask your technical SQL questions--or help out your peers by answering them--in our live discussion forums: http://searchdatabase.techtarget.com/forums/0,289802,sid13_fid1,00.html Our database experts are waiting to answer your toughest SQL questions in our new Ask the Expert section: http://searchdatabase.techtarget.com/ateExperts/0,289622,sid13,00.html Have an open source or SQL tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize! http://searchdatabase.techtarget.com/tipsContest/0,289488,sid13_prz520733_cts520732,00.html ================================================ SUBMIT A TECHNICAL TIP AND WIN A PRIZE! ================================================ Do you have a time-saving shortcut, trick, or script that you want to share with other database pros? The first fifty individuals who submit a tip will receive a free searchDatabase.com hat. The highest rated tips each month will win our "Tip of the Month" contest and receive a high-quality searchDatabase.com denim shirt. We're accepting short, focused tips or code snippets on topics of interest to DBA's and database developers, such as: * Oracle * DB2 * SQL Server * database design * SQL * performance tuning Click here for more info and to submit your tip: http://searchdatabase.techtarget.com/tipsPrize/0,289492,sid13_prz520733_cts520732,00.html This will be a great way to share your knowledge, cement your status as an industry expert, and maybe win a prize. Send us your tip today! ******* Sponsored by Postmaster Direct ********* Get free offers from reputable merchants for products that you are interested in. Pick from over 50 categories of interest, modify your profile at any time to suit your needs, and receive only the email that interests you when you subscribe today. Just click on the link below and get your account up and running: http://searchdatabase.techtarget.com/postmasterDirect/1,289639,sid13,00.html ************************************************ To Remove your email address from the distribution list for this specific newsletter "Reply" to this message with REMOVE in the subject line. You will receive an email confirming that you have been removed. To Remove yourself from additional distribution lists or to update your preferences, go to the searchDatabase.com registration page at: http://searchDatabase.techtarget.com/register
