如何在没有ON子句的情况下联接2个表 [英] How to join 2 tables without an ON clause

问题描述

我想从两个不同的表中获取SUM(column_a)，并获得它们的区别.我正在使用MySQL.

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

我不确定要在我的ON子句中添加什么:

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

推荐答案

无条件的联接是交叉联接.交叉联接为左侧表的每一行重复右侧表的每一行:

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

请注意，在MySQL中，cross join/join/inner join相同.所以你可以这样写:

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

FROM table_a a
JOIN table_b b

只要省略on子句，它就可以作为交叉联接.

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

如果您想对两个表中的两列求和，则交叉联接将不起作用，因为它会重复行.您会得到很高的数字.对于总和，更好的方法是根据@sgeddes答案使用子查询.

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.

这篇关于如何在没有ON子句的情况下联接2个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！