How To Get 35 Records FROM 1 — Or Why You Must Declare Your JOIN Type

The FROM clause in MySql is completed first and it provides the table from which other operations will be run. Even with that essential description, some constructions of the FROM can be difficult to understand and provide you with wildly unexpected results.

Those unexpected results come from the fact that the tables in the FROM clause are combined into one table for querying whether you specify the type of JOIN or not. What’s worse is the default JOIN is a CROSS JOIN (aka Cartesian Join). Which has undoubtedly been the source of many painful, late-nights finding bugs.

Let’s create a schema and a few tables for demonstration:

create schema testing;

use testing;

create table `table1` 
(
	`id` int unsigned not null auto_increment primary key,
    `value` varchar(255) not null
);


create table `table2` 
(
	`id` int unsigned not null auto_increment primary key,
    `value` varchar(255) not null
);

create table `table3` 
(
	`id` int unsigned not null auto_increment primary key,
    `value` varchar(255) not null
);

Next let’s insert some data:

INSERT INTO `table1` (`value`)
    VALUES (1),(2),(3),(4),(5);

INSERT INTO `table2` (`value`)
    VALUES (6),(7),(8),(9),(10);

INSERT INTO `table3` (`value`)
    VALUES (11),(12),(13),(14),(15),(16),(17);

Let’s count some numbers

Given the following query, what do you think the count will be? There is only a single 1 in all those tables right? Not with a CROSS JOIN. You get 35 records. Ya. Read’em and weep.

SELECT count(testing.table1.id) as count FROM testing.table1, testing.table2, testing.table3 WHERE testing.table1.id = 1;

You think that has the potential to cause errors? As someone without much experience with SQL, would you expect that result given the above FROM syntax?

Needless to say, it is always a good idea to explicitly declare the JOIN type.

I found this article to be a good overview of Cartesian Joins.

Written on January 4, 2016