MySQL 4.1: Finally, Subqueries are Here!

For years, the high-end database crowd (Oracle, DB2, even PostgreSQL) has looked down on MySQL because we didn't have subqueries. We had to create temporary tables, run a query, store the IDs, and then run another query. It worked, but it was ugly and hard to optimize. With the alpha release of MySQL 4.1, those days are over.

The Old Way: Temporary Tables

If you wanted to find all customers who haven't placed an order, you'd do something like this:

CREATE TEMPORARY TABLE customers_with_orders
SELECT DISTINCT customer_id FROM orders;

SELECT * FROM customers 
LEFT JOIN customers_with_orders ON customers.id = customers_with_orders.customer_id
WHERE customers_with_orders.customer_id IS NULL;

DROP TABLE customers_with_orders;

It's three steps, it's slow, and it messes up your transaction logs.

The New Way: NOT IN and EXISTS

Now, you can do it in a single, elegant statement. The optimizer can actually look at this and decide the best way to execute it.

SELECT * FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);

Or even better, using EXISTS:

SELECT * FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

Performance Warning

While subqueries are a huge leap forward, don't go crazy. In early versions of 4.1, the optimizer still struggles with some IN clauses. If you're dealing with millions of rows, sometimes a LEFT JOIN is still faster. But for 90% of your daily work, subqueries will make your life significantly easier.

MySQL is finally growing up. It's not just a "fast key-value store with a SQL parser" anymore; it's becoming a real relational database.