Is it better to name SQL tables with singular or plural names? In my opinion, the most important thing to do is pick one and stick to it. Programmers will always argue about singular vs. plural, and it is possible to make a valid case for both. My strong preference is singular. The name is the table name; it is not the name of the tuples. A bag does not become a “bags” when you put apples in it.

Continue Reading

Bitwise arithmetic can be very useful, and not just for C and graphics programmers, but for all types of programming tasks. Those who use it frequently enough become fluent. Here I present a few tips. As I think of more, I will add them here. Signed versus unsigned Remember numbers can be signed or unsigned. Know the difference, and know the arithmetic for both. For example, when checking to see if a certain bit is set:

Continue Reading

If your RDBMS does not support the ROW_NUMBER function, it is still possible to write some queries without it. ROW_NUMBER operates over a partition of the result set, and returns a number for each row in a partition (it is possible to specify numbering schemes besides consecutive integers). Ranking and windowing functions are available in Microsoft SQL Server 2005, and are part of the newer SQL standards. I expect it will be some time before MySQL implements them.

Continue Reading

Simpler is often better. The ability to decide quickly between two equivalent solutions is what sets expert programmers apart. It has been demonstrated over and over again that a) there is more than a tenfold difference in productivity between programmers, and b) there is absolutely zero correlation between speed and quality in programming. Choosing the best solution to a problem is an acquired skill that takes time and experience, but it takes something more as well: a certain mental attitude.

Continue Reading

Many people are confused by SQL terminology, particularly by subqueries and derived tables, and use the terms incorrectly. In fact the official terminology is defined very specifically. It is a good idea to use the right language. Not only does it foster clear understanding, but among people who know the difference, it helps create a good impression of competence and attention to detail. The official standard defines a derived table as follows:1

Continue Reading

If necessary, INSERT IF NOT EXISTS queries can be written in a single atomic statement, eliminating the need for a transaction, and without violating standards. In this article I’ll explain several ways to write such queries in a platform-independent way. Motivation Suppose a table of urls has a unique index on the url: create table urls(url varchar(200) primary key); Now suppose I want to insert a row in a single statement, without using a transaction, and I only want the row to get inserted if the url isn’t already in the table.

Continue Reading

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 was added to the SQL-92 standard specifically to address certain queries that had only been possible with NOT IN subqueries.

Continue Reading

MySQL does not support UNION prior to version 4.0.0, but it is possible to write a UNION with a mutex table and LEFT OUTER JOIN. As an example, UNION these tables together: create table colors ( title varchar(20) ); create table flavors( title varchar(20) ); insert into colors values ('blue'), ('green'); insert into flavors values ('vanilla'), ('chocolate'); The trick is to LEFT OUTER join such that the values from one table do not get included in the same row with values from other tables, then COALESCE the values to select the (only) non-NULL value from each row.

Continue Reading

Here’s an ‘interactive’ directory merging tool that doesn’t require GUI libraries, printer subsystems, and so forth. It is useful when you want to do things the easy way, at a terminal, without taking your hands off your keyboard. And it works on systems where you don’t have access to install things yourself, such as a shared webhosting server. #!/bin/bash echo "#!/bin/bash" > /tmp/mergedir diff --brief --recursive $1 $2 | awk '/differ/ {print "vimdiff " $2 " " $4 ";"}' >> /tmp/mergedir chmod +x /tmp/mergedir /tmp/mergedir Create that as a shell script, chmod +x it, put it in your PATH, and invoke it like this, assuming you called it mergedir:

Continue Reading

A “mutex” table is a clever trick that allows joining tables while keeping them independent of each other in a query. This property allows interesting queries that are not otherwise possible. It is especially useful in earlier versions of MySQL, where it can be used to simulate some unsupported queries such as UNION and derived tables in the FROM clause. My standard mutex table is as follows: create table mutex( i int not null primary key ); insert into mutex(i) values (0), (1); I typically use the mutex table as the leftmost table of LEFT OUTER JOIN queries.

Continue Reading

Some queries that seemingly require the use of subqueries in the FROM clause (commonly called derived tables) can be written without them. This is useful for earlier versions of MySQL, which do not support subqueries. In this article I’ll explain how to get the effect of subqueries without actually using them. The basic principle that makes this work is this: a subquery in the FROM clause really works like an anonymous view, and can be accomplished as joins to base tables, as long as the joins don’t interact.

Continue Reading

Victory comes from a pragmatic blend of small wins and big wins. I have too often seen people shoot for the moon, miss, and end up with nothing. The big wins are possible, but many of them will not happen, and you need a steady diet of small wins to keep things going, both practically and psychologically.

Continue Reading