Skip to content

Latest commit

 

History

History
26 lines (22 loc) · 1.59 KB

3008.md

File metadata and controls

26 lines (22 loc) · 1.59 KB

Spaghetti Query Alert

Split up a complex spaghetti query into several simpler queries:

SQL is a very expressive language—you can accomplish a lot in a single query or statement. But that doesn't mean it's mandatory or even a good idea to approach every task with the assumption it has to be done in one line of code.

One common unintended consequence of producing all your results in one query is a Cartesian product. This happens when two of the tables in the query have no condition restricting their relationship. Without such a restriction, the join of two tables pairs each row in the first table to every row in the other table. Each such pairing becomes a row of the result set, and you end up with many more rows than you expect.

It's important to consider that these queries are simply hard to write, hard to modify, and hard to debug. You should expect to get regular requests for incremental enhancements to your database applications. Managers want more complex reports and more fields in a user interface. If you design intricate, monolithic SQL queries, it's more costly and time-consuming to make enhancements to them. Your time is worth something, both to you and to your project.

Split up a complex spaghetti query into several simpler queries. When you split up a complex SQL query, the result may be many similar queries, perhaps varying slightly depending on data values. Writing these queries is a chore, so it's a good application of SQL code generation. Although SQL makes it seem possible to solve a complex problem in a single line of code, don't be tempted to build a house of cards.