WIXLIB

Transact-SQL User’s GuideAdaptive Server Enterprise12.5

DOCUMENT ID: 32300-01-1250-03LAST REVISED: August 2002Copyright 1989-2002 by Sybase, Inc. All rights reserved.This publication pertains to Sybase software and to any subsequent release until otherwise indicated in new editions or technical notes.Information in this document is subject to change without notice. The software described herein is furnished under a license agreement,and it may be used or copied only in accordance with the terms of that agreement.To order additional documents, U.S. and Canadian customers should call Customer Fulfillment at (800) 685-8225, fax (617) 229-9845.Customers in other countries with a U.S. license agreement may contact Customer Fulfillment via the above fax number. All otherinternational customers should contact their Sybase subsidiary or local distributor. Upgrades are provided only at regularly scheduledsoftware release dates. No part of this publication may be reproduced, transmitted, or translated in any form or by any means, electronic,mechanical, manual, optical, or otherwise, without the prior written permission of Sybase, Inc.Sybase, the Sybase logo, AccelaTrade, ADA Workbench, Adaptable Windowing Environment, Adaptive Component Architecture,Adaptive Server, Adaptive Server Anywhere, Adaptive Server Enterprise, Adaptive Server Enterprise Monitor, Adaptive ServerEnterprise Replication, Adaptive Server Everywhere, Adaptive Server IQ, Adaptive Warehouse, Anywhere Studio, ApplicationManager, AppModeler, APT Workbench, APT-Build, APT-Edit, APT-Execute, APT-FORMS, APT-Translator, APT-Library, BackupServer, BizTracker, ClearConnect, Client-Library, Client Services, Convoy/DM, Copernicus, Data Pipeline, Data Workbench,DataArchitect, Database Analyzer, DataExpress, DataServer, DataWindow, DB-Library, dbQueue, Developers Workbench, DirectConnect Anywhere, DirectConnect, Distribution Director, e-ADK, E-Anywhere, e-Biz Integrator, E-Whatever, EC-GATEWAY,ECMAP, ECRTP, eFulfillment Accelerator, Embedded SQL, EMS, Enterprise Application Studio, Enterprise Client/Server, EnterpriseConnect, Enterprise Data Studio, Enterprise Manager, Enterprise SQL Server Manager, Enterprise Work Architecture, Enterprise WorkDesigner, Enterprise Work Modeler, eProcurement Accelerator, EWA, Financial Fusion, Financial Fusion Server, Gateway Manager,GlobalFIX, ImpactNow, Industry Warehouse Studio, InfoMaker, Information Anywhere, Information Everywhere, InformationConnect,InternetBuilder, iScript, Jaguar CTS, jConnect for JDBC, MainframeConnect, Maintenance Express, MDI Access Server, MDI DatabaseGateway, media.splash, MetaWorks, MySupport, Net-Gateway, Net-Library, New Era of Networks, ObjectConnect, ObjectCycle,OmniConnect, OmniSQL Access Module, OmniSQL Toolkit, Open Biz, Open Client, Open ClientConnect, Open Client/Server, OpenClient/Server Interfaces, Open Gateway, Open Server, Open ServerConnect, Open Solutions, Optima , PB-Gen, PC APT Execute, PCNet Library, Power , power.stop, PowerAMC, PowerBuilder, PowerBuilder Foundation Class Library, PowerDesigner,PowerDimensions, PowerDynamo, PowerJ, PowerScript, PowerSite, PowerSocket, Powersoft, PowerStage, PowerStudio, PowerTips,Powersoft Portfolio, Powersoft Professional, PowerWare Desktop, PowerWare Enterprise, ProcessAnalyst, Rapport, Report Workbench,Report-Execute, Replication Agent, Replication Driver, Replication Server, Replication Server Manager, Replication Toolkit, ResourceManager, RW-DisplayLib, S-Designor, SDF, Secure SQL Server, Secure SQL Toolset, Security Guardian, SKILS, smart.partners,smart.parts, smart.script, SQL Advantage, SQL Anywhere, SQL Anywhere Studio, SQL Code Checker, SQL Debug, SQL Edit, SQLEdit/TPU, SQL Everywhere, SQL Modeler, SQL Remote, SQL Server, SQL Server Manager, SQL SMART, SQL Toolset, SQL Server/CFT, SQL Server/DBM, SQL Server SNMP SubAgent, SQL Station, SQLJ, STEP, SupportNow, S.W.I.F.T. Message Format Libraries,Sybase Central, Sybase Client/Server Interfaces, Sybase Financial Server, Sybase Gateways, Sybase MPP, Sybase SQL Desktop, SybaseSQL Lifecycle, Sybase SQL Workgroup, Sybase User Workbench, SybaseWare, Syber Financial, SyberAssist, SyBooks, System 10,System 11, System XI (logo), SystemTools, Tabular Data Stream, TradeForce, Transact-SQL, Translation Toolkit, UNIBOM, Unilib,Uninull, Unisep, Unistring, URK Runtime Kit for UniCode, Viewer, Visual Components, VisualSpeller, VisualWriter, VQL,WarehouseArchitect, Warehouse Control Center, Warehouse Studio, Warehouse WORKS, Watcom, Watcom SQL, Watcom SQL Server,Web Deployment Kit, Web.PB, Web.SQL, WebSights, WebViewer, WorkGroup SQL Server, XA-Library, XA-Server and XP Server aretrademarks of Sybase, Inc. 07/02Unicode and the Unicode Logo are registered trademarks of Unicode, Inc.All other company and product names used herein may be trademarks or registered trademarks of their respective companies.Use, duplication, or disclosure by the government is subject to the restrictions set forth in subparagraph (c)(1)(ii) of DFARS 52.2277013 for the DOD and as set forth in FAR 52.227-19(a)-(d) for civilian agencies.Sybase, Inc., One Sybase Drive, Dublin, CA 94568.

