How to join 2 tables without an ON clause

Question

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

Table A sum = 1234

Table B sum = 4001

I'm not sure what to add to 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 a condition is a cross join. Cross join repeats each row for the left table for each row in the right table:

 FROM table_a a CROSS JOIN table_b b 

Note that in MySQL cross join / join / inner join identical. Therefore you can 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 want to sum two columns from two tables, cross join won't work because it repeats rows. You will get very high numbers. For sums, the best approach uses subqueries, @sgeddes answers.

Answer 2

Here is one parameter 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 

Answer 3

First you want to sum and then do the calculations:

 select a.suma, b.sumb, a.suma - b.sumb from (select sum(a.column1) as suma from tablea) a cross join (select sum(b.column1) as sumb from tableb) b 

Performing a cross join between the tables will create a Cartesian product that spoils your amounts.