CODEWITHSUNDEEP
mysqlsql
Don P
Don P

Reputation: 63728

How to join 2 tables without an ON clause

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 ??????

Upvotes: 6

Views: 19603

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270513

You want to summarize first 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

Doing the cross join between the tables will generate a cartesian product that will mess up your sums.

Upvotes: 1

sgeddes
sgeddes

Reputation: 62851

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

Upvotes: 5

Andomar
Andomar

Reputation: 238196

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.

Upvotes: 14

Related Questions