SQL Data Manipulation Language - …

1 Teradata DatabaseSQL data Manipulation LanguageRelease 2010 The product or products described in this book are licensed products of Teradata Corporation or its , BYNET, DBC/1012, DecisionCast, DecisionFlow, DecisionPoint, Eye logo design, InfoWise, Meta Warehouse, MyCommerce, SeeChain, SeeCommerce, SeeRisk, Teradata Decision Experts, Teradata Source Experts, WebAnalyst, and You ve Never Seen Your Business Like This Before are trademarks or registered trademarks of Teradata Corporation or its and SCSIS elect are trademarks or registered trademarks of Adaptec, Opteron and Opteron are trademarks of Advanced Micro Devices, and NetVault are trademarks or registered trademarks of BakBone Software, , PowerPath, SRDF, and Symmetrix are registered trademarks of EMC is a trademark of GoldenGate Software, and HP are registered trademarks of Hewlett-Packard , Pentium, and XEON are registered trademarks of Intel , CICS, RACF, Tivoli, and z/OS are registered trademarks of International Business Machines is a registered trademark of Linus and Engenio are registered trademarks of LSI , Active Directory, Windows, Windows NT, and Windows Server are registered trademarks of Microsoft Corporation in the United States and other and SUSE are registered trademarks of Novell, Inc.

2 , in the United States and other and SANbox are trademarks or registered trademarks of QLogic and SAS/C are trademarks or registered trademarks of SAS Institute is a registered trademark of SPARC International, Microsystems, Solaris, Sun, and Sun Java are trademarks or registered trademarks of Sun Microsystems, Inc., in the United States and other , NetBackup, and VERITAS are trademarks or registered trademarks of Symantec Corporation or its affiliates in the United States and other is a collective membership mark and a service mark of Unicode, is a registered trademark of The Open Group in the United States and other product and company names mentioned herein may be the trademarks of their respective INFORMATION CONTAINED IN THIS DOCUMENT IS PROVIDED ON AN AS-IS BASIS, WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESS OR IMPLIED, INCLUDING THE IMPLIED WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE, OR NON-INFRINGEMENT.

3 SOME JURISDICTIONS DO NOT ALLOW THE EXCLUSION OF IMPLIED WARRANTIES, SO THE ABOVE EXCLUSION MAY NOT APPLY TO YOU. IN NO EVENT WILL TERADATA CORPORATION BE LIABLE FOR ANY INDIRECT, DIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING LOST PROFITS OR LOST SAVINGS, EVEN IF EXPRESSLY ADVISED OF THE POSSIBILITY OF SUCH information contained in this document may contain references or cross-references to features, functions, products, or services that are not announced or available in your country. Such references do not imply that Teradata Corporation intends to announce such features, functions, products, or services in your country. Please consult your local Teradata Corporation representative for those features, functions, products, or services available in your contained in this document may contain technical inaccuracies or typographical errors.

4 Information may be changed or updated without notice. Teradata Corporation may also make improvements or changes in the products or services described in this information at any time without maintain the quality of our products and services, we would like your comments on the accuracy, clarity, organization, and value of this document. Please e-mail: comments or materials (collectively referred to as Feedback ) sent to Teradata Corporation will be deemed non-confidential. Teradata Corporation will have no obligation of any kind with respect to Feedback and will be free to use, reproduce, disclose, exhibit, display, transform, create derivative works of, and distribute the Feedback and derivative works thereof without limitation on a royalty-free basis. Further, Teradata Corporation will be free to use any ideas, concepts, know-how, or techniques contained in such Feedback for any purpose whatsoever, including developing, manufacturing, or marketing products or services incorporating 2000 2010 by Teradata Corporation.

5 All Rights data Manipulation Language3 PrefacePurposeSQL data Manipulation Language describes how to use SQL to manipulate this book with the other volumes in the SQL book programmers and end users are the principal audience for this manual. System administrators, database administrators, security administrators, Teradata field engineers, and other technical personnel responsible for designing, maintaining, and using Teradata Database might also find this manual to be Software ReleaseThis book supports Teradata Database If you are not familiar with Teradata Database, you should read Introduction to Teradata before reading this information about developing applications using embedded SQL is found in Teradata Preprocessor2 for Embedded SQL Programmer functions and operators are documented in SQL Functions, Operators, Expressions, and should be familiar with basic relational database management technology.

