Query all columns of table1 left join and count of the table2

Question

I couldn't get this query working :

DOESN'T WORK

select 
    Region.*, count(secteur.*) count 
from  
    Region 
left join 
    secteur on secteur.region_id = Region.id

The solution I found is this but is there a better solution using joins or if this doesn't affect performance, because I have a very large dataset of about 500K rows

WORKS BUT AFRAID OF PERFORMANCE ISSUES

select 
    Region.*,
    (select count(*) 
     from Secteur 
     where Secteur.Region_id = region.id) count 
from  
    Region

@PrashantPimpale Because [it makes no difference](https://stackoverflow.com/questions/1221559/count-vs-count1-sql-server). — Ruud Helderman, Dec 01 '18 at 14:02
@RuudHelderman yes, is it identical for all cases? cause had one query which takes 1 min and 13 seconds to execute which I was using `COUNT(*)` but once I changed to `COUNT(_id)` which is int value now it's taking 10 to 15 seconds to execute! — Prashant Pimpale, Dec 01 '18 at 14:25
@PrashantPimpale Tested with cold cache or warm cache? Were the index statistics up to date? Did `_id` hold null values? Besides duration, did you measure I/O and CPU? Did you compare the query plans? What version and edition of SQL Server? I am willing to take your claim seriously, but without an [MCVE](https://stackoverflow.com/help/mcve), the claim is meaningless. — Ruud Helderman, Dec 01 '18 at 22:11

Lee Mac · Answer 1 · 2018-12-01T14:12:52.007

2

I would suggest:

select region.*, count(secteur.region_id) as count
from region left join secteur on region.id = secteur.region_id
group by region.id, region.field2, region.field3....

Note that count(table.field) will ignore nulls, whereas count(*) will include them.

Alternatively, left join on a subquery and use coalesce to avoid nulls:

select region.*, coalesce(t.c, 0) as count
from region left join 
(select region_id, count(*) as c from secteur group by region_id) t on region.id = t.region_id

edited Dec 01 '18 at 14:12

answered Dec 01 '18 at 14:01

Lee Mac

15,615
6
32
80

returns incorrect syntax near * (in the group by line) ??!! – seddik Dec 01 '18 at 14:04
Apologies, you'll need to list the fields contained in `region`, each to be grouped by. – Lee Mac Dec 01 '18 at 14:07

Mureinik · Accepted Answer · 2018-12-01T15:44:37.307

1

I'd join region on an aggregate query of secteur:

SELECT    r.*, COALESCE(s.cnt, 0)
FROM      region r
LEFT JOIN (SELECT   region_id, COUNT(*) AS cnt
           FROM     secteur
           GROUP BY region_id) s ON s.region_id = r.id

edited Dec 01 '18 at 15:44

answered Dec 01 '18 at 14:01

Mureinik

297,002
52
306
350

in term of performance, I think it is better then my working query ??!! – seddik Dec 01 '18 at 14:12
@seddik But this isn't using a `LEFT JOIN`? – Lee Mac Dec 01 '18 at 15:09
i ve added a left join to it, and its the most convenient solution in my opinion – seddik Dec 01 '18 at 15:10

score 1 · Answer 3 · answered Dec 01 '18 at 16:14

I would go with this query:

select r.*,
       (select count(*) 
        from Secteur s
        where s.Region_id = r.id
      ) as num_secteurs 
from Region r;

Then fix the performance problem by adding an index on Secteur(region_id):

create index idx_secteur_region on secteur(region_id);

mchist · Answer 4 · 2018-12-01T15:28:10.910

You make a two mistakes

First: you have try to calulate COUNT() in only one (I mean, the second) table. This doesn't will work because theCOUNT(), like an any aggregate function, calculates only for the whole set of rows, not just for any part of the set (not only just for the one or an other joined table).
In your first query, you may replace secteur. * only by asterisk, like a Region.region_id, count(*) AS count, and do not forget add Region.region_id on the GROUP BY step.
Second: You has define not only aggregate function in the query, but and other fields: select Region.*, but you don't define them in GROUP BY step. You need to add to GROUP BY statement all columns, which you has define in the SELECT step but not apply an aggregate functions to them.
Append: not, GROUP BY Region.* doesn't will work, you should to define a columns in the GROUP BY step by their actual names.

So, correct form of this will looks like a

SELECT 
    Region.col1
    ,Region.col2,
    , count(*) count 
from  Region 
left join 
    secteur on secteur.region_id = Region.id
GROUP BY Region.col1, Region.col2

Or, if you don't want to type each name of column, use window queries

SELECT
    Region.*,
    , count( * ) OVER (PARTITION BY region_id) AS count 
from  Region 
left join 
    secteur on secteur.region_id = Region.id

Query all columns of table1 left join and count of the table2

4 Answers4