OUTER JOIN queries in SQL are susceptible to two very subtle bugs that I’ve observed a number of times in the real world. Daniel and I have been hammering out ways to automatically detect queries that suffer from these bugs, in a relatively new Maatkit tool called mk-query-advisor. It’s part of our series of advisor …
There is usually more than one way to write a given query, but not all ways are created equal. Some mathematically equivalent queries can have drastically different performance. This article examines one of the motivations for inventing LEFT OUTER join and including it in the SQL standard: improved performance through exclusion joins.
LEFT OUTER join syntax …
Here’s a way to write join clauses so they are more compact, more readable, and less confusing.
Standard SQL:2003 defines a USING clause that can serve the same function as the ON clause in the familiar JOIN syntax. For example, the following join
select a.col1, b.col2
inner join b on a.col3 = b.col3
May be …
In this article I’ll show several ways to emulate a FULL OUTER join on a RDBMS that doesn’t support it, as is the case with even the most recent versions of MySQL. This useful query is surprisingly tricky to get right.
A standard SQL FULL OUTER join is like a LEFT or RIGHT join, except that …
Event delegation is an advanced technique for event-driven programming. The idea: instead of attaching one listener onto each element in a group, attach one listener onto an ancester shared by all the elements in that group. Then, when the listener is triggered, determine whether to act by looking at which element originally received the action.
Introduction to JSON
Ajax functionality is contained in the global Ajax object. The transport for …
In early versions of Prototype, the framework came with basic support for class creation: the Class.create() method. Until now the only feature of classes defined this way was that the constructor called a method called initialize automatically.
Prototype 1.6.0 now features a richer class system that's backward-compatible and adds some new features.
The cornerstone of class creation …
Prototype takes the complexity out of client-side web programming. Built to solve real-world problems, it adds useful extensions to the browser scripting environment and provides elegant APIs around the clumsy interfaces of Ajax and the Document Object Model.
Getting started: Defining classes and inheritance • How Prototype extends the DOM • Introduction to Ajax • Using …
from the Artful Common Queries page
There are two main ways to reconcile payments against charges:
Open Item: match payments against individual charges, typically by carrying the charge number in the payments table
Statement: list and sum all charges and all payments, and show the difference as the outstanding balance.
The Open Item method needs a foolproof way …
Parents without children
Given tables parent(id INT), child(id INT,parent_id INT), how do we find parents with no children? It's the All X for which there is no Y pattern, which can be written as an exclusion join...
LEFT JOIN child ON parent.id = child.parent_id
WHERE child.parent_id IS NULL;
or with a NOT EXISTS subquery, which is logically equivalent to the exclusion join, but usually performs much slower:
SELECT parent.id AS ParentID
WHERE NOT EXISTS (
JOIN child ON parent.ID = child.parent_id
Parties who …
About the Abstract Factory
In the Abstract Factory Pattern, an abstract factory defines what objects the non-abstract or concrete factory will need to be able to create. The concrete factory must create the correct objects for it's context, insuring that all objects created by the concrete factory have been chosen to be able to work correctly …