6 This book is not an SQL to This Book4 SQL data Manipulation LanguageChanges to This BookReleaseDescriptionTe r a d a t a Database 2010 Added syntax diagram for the HAVING clause to Chapter 1. Updated the syntax for DIAGNOSTIC r a d a t a Database 2009 Clarified the way locks are placed for SELECT requests. Clarified rules for using parameters inside a r a d a t a Database 2009 Added the HASH BY and LOCAL ORDER BY clauses to Chapter 1. Added scalar subqueries throughout the book. Moved the following statements from SQL data Definition Language to the new Chapter 4 of this book: COLLECT DEMOGRAPHICS COLLECT STATISTICS (QCD Form) DIAGNOSTIC COSTPRINT DIAGNOSTIC DUMP COSTS DIAGNOSTIC HELP COSTS DIAGNOSTIC SET COSTS DIAGNOSTIC HELP PROFILE DIAGNOSTIC SET PROFILE DIAGNOSTIC DUMP SAMPLES DIAGNOSTIC HELP SAMPLES DIAGNOSTIC SET SAMPLES DIAGNOSTIC Validate Index DROP STATISTICS (QCD Form) DUMP EXPLAIN INITIATE INDEX ANALYSIS INITIATE PARTITION ANALYSIS INSERT EXPLAIN RESTART INDEX ANALYSIS Updated the following statements: ABORT CALL COMMENT DELETE INITIATE INDEX ANALYSIS INSERT/INSERT.

7 SELECT INSERT EXPLAIN MERGE RESTART INDEX ANALYSIS ROLLBACK SELECT UPDATE UPDATE (Upsert Form) Updated the following request modifiers: EXPLAIN USING Updated the maximum number of tables and single-table views that can be joined to InformationSQL data Manipulation Language5 Additional InformationTe r a d a t a Database 2007 Updated the following statements: ABORT CALL DELETE INSERT/INSERT .. SELECT MERGE ROLLBACK SELECT the Teradata Information Products Publishing Library site to: View or download a manual:1 Under Online Publications, select General your search criteria and click Search. Download a documentation CD-ROM:1 Under Online Publications, select General the Title or Keyword field, enter CD-ROM, and click Search. Order printed manuals:Under Print & CD Publications, select How to Teradata home page provides links to numerous sources of information about Teradata.

8 Links include: Executive reports, case studies of customer experiences with Teradata, and thought leadership Technical information, solutions, and expert advice Press releases, mentions and media Customer Education designs, develops and delivers education that builds skills and capabilities for our customers, enabling them to maximize their Teradata Teradata @ Your Service to access Orange Books, technical alerts, and knowledge repositories, view and join forums, and download software Developer Exchange provides articles on using Teradata products, technical discussion forums, and code to Microsoft Windows and Linux6 SQL data Manipulation LanguageTo maintain the quality of our products and services, we would like your comments on the accuracy, clarity, organization, and value of this document.

9 Please e-mail: to Microsoft Windows and LinuxThis book refers to Microsoft Windows and Linux. For Teradata Database , these references mean: Windows is Microsoft Windows Server 2003 64-bit. Linux is SUSE Linux Enterprise Server 9 and SUSE Linux Enterprise Server data Manipulation Language7 Table of ContentsPreface..3 Purpose ..3 Audience ..3 Supported Software Release ..3 Prerequisites ..3 Changes to This Book ..4 Additional Information ..5 References to Microsoft Windows and Linux ..6 Chapter 1: The SELECT Statement .. 11 SELECT .. 12 SELECT AND CONSUME.. 46 SELECT .. INTO .. 50 WITH [RECURSIVE] Request Modifier .. 51 DISTINCT, ALL, and .ALL Options .. 71 TOP n Operator .. 78 FROM Clause .. 82 HASH BY Clause.. 90 LOCAL ORDER BY Clause .. 93 Derived Tables.

10 95 WHERE Clause .. 98 Subqueries in Search Conditions.. 107 Correlated Subqueries .. 111 Correlated Subqueries and SELECT Requests .. 118 Common Rules and Restrictions for Scalar Subqueries .. 121 GROUP BY Clause .. 124 How Extended GROUP BY Operator Specifications Are Resolved .. 131 CUBE Option .. 133 GROUPING SETS Option .. 137 ROLLUP Option .. 140 Table of Contents8 SQL data Manipulation LanguageHAVING Clause ..144 QUALIFY Clause..148 SAMPLE Clause ..152 SAMPLEID Expression..161 ORDER BY Clause ..163 WITH Clause..173 Chapter 2: Join Expressions ..181 Joins ..182 Inner Joins ..185 Ordinary Inner Join ..186 Cross Join..189 Self-Join ..191 Outer Joins..192 Definition of the Outer Join..193 Review of Relevant Relational Algebra for the Outer Join ..203 Left Outer Join.