how to find number of records in both table using join. i have two tables table1 and table2 with same structure.

table1

table2

Output should be like this.

CodePudding user response：

SELECT DISTINCT id, (
  SELECT COUNT(*) FROM table1 AS table1_2 WHERE table1_2.id=table1.id
) AS "table1.itemcount", (
  SELECT COUNT(*) FROM table2 AS table2_2 WHERE table2_2.id=table1.id
) AS "table2.itemcount"
FROM table1;

CodePudding user response：

Assuming that each id is guaranteed to exist in both tables, the following would work

select 
    t1.id, 
    count(distinct t1.item) t1count, 
    count(distinct t2.item) t2count 
from t1 
join t2 on t1.id = t2.id
group by 1;

But if that is not guaranteed then we'll have to use full outer join to get unique ids from both tables

select 
    coalesce(t1.id, t2.id) id, 
    count(distinct t1.item) t1count, 
    count(distinct t2.item) t2count 
from t1 
full outer join t2 on t1.id = t2.id
group by 1;

We're using coalesce here as well for id because if it only exists in t2, t1.id would result in null.

@DeeStark's answer also works if ids are guaranteed to be in both tables but it's quite inefficient because count is essentially run twice for every distinct id in the table. Here's the fiddle where you can test out different approaches. I've prefixed each query with explain which shows the cost

Hope this helps