Predicate Evaluation Order
Just before Part 3 of this series, we took a brief pause to talk about Predicates in SQL, as they factored into mistakes related to Outer Joins. In this final installment of this series on Top SQL Query Mistakes, predicates will once again enter the picture, as we examine how predicate evaluation order can cause seemingly well constructed queries to fail with errors.
Breaking Subqueries
In this series on Top SQL Query Mistakes, we've seen several examples of SQL queries that look perfectly solid on first inspection, but can lead to erroneous results and/or performance degradation. Last week, learned how the placement of predicates can adversely affect query execution - particularly in outer joins. Today's installment will focus on subqueries, and how they can break an SQL statement when changes are made to any of its underlying tables.
Outer Joins and Cartesian Products
In this series on Top SQL Query Mistakes, we've been exploring how seemingly intuitive ways of constructing SQL queries can result in anti-patterns that lead to erroneous results and/or performance degradation. Last week, we took a break from the series to talk about Predicates in SQL. In this installment, we'll be learning how their placement can adversely affect query execution - particularly in outer joins.
This week, we're going to briefly hit the Pause button from the Some Top SQL Query Mistakes series in order to talk about Predicates in SQL. The reason is that Predicates will factor into Part 3 of the Top SQL Query Mistakes series.
Part 2: Non-SARGable Query Conditions
Like most programmers, database developers tend to write code that is more or less a direct translation of a given request. The fact that most programming languages - SQL included - are designed to be human readable, also contributes to this problem. Why is this a concern? All programming languages execute certain operations faster than others. In relational databases, the query optimizer analyzes SQL queries and determines the efficient execution mechanisms called query plans. The optimizer generates one or more query plans for each query, each of which represent one possible way to run a query. The most efficient query plan is then selected and utilized to run the query. As it turns out, SQL that mimics the language of a request is seldom the most efficient approach.
In this installment of the Top SQL Query Mistakes series, we'll explore one example of a poorly written SQL statement and rewrite it in a way that increases efficiency.
- 2024 (1)
- 2023 (1)
- 2022 (1)
- 2021 (1)
- 2020 (1)
- 2019 (1)
- 2018 (1)
- 2017 (1)
