SQL in the Wild » Blog Archive » NOT EXISTS vs NOT IN. Continuing with the mini-series on query operators, I want to have a look at NOT EXISTS and NOT IN.
Previous parts of this miniseries are: Just one note before diving into that. The examples I’m using are fairly simplistic and that’s intentional. I’m trying to find what, if any, are the performance differences in a benchmark-style setup. I’ll have some comments on more complex examples in a later post. The most important thing to note about NOT EXISTS and NOT IN is that, unlike EXISTS and IN, they are not equivalent in all cases. The reason for this can be found by looking at the details of what the NOT IN operation actually means. Let’s say, for illustration purposes that there are 4 rows in the table called t, there’s a column called ID with values 1..4 Let’s further say that AVal is NULL where ID = 4. Hence, if any row of that subquery returns NULL, the entire NOT IN operator will evaluate to either FALSE or NULL and no records will be returned So what about EXISTS? Then the queries.
Introduction to Indexes. Good indexes are the key to good performance in SQL Server and the key to creating good indexes is to understand what indexes are and how SQL Server uses them to evaluate queries.
In this first part of a three part series, I’m going to look at the very basics of what indexes are, what types exist in SQL and how they’re used. What is an index? An index is a structure within SQL that is used to quickly locate specific rows within a table. It can be useful to imaging an index at the back of a textbook when thinking about SQL indexes. They both serve the same purpose – to find specific information quickly. General Structure An index is defined on one or more columns, called key columns.
Figure 1: Book index. If an index is created with more than one key column, it is known as a composite index. The general structure of an index is that of a balanced tree (b-tree). Note: Commonly the root page is shown at the top of the tree diagram and the leaf pages at the bottom. Clustered and nonclustered 1. Myth Directory. SQLAlchemy and You. Written on Tuesday, July 19, 2011 Without doubt are most new Python web programmers these days chosing the Django framework as their gateway drug to Python web development.
As such many people's first experience with a Python ORM (or maybe an ORM altogether) is the Django one. When they are later switching to something else they often find SQLAlchemy unnecessarily complex and hard to use. Why is that the case? I made a quick poll on Twitter about why people prefer the Django ORM over SQLAlchemy and I got back a few interesting results. SQLAlchemy in general just has a much larger featureset and it's the only ORM for Python which allows you to take full advantage of your database and does not stand in your way. This article assumes that you have some basic Django knowledge and want to give SQLAlchemy a try. Design Differences There are two very important differences between SQLAlchemy and Django. What's the difference? SQLAlchemy's Complexity The Session — The Heart of the ORM.