How to join 2 tables without an ON clause

Question

I want to get the SUM(column_a) from two different tables, and get their difference. I am using MySQL.

Table A's sum = 1234

Table B's sum = 4001

I'm not sure what to put in my ON clause:

SELECT 
  SUM(a.column1) AS table_a_sum,
  SUM(b.column1) AS table_b_sum,
  SUM(a.column1) - SUM(b.column1) AS difference
FROM table_a a
JOIN table_b b
ON ??????

Answer 1

A join without condition is a cross join. A cross join repeats each row for the left hand table for each row in the right hand table:

FROM table_a a
CROSS JOIN table_b b

Note that in MySQL, cross join / join / inner join are identical. So you could write:

FROM table_a a
JOIN table_b b

As long as you omit the on clause, this will work as a cross join.

If you'd like to sum two columns from two tables, a cross join would not work because it repeats rows. You'd get highly inflated numbers. For sums, a better approach uses subqueries, per @sgeddes answer.

Answer 2

Here's one option using subqueries -- there are several ways to do this:

SELECT 
  table_a_sum,
  table_b_sum,
  table_a_sum - table_b_sum AS difference
FROM 
    (SELECT SUM(column1) table_a_sum FROM table_a) a,
    (SELECT SUM(column1) table_b_sum FROM table_b) b