Celko - Joe Celkos SQL Programming Style

Joe Celkos SQL Programming Style

Joe Celko

Morgan Kaufmann

Publishing Director Michael Forster Publisher Diane Cerra Publishing Services Manager Andre Cuello Senior Production Editor George Morrison Editorial Assistant Asma Stephan Cover Design Side by Side Studios Cover Image Side by Side Studios Composition Multiscience Press, Inc. Copyeditor Multiscience Press, Inc. Proofreader Multiscience Press, Inc. Indexer Multiscience Press, Inc. Interior printer The Maple-Vail Book Manufacturing Group Cover printer Phoenix Color Corp.

Morgan Kaufmann Publishers is an imprint of Elsevier. 500 Sansome Street, Suite 400, San Francisco, CA 94111

This book is printed on acid-free paper.

Designations used by companies to distinguish their products are often claimed as trademarks or registered trademarks. In all instances in which Morgan Kaufmann Publishers is aware of a claim, the product names appear in initial capital or all capital letters. Readers, however, should contact the appropriate companies for more complete information regarding trademarks and registration.

No part of this publication may be reproduced, stored in a retrieval system, or transmitted in any form or by any means-electronic, mechanical, photocopying, scanning, or otherwise-without prior written permission of the publisher.

Permissions may be sought directly from Elsevier's Science & Technology Rights Department in Oxford, UK: phone: (+44) 1865 843830, fax: (+44) 1865 853333, e-mail: ) by selecting Customer Support and then Obtaining Permissions.

Library of Congress Cataloging-in-Publication Data

Application submitted.

ISBN: 0-12-088797-5

For information on all Morgan Kaufmann publications, visit our Web site at www.mkp.com or www.books.elsevier.com

Printed in the United States of America 05 06 07 08 5 4 3 2 1

To Eve Astrid Adersson, Miss American and April Wilson, who rubs me the right way

Introduction

I AM NOT trying to teach you to program in SQL in this book. You might want to read that again. If that is what you wanted, there are better books. This ought to be the second book you buy, not the first.

I assume that you already write SQL at some level and want to get better at it. If you want to learn SQL programming tricks, get a copy of my other book, SQL for Smarties (3rd edition, 2005). I am trying to teach the reader how to work in logical and declarative terms, instead of in a procedural or OO mannerQuery Eye for the Database Guy, if you will forgive a horrible contemporary pun.

Few, if any, SQL programmers came to SQL before learning and writing for years in a procedural or object-oriented language. They then got one particular SQL product and were told to learn it on their own or with a book that has a title like SQL for Brain-Dead Morons, Learn SQL in Ten Easy Lessons or Five Hard Ones, or worse.

This is absurd! It takes at least five years to learn to be a master carpenter or chef. Why would you believe people could become SQL gurus in a weekend? What they become is bad SQL programmers, who speak SQL in dialect from the local SQL product with a strong accent from their previous languages. You might want to read Teach Yourself Programming in Ten Years by Peter Norvig (www.norvig.com/21-days.html) or No Silver Bullets by Fred Brooks, Computer, 20(4): 1019, April 1987) to get a reality check.

The horrible part is that these people often dont know they are bad programmers. At one extreme, the entire shop where they work is just as bad, and they never see anything else. At the other extreme, if anyone tries to tell them about their problems, they become defensive or angry. If you look at postings on SQL newsgroups, many programmers just want to get a kludge for an immediate problem and not actually obtain a true long-term solution.

If these were woodworking newsgroups, their questions would be the equivalent of What are the best kind of rocks to use to pound screws into fine furniture? When someone tells them to use large chunks of granite, they are happy, but if you try to tell them about screwdrivers, they explode into a rage.

You might want to read an essay on this phenomenon: Unskilled and Unaware of It: How Difficulties in Recognizing Ones Own Incompetence Lead to Inflated Self-Assessments by Justin Kruger and David Dunning (Department of Psychology, Cornell University, www.apa.org/journals/psp/psp7761121.html).

Or look at the actual and self-assessments of American high school students in mathematics and sciences that were part of the Bush administrations No Child Left Behind Act.

So how did we old farts learn to be better programmers when dinosaurs walked the earth? One of the best helpers we had in the late 1970s when the structured programming revolution came along was a series of books entitled [Pascal | FORTRAN | COBOL | BASIC] with Style: Programming Proverbs by Henry Ledgard and some of his colleagues at MIT. The covers were done like a Victorian novel with angels, scrolls, and old-style typographical elements. And like a Victorian novel, the books were subtitled Principles of Good Programming with Numerous Examples to Improve Programming Style and Proficiency. These books and others made a big difference for most of us because they taught us how to think like good programmers.

My goals in this book are to improve SQL programming style and proficiency. To be more exact:

If you are the team leader, you now have a book (and author) that you can hold up and blame for anything that your people do not like. Even if I am later shown to be wrong about something, you will have been consistent. It is much easier to repair errors if they were made consistently.

Craig Mullins provided the structure of the chapter on VIEWs in an article are based on internal standards from Teradata Corporation. The scales and measurements and the encoding schemes material appeared in several of my old magazine columns in DBMS and Database Programming & Design before they were collected into a chapter in my book Data & Database (Morgan-Kaufmann Publishers). I have tried to give credit in the text, but so many people have participated in the newsgroups over the years that I know I am forgetting someone.

And, obviously, thanks to Henry Ledgard and his Programming Proverbs series for the inspiration.

I would also like to thank all of the newbie programmers who wrote bad code. It sounds a bit sarcastic, but it is not meant to be. Many of thenewbies are programmers who were thrown into a DBA or SQL programmer job by management without training or an experienced mentor. I do not want to blame the victims unless they are really not working on getting better. Your errors in syntax, semantics, and style showed me how you were thinking. Diagnosis is the first step to treatment.