By “Good SQL Querying”, I am not referring to “how to make your queries more perfomant.” I am about to go on a mini-rant about how to make readable and self-documenting SQL queries.

One practice that will get me instantly going on a rant is using a comma join. There is NO reason to do the following:

-- uses the sakila sample database
SELECT first_name, last_name, address
FROM customer,address;

What kind of join did the original author intend? A CROSS JOIN? Or did they really want an INNER JOIN and forget the WHERE clause?

The answer: you do not know for sure; you can only guess. Had the query been

SELECT first_name,last_name,address
FROM customer INNER JOIN address;

you would know that the author intended an INNER JOIN; had the query been

SELECT first_name,last_name,address
FROM customer CROSS JOIN address;

you would know that the author intended a CROSS JOIN. I advocate using INNER JOIN instead of JOIN because like the comma, JOIN is ambiguous.

For full disclosure, the MySQL EXPLAIN plan is exact same for the above queries. The difference is purely semantic, but I think it a very important difference, because it makes the query author’s intentions clear. There are issues with the comma join not having the same precedence as INNER JOIN, but that is for more complex queries. To wit:

SELECT first_name,last_name,address
FROM customer, address INNER JOIN city;

will actually do address INNER JOIN city first, then join with customer. This was changed to be more like the SQL standard, so it’s only in MySQL 5.0.12 and higher that it acts this way.

My feeling is that a comma join should NEVER be used in MySQL. There is no such thing as a “comma join”, really what you mean is an “inner join” or a “left/right join” or “cross join” (or “natural join”).

I know that naysayers will say that

SELECT first_name,last_name,address
FROM customer INNER JOIN address;

actually does a CROSS JOIN (Cartesian product), so it is folly to rely only on the name of the join. And I agree. However, if you name your joins appropriately for what you want and intend them to be, it is much easier to catch mistakes.

And while we are on the topic of good SQL query techniques…..I would like to rant a bit about join criteria. (more…)

I’ve been told that using NOT EXISTS in (Oracle) SQL is a bad idea, and that a way to overcome this problem is to collect the non-matching rows with an OUTER JOIN. So I decided to check if it is true.

In order to start, here is my test case:

create table t1(id number,
   constraint t1_pk primary key(id));

  create table t2(id number);

begin
  for i in 1..100 loop
    insert into t1 values(i);
  end loop;
  commit;
end;

begin
  for i in 1..100000 loop
    insert into t2
      values(mod(i,97));
  end loop;
  commit;
end;
/

create index t2_idx on t2(id);

exec dbms_stats.gather_table_stats(USER,'T1');

exec dbms_stats.gather_table_stats(USER,'T2');

First, I checked what I’d been told, i.e. that the OUTER JOIN is more efficient than the NOT EXISTS. In order to do that, I wrote a simple SELECT and displayed the plan for both syntaxes (my database is 11.1.0.6 on Linux 32-bits). As I assumed, it’s not the case. In fact, both orders took the same plan. (more…)