TAILIEUCHUNG - SQL PROGRAMMING STYLE- P19

SQL PROGRAMMING STYLE- P19:Im mot 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). | Avoid UNIONs 127 HAVING MAX payment_status F AND MIN payment_status F The self-reference and correlation are complicated for both humans and machines. Most optimizers are not smart enough to flatten the first query like this. Exceptions If you have a problem that is easier to understand with correlated subqueries and your optimizer is good then don t be so afraid of them. Avoid UNIONs Rationale UNIONs are usually not well optimized. Because they require that redundant duplicates be discarded they force most SQL engines to do a sort before presenting the result set to the user. If possible use UNION ALL instead. You should never have to build a chain of UNIONs from the same base table. That code can be written with OR-ed predicates or CASE expressions. As an example of a horrible misuse of SQL Chris White posted a procedure that built dynamic SQL that would then build a report. Aside from the obvious violations of basic software engineering the output was so huge that it exceeded the text size limits of SQL Server. He was attempting to construct an entire report in the database by using UNIONs to get the 12 lines of the report in the right order by assigning them a letter of the alphabet. The whole thing would take several pages to show but it is an extraction of the printout lines that were constructed from just the General Ledger. I have not attempted to clean up much of the code so there are many violations of good coding rules in this snippet. UNION SELECT DISTINCT J AS section NUMBER CHECKS AS description AS branch COUNT DISTINCT AS total1 0 AS total2 FROM GeneralLedger AS GL WHERE start_period AND end_period AND period_yr 128 CHAPTER 6 CODING CHOICES AND IN 3020 AND IN CD UNION SELECT DISTINCT C AS section CASH RECEIPTS AS description AS branch SUM -1 AS totall 0 AS total2 FROM GeneralLedger AS GL WHERE start_period AND end_period AND .

• Cơ sở dữ liệu
• cơ sở dữ liệu MySQL
• database SQL
• thiết kế CSDL quan hệ
• Kiến trúc hệ quản trị CSDL
• đặc tính cơ sở dữ liệu
• bảo mật cơ sở dữ liệu
• Cơ sở dữ liệu
• Hệ quản trị cơ sở dữ liệu MySQL
• Quản trị cơ sở dữ liệu
• Làm việc với MySQL
• Giới thiệu về MySQL
• Bài giảng Phần mềm nguồn mở
• Phần mềm nguồn mở
• Hệ quản trị cơ sở dữ liệu
• Lịch sử phát triển MySQL
• Kiến trúc MySQL
• MySQL Command Line
• MySQL Query Browser
• giáo trình cơ sở dữ liệu
• lý thuyết cơ sở dữ liệu
• bái giảng cơ sở dữ liệu
• tài liệu cơ sở dữ liệu
• Kết nối với MySQL
• Truy xuất cơ sở dữ liệu
• Quản lý dữ liệu
• Kết nối cơ sở dữ liệu
• Cập nhật dữ liệu
• Kích hoạt dữ liệu
• cơ sở dữ liệu sql server
• MySQL cơ bản