- Sams Teach Yourself SQL in 24 Hours, Third Edition
- Table of Contents
- Copyright
- About the Authors
- Acknowledgments
- Tell Us What You Think!
- Introduction
- Part I: A SQL Concepts Overview
- Hour 1. Welcome to the World of SQL
- SQL Definition and History
- SQL Sessions
- Types of SQL Commands
- An Introduction to the Database Used in This Book
- Summary
- Q&A
- Workshop
- Part II: Building Your Database
- Hour 2. Defining Data Structures
- What Is Data?
- Basic Data Types
- Summary
- Q&A
- Workshop
- Hour 3. Managing Database Objects
- What Are Database Objects?
- What Is a Schema?
- A Table: The Primary Storage for Data
- Integrity Constraints
- Summary
- Q&A
- Workshop
- Hour 4. The Normalization Process
- Normalizing a Database
- Summary
- Q&A
- Workshop
- Hour 5. Manipulating Data
- Overview of Data Manipulation
- Populating Tables with New Data
- Updating Existing Data
- Deleting Data from Tables
- Summary
- Q&A
- Workshop
- Hour 6. Managing Database Transactions
- What Is a Transaction?
- What Is Transactional Control?
- Transactional Control and Database Performance
- Summary
- Q&A
- Workshop
- Part III: Getting Effective Results from Queries
- Hour 7. Introduction to the Database Query
- What Is a Query?
- Introduction to the <tt>SELECT</tt> Statement
- Examples of Simple Queries
- Summary
- Q&A
- Workshop
- Hour 8. Using Operators to Categorize Data
- What Is an Operator in SQL?
- Comparison Operators
- Logical Operators
- Conjunctive Operators
- Negating Conditions with the <tt>NOT</tt> Operator
- Arithmetic Operators
- Summary
- Q&A
- Workshop
- Hour 9. Summarizing Data Results from a Query
- What Are Aggregate Functions?
- Summary
- Q&A
- Workshop
- Hour 10. Sorting and Grouping Data
- Why Group Data?
- The <tt>GROUP BY</tt> Clause
- <tt>GROUP BY</tt> Versus <tt>ORDER BY</tt>
- The <tt>HAVING</tt> Clause
- Summary
- Q&A
- Workshop
- Hour 11. Restructuring the Appearance of Data
- The Concepts of ANSI Character Functions
- Various Common Character Functions
- Miscellaneous Character Functions
- Mathematical Functions
- Conversion Functions
- The Concept of Combining Character Functions
- Summary
- Q&A
- Workshop
- Hour 12. Understanding Dates and Times
- How Is a Date Stored?
- Date Functions
- Date Conversions
- Summary
- Q&A
- Workshop
- Part IV: Building Sophisticated Database Queries
- Hour 13. Joining Tables in Queries
- Selecting Data from Multiple Tables
- Types of Joins
- Join Considerations
- Summary
- Q&A
- Workshop
- Hour 14. Using Subqueries to Define Unknown Data
- What Is a Subquery?
- Embedding a Subquery Within a Subquery
- Summary
- Q&A
- Workshop
- Hour 15. Combining Multiple Queries into One
- Single Queries Versus Compound Queries
- Why Would I Ever Want to Use a Compound Query?
- Compound Query Operators
- Using an <tt>ORDER BY</tt> with a Compound Query
- Using <tt>GROUP BY</tt> with a Compound Query
- Retrieving Accurate Data
- Summary
- Workshop
- Q&A
- Part V: SQL Performance Tuning
- Hour 16. Using Indexes to Improve Performance
- What Is an Index?
- How Do Indexes Work?
- The <tt>CREATE INDEX</tt> Command
- Types of Indexes
- When Should Indexes Be Considered?
- When Should Indexes Be Avoided?
- Summary
- Q&A
- Workshop
- Hour 17. Improving Database Performance
- What Is SQL Statement Tuning?
- Database Tuning Versus SQL Tuning
- Formatting Your SQL Statement
- Full Table Scans
- Other Performance Considerations
- Performance Tools
- Summary
- Q&A
- Workshop
- Part VI: Using SQL to Manage Users and Security
- Hour 18. Managing Database Users
- Users Are the Reason
- The Management Process
- Tools Utilized by Database Users
- Summary
- Q&A
- Workshop
- Hour 19. Managing Database Security
- What Is Database Security?
- How Does Security Differ from User Management?
- What Are Privileges?
- Controlling User Access
- Controlling Privileges Through Roles
- Summary
- Q&A
- Workshop
- Part VII: Summarized Data Structures
- Hour 20. Creating and Using Views and Synonyms
- What Is a View?
- Creating Views
- Dropping a View
- What Is a Synonym?
- Summary
- Q&A
- Workshop
- Hour 21. Working with the System Catalog
- What Is the System Catalog?
- How Is the System Catalog Created?
- What Is Contained in the System Catalog?
- Examples of System Catalog Tables by Implementation
- Querying the System Catalog
- Updating System Catalog Objects
- Summary
- Q&A
- Workshop
- Part VIII: Applying SQL Fundamentals in Today's World
- Hour 22. Advanced SQL Topics
- Advanced Topics
- Cursors
- Stored Procedures and Functions
- Triggers
- Dynamic SQL
- Call-Level Interface
- Using SQL to Generate SQL
- Direct Versus Embedded SQL
- Summary
- Q&A
- Workshop
- Hour 23. Extending SQL to the Enterprise, the Internet, and the Intranet
- SQL and the Enterprise
- Accessing a Remote Database
- Accessing a Remote Database Through a Web Interface
- SQL and the Internet
- SQL and the Intranet
- Summary
- Q&A
- Workshop
- Hour 24. Extensions to Standard SQL
- Various Implementations
- Examples of Extensions from Some Implementations
- Interactive SQL Statements
- Summary
- Q&A
- Workshop
- Part IX: Appendixes
- Appendix A. Common SQL Commands
- SQL Statements
- SQL Clauses
- Appendix B. Using MySQL for Exercises
- Windows Installation Instructions
- Linux Installation Instructions
- Appendix C. Answers to Quizzes and Exercises
- Hour 1, "Welcome to the World of SQL"
- Hour 2, "Defining Data Structures"
- Hour 3, "Managing Database Objects"
- Hour 4, "The Normalization Process"
- Hour 5, "Manipulating Data"
- Hour 6, "Managing Database Transactions"
- Hour 7, "Introduction to the Database Query"
- Hour 8, "Using Operators to Categorize Data"
- Hour 9, "Summarizing Data Results from a Query"
- Hour 10, "Sorting and Grouping Data"
- Hour 11, "Restructuring the Appearance of Data"
- Hour 12, "Understanding Dates and Time"
- Hour 13, "Joining Tables in Queries"
- Hour 14, "Using Subqueries to Define Unknown Data"
- Hour 15, "Combining Multiple Queries into One"
- Hour 16, "Using Indexes to Improve Performance"
- Hour 17, "Improving Database Performance"
- Hour 18, "Managing Database Users"
- Hour 19, "Managing Database Security"
- Hour 20, "Creating and Using Views and Synonyms"
- Hour 21, "Working with the System Catalog"
- Hour 22, "Advanced SQL Topics"
- Hour 23, "Extending SQL to the Enterprise, the Internet, and the Intranet"
- Hour 24, "Extensions to Standard SQL"
- Appendix D. <tt>CREATE TABLE</tt> Statements for Book Examples
- <tt>EMPLOYEE_TBL</tt>
- <tt>EMPLOYEE_PAY_TBL</tt>
- <tt>CUSTOMER_TBL</tt>
- <tt>ORDERS_TBL</tt>
- <tt>PRODUCTS_TBL</tt>
- Appendix E. <tt>INSERT</tt> Statements for Data in Book Examples
- <tt>INSERT</tt> Statements
- Appendix F. Glossary
- Appendix G. Bonus Exercises
Recommended Book
Recommended Book
Recommended Book
Formatting Your SQL Statement
Formatting your SQL statement sounds like an obvious statement; as obvious as it may sound, it is worth mentioning. There are several things that a newcomer to SQL will probably not take into consideration when building a SQL statement. The following sections discuss the listed considerations; some are common sense, others are not so obvious:
- Formatting SQL statements for readability
- The order of tables in the FROM clause
- The placement of the most restrictive conditions in the WHERE clause
- The placement of join conditions in the WHERE clause
Formatting a Statement for Readability
Formatting a SQL statement for readability is pretty obvious, but many SQL statements have not been written neatly. Although the neatness of a statement does not affect the actual performance (the database does not care how neat the statement appears), careful formatting is the first step in tuning a statement. When you look at a SQL statement with tuning intentions, making the statement readable is always the first thing to do. How can you determine whether the statement is written well if it is difficult to read?
Some basic rules for making a statement readable include:
- Always begin a new line with each clause in the statement— For example, place the FROM clause on a separate line from the SELECT clause. Place the WHERE clause on a separate line from the FROM clause, and so on.
- Use tabs or spaces for indentation when arguments of a clause in the statement exceed one line.
- Use tabs and spaces consistently.
- Use table aliases when multiple tables are used in the statement— The use of the full table name to qualify each column in the statement quickly clutters the statement and makes reading it difficult.
- Use remarks sparingly in SQL statements if they are available within your specific implementation— Remarks are great for documentation, but too many of them clutter a statement.
- Begin a new line with each column name in the SELECT clause if many columns are being selected.
- Begin a new line with each table name in the FROM clause if many tables are being used.
- Begin a new line with each condition of the WHERE clause— You can easily see all conditions of the statement and the order in which they are used.
The following is an example of an unreadable statement:
SELECT CUSTOMER_TBL.CUST_ID, CUSTOMER_TBL.CUST_NAME, CUSTOMER_TBL.CUST_PHONE, ORDERS_TBL.ORD_NUM, ORDERS_TBL.QTY FROM CUSTOMER_TBL, ORDERS_TBL WHERE CUSTOMER_TBL.CUST_ID = ORDERS_TBL.CUST_ID AND ORDERS_TBL.QTY > 1 AND CUSTOMER_TBL.CUST_NAME LIKE 'G%' ORDER BY CUSTOMER_TBL.CUST_NAME;
CUST_ID CUST_NAME CUST_PHONE ORD_NUM QTY ---------- ------------------------------ ---------- ----------------- --- 287 GAVINS PLACE 3172719991 18D778 10 1 row selected.
The following is an example of a reformatted statement for improved readability:
Both statements are exactly the same, but the second statement is much more readable. The second statement has been greatly simplified by using table aliases, which have been defined in the query's FROM clause. Spacing has been used to align the elements of each clause, making each clause stand out.
Again, making a statement more readable does not directly improve its performance, but it assists you in making modifications and debugging a lengthy and otherwise complex statement. Now you can easily identify the columns being selected, the tables being used, the table joins being performed, and the conditions being placed on the query.
Proper Arrangement of Tables in the FROM Clause
The arrangement or order of tables in the FROM clause may make a difference, depending on how the optimizer reads the SQL statement. For example, it may be more beneficial to list the smaller tables first and the larger tables last. Some users with lots of experience have found that listing the larger tables last in the FROM clause proves to be more efficient.
The following is an example FROM clause:
FROM SMALLEST TABLE,
LARGEST TABLE
Proper Order of Join Conditions
As you learned in Hour 13, "Joining Tables in Queries," most joins use a BASE TABLE to link tables that have one or more common columns on which to join. The BASE TABLE is the main table that most or all tables are joined to in a query. The column from the BASE TABLE is normally placed on the right side of a join operation in the WHERE clause. The tables being joined to the BASE TABLE are normally in order from smallest to largest, similar to the tables listed in the FROM clause.
Should there not be a BASE TABLE, the tables should be listed from smallest to largest, with the largest tables on the right side of the join operation in the WHERE clause. The join conditions should be in the first position(s) of the WHERE clause followed by the filter clause(s), as shown in the following:
FROM TABLE1, Smallest Table
TABLE2, to
TABLE3 Largest Table, also BASE TABLE
WHERE TABLE1.COLUMN = TABLE3.COLUMN Join condition
AND TABLE2.COLUMN = TABLE3.COLUMN Join condition
[ AND CONDITION1 ] Filter condition
[ AND CONDITION2 ] Filter condition
In this example, TABLE3 is used as the BASE TABLE. TABLE1 and TABLE2 are joined to TABLE3 for both simplicity and proven efficiency.
The Most Restrictive Condition
The most restrictive condition is typically the driving factor in achieving optimal performance for a SQL query. What is the most restrictive condition? The condition in the WHERE clause of a statement that returns the fewest rows of data. Conversely, the least restrictive condition is the condition in a statement that returns the most rows of data. This hour is concerned with the most restrictive condition simply because it is this condition that filters the data that is to be returned by the query the most.
It should be your goal for the SQL optimizer to evaluate the most restrictive condition first because a smaller subset of data is returned by the condition, thus reducing the query's overhead. The effective placement of the most restrictive condition in the query requires knowledge of how the optimizer operates. The optimizers, in some cases, seem to read from the bottom of the WHERE clause up. Therefore, you would want to place the most restrictive condition last in the WHERE clause, which is the condition that is first read by the optimizer.
FROM TABLE1, Smallest Table
TABLE2, to
TABLE3 Largest Table, also BASE TABLE
WHERE TABLE1.COLUMN = TABLE3.COLUMN Join condition
AND TABLE2.COLUMN = TABLE3.COLUMN Join condition
[ AND CONDITION1 ] Least restrictive
[ AND CONDITION2 ] Most restrictive
The following is an example using a phony table:
|
Table: |
TEST |
|
Row count: |
95,867 |
|
Conditions: |
WHERE LAST_NAME = 'SMITH' returns 2,000 rows WHERE CITY = 'INDIANAPOLIS' returns 30,000 rows |
|
Most restrictive condition is: |
WHERE LAST_NAME = 'SMITH' |
QUERY1:
QUERY2:
Suppose that QUERY1 completed in 20 seconds, whereas QUERY2 completed in 10 seconds. Because QUERY2 returned faster results and the most restrictive condition was listed last in the WHERE clause, it would be safe to assume that the optimizer reads the WHERE clause from the bottom up.