Count two columns in a join

At first glance, the answer to this question appears obvious --

select count(table1.column1) as count_column1
     , count(table2.column2) as count_column2
  from table1
inner
  join table2
    on table2.field2 = table1.field1

However, since these counts are in an INNER JOIN, they will count column values only from the joined rows. In other words, if there are any rows of table1 that don't have a matching row in table2, then those values of column1 won't be counted. And vice versa if there are rows in table2 that don't exist in table1.

Another thing to note is that the counts will be exactly the same if both column1 and column2 are NOT NULL. Why? Because COUNT(column), like all aggregate functions except COUNT(*), ignores nulls. And, again, since it's a join, the counts will be identical. The only time they'll be different is when nulls are allowed and actually exist in one or the other column.

If, on the other hand, you're after the overall counts of these two columns from their respective tables, regardless of which rows actually meet the join conditions, then you can get those separate counts in one query like this --

select ( select count(column1)
           from table1 ) as count_column1
     , ( select count(column2)
           from table2 ) as count_column2

Notice that the outer query has no FROM clause; not all databases support this syntax. And, again, remember the difference between COUNT(column) and COUNT(*), and use COUNT(*) if you actually want to count rows instead of non-null values.