A Simple CROSS JOIN Example

99% of the queries I write to join tables wind up using JOIN (aka INNER JOIN) or LEFT JOIN so whenever there’s an opportunity to use one the other types, I get pretty excited 🙂. Today, that wound up being a CROSS JOIN.

Consider the following table containing charges:

DROP TABLE IF EXISTS charges;
CREATE TABLE charges (id INT PRIMARY KEY AUTO_INCREMENT, amount DECIMAL(10,2));
INSERT INTO charges (amount) VALUES (18);
INSERT INTO charges (amount) VALUES (15);
INSERT INTO charges (amount) VALUES (27);
+—-+——–+
| id | amount |
+—-+——–+
| 1 | 18.00 |
| 2 | 15.00 |
| 3 | 27.00 |
+—-+——–+

view raw
Charges.txt
hosted with ❤ by GitHub

How would you add add a column showing how much each charge represents as a percentage of the total charges?

Option 1: Using a subquery

One way to solve this is to use a subquery:

SELECT *, ROUND(amount / (SELECT SUM(amount) FROM charges) * 100) AS percent
FROM charges
+—-+——–+———+
| id | amount | percent |
+—-+——–+———+
| 1 | 18.00 | 30 |
| 2 | 15.00 | 25 |
| 3 | 27.00 | 45 |
+—-+——–+———+

view raw
gistfile1.txt
hosted with ❤ by GitHub

For each record, we divide the amount by the sum of all the amounts to get the percentage.

Option 2: Using a variable

Similar to the solution above, except here we save the sum of the amounts in a variable and then use that variable in the query:

SET @total := (SELECT SUM(amount) FROM charges);
SELECT *, ROUND(amount / @total * 100) AS percent
FROM charges;
+—-+——–+———+
| id | amount | percent |
+—-+——–+———+
| 1 | 18.00 | 30 |
| 2 | 15.00 | 25 |
| 3 | 27.00 | 45 |
+—-+——–+———+

view raw
gistfile1.txt
hosted with ❤ by GitHub

Option 3: Using CROSS JOIN

A cross join takes every row from the first table and joins it on every row in the second table. From w3resource.com:

cross-join-round.png

In this solution, we create a result set with one value (the sum of the amounts) and then cross join the charges table on it. That will add the total to each record, which we can then divide the amount by to get the percentage:

SELECT *, ROUND(amount / total * 100) AS percent
FROM charges
CROSS JOIN (SELECT SUM(amount) AS total FROM charges) t;
+—-+——–+——-+———+
| id | amount | total | percent |
+—-+——–+——-+———+
| 1 | 18.00 | 60.00 | 30 |
| 2 | 15.00 | 60.00 | 25 |
| 3 | 27.00 | 60.00 | 45 |
+—-+——–+——-+———+

view raw
gistfile1.txt
hosted with ❤ by GitHub

If we didn’t want the total column in the result, we could simply exclude it:

SELECT id, amount, ROUND(amount / total * 100) AS percent
FROM charges
CROSS JOIN (SELECT SUM(amount) AS total FROM charges) t;
+—-+——–+———+
| id | amount | percent |
+—-+——–+———+
| 1 | 18.00 | 30 |
| 2 | 15.00 | 25 |
| 3 | 27.00 | 45 |
+—-+——–+———+

view raw
gistfile1.txt
hosted with ❤ by GitHub

In this case there shouldn’t be any performance gains using the CROSS JOIN vs one of the other methods, but I find it more elegant than the subquery or variable solutions.

CROSS JOIN vs INNER JOIN

Note that CROSS JOIN and INNER JOIN do the same thing, it’s just that because we’re not joining on a specific column, the convention is to use CROSS JOIN. For example, this produces the same result as the last CROSS JOIN example:

SELECT id, amount, ROUND(amount / total * 100) AS percent
FROM charges
INNER JOIN (SELECT SUM(amount) AS total FROM charges) t;
+—-+——–+———+
| id | amount | percent |
+—-+——–+———+
| 1 | 18.00 | 30 |
| 2 | 15.00 | 25 |
| 3 | 27.00 | 45 |
+—-+——–+———+

view raw
cross-vs-inner.txt
hosted with ❤ by GitHub

And so does this:

SELECT id, amount, ROUND(amount / total * 100) AS percent
FROM charges, (SELECT SUM(amount) AS total FROM charges) t;
+—-+——–+———+
| id | amount | percent |
+—-+——–+———+
| 1 | 18.00 | 30 |
| 2 | 15.00 | 25 |
| 3 | 27.00 | 45 |
+—-+——–+———+

view raw
gistfile1.txt
hosted with ❤ by GitHub

So why use CROSS JOIN at all? Per a Stack Overflow thread:

Using CROSS JOIN vs (INNER) JOIN vs comma

The common convention is:

* Use CROSS JOIN when and only when you don’t compare columns between tables. That suggests that the lack of comparisons was intentional.
* Use (INNER) JOIN with ON when and only when you have comparisons between tables (plus possibly other comparisons).
* Don’t use comma.

Props this Stack Overflow question for the tip about using CROSS JOIN to solve this type of problem.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s