ContentsAbout This Book . xixCHAPTER 1SQL Building Blocks. 1SQL in Adaptive Server. 1Queries, data modification, and commands . 2Tables, columns, and rows. 3The relational operations . 3Compiled objects . 4Naming conventions. 6SQL data characters . 7SQL language characters. 7Identifiers . 7Expressions in Adaptive Server . 13Arithmetic and character expressions . 13Relational and logical expressions . 19Transact-SQL extensions. 20The compute clause . 21Control-of-flow language . 21Stored procedures . 21Extended stored procedures . 22Triggers . 23Defaults and rules . 23Error handling and set options . 23Additional Adaptive Server extensions to SQL. 25Compliance to ANSI standards . 27Federal Information Processing Standards (FIPS) flagger. 28Chained transactions and isolation levels . 28Identifiers . 28SQL standard-style comments . 29Right truncation of character strings. 29Permissions required for update and delete statements . 29Arithmetic errors . 29Synonymous keywords . 30Treatment of nulls. 31Transact-SQL User’s Guideiii

ContentsAdaptive Server login accounts.Group membership.Role membership .Getting information about your Adaptive Server account.Changing your password.Understanding remote logins .How to use Transact-SQL with the isql utility.Default databases .Using network-based security services with isql .Logging out of isql .Using the pubs2 and pubs3 sample databases .What is in the sample databases? .313232333334363637383838CHAPTER 2Queries: Selecting Data from a Table . 41What are queries? . 41select syntax. 42Choosing columns: the select clause . 44Choosing all columns: select * . 44Choosing specific columns. 45Rearranging the order of columns. 45Renaming columns in query results . 46Expressions. 47Selecting text and image values. 53Select list summary . 55Eliminating duplicate query results with distinct . 55Specifying tables: the from clause . 57Selecting rows: the where clause. 58Comparison operators. 59Ranges (between and not between) . 61Lists (in and not in) . 62Pattern matching . 65Matching character strings: like. 65Character strings and quotation marks . 71“Unknown” values: NULL . 71Connecting conditions with logical operators . 77CHAPTER 3Using Aggregates, Grouping, and Sorting. 81Using aggregate functions . 81Aggregate functions and datatypes. 83count vs. count(*) . 84Using aggregate functions with distinct . 85Null values and the aggregate functions . 86Organizing query results into groups: the group by clause . 87ivAdaptive Server

Contentsgroup by syntax . 88Referencing other columns in queries using group by . 90Expressions and group by . 93Nesting aggregates with group by. 94Null values and group by. 95where clause and group by . 96group by and all. 97Using aggregates without group by. 98Selecting groups of data: the having clause . 99How the having, group by, and where clauses interact . 101Using having without group by . 104Sorting query results: the order by clause. 105order by and group by . 107order by and group by used with select distinct . 107Summarizing groups of data: the compute clause . 108Row aggregates and compute . 111Specifying more than one column after compute . 112Using more than one compute clause. 113Applying an aggregate to more than one column. 114Using different aggregates in the same compute clause . 115Grand values: compute without by . 115Combining queries: the union operator . 117Guidelines for union queries. 119Using union with other Transact-SQL commands. 121CHAPTER 4Joins: Retrieving Data from Several Tables .How joins work .Join syntax .Joins and the relational model.How joins are structured .The from clause.The where clause .How joins are processed .Equijoins and natural joins .Joins with additional conditions.Joins not based on equality.Self-joins and correlation names.The not-equal join .Not-equal joins and subqueries.Joining more than two tables .Outer joins.Inner and outer tables .Outer join restrictions .Views used with outer joins .Transact-SQL User’s 140140141142v

ContentsCHAPTER 5CHAPTER 6viANSI Inner and outer joins .ANSI outer joins .Transact-SQL outer joins .How null values affect joins .Determining which table columns to join.142148160164165Subqueries: Using Queries Within Other Queries.How subqueries work.Subquery syntax.Subquery restrictions.Example of using a subquery .Qualifying column names .Subqueries with correlation names .Multiple levels of nesting .Subqueries in update, delete, and insert statements .Subqueries in conditional statements.Using subqueries instead of expressions.Types of subqueries.Expression subqueries .Quantified predicate subqueries.Subqueries used with in .Subqueries used with not in .Subqueries using not in with NULL .Subqueries used with exists.Subqueries used with not exists.Finding intersection and difference with exists.Using correlated subqueries .Correlated subqueries containing Transact-SQL outer joins.Correlated subqueries with correlation names .Correlated subqueries with comparison operators.Correlated subqueries in a having clause 89192192194195196196198Using and Creating Datatypes.How Transact-SQL datatypes work .Using system-supplied datatypes .Exact numeric types: integers .Exact numeric types: decimal numbers.Approximate numeric datatypes.Money datatypes .Date and time datatypes .Character datatypes .Binary datatypes.The bit ive Server