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.
NOT IN Versus NOT EXISTS
There's a term the is commonly thrown around in programming circles called "anti-patterns". It refers to a response to a recurring problem that is not only ineffective, but also risks being highly counterproductive. The term was originally coined in 1995 by computer programmer Andrew Koenig, in his book Design Patterns, as the antithesis of design patterns that are considered to be both reliable and effective.
- 2024 (1)
- 2023 (1)
- 2022 (1)
- 2021 (1)
- 2020 (1)
- 2019 (1)
- 2018 (1)
- 2017 (1)
