Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtps
 (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <pgsql-sql-owner+archive@lists.postgresql.org>)
	id 1ntEpy-0003vB-Hn
	for pgsql-sql@arkaria.postgresql.org; Mon, 23 May 2022 20:39:34 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.92)
	(envelope-from <pgsql-sql-owner+archive@lists.postgresql.org>)
	id 1ntEpx-0005ZA-02
	for pgsql-sql@arkaria.postgresql.org; Mon, 23 May 2022 20:39:33 +0000
Received: from makus.postgresql.org ([2001:4800:3e1:1::229])
	by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <fstreitzig@gmx.net>)
	id 1ntEpw-0005Z0-LJ
	for pgsql-sql@lists.postgresql.org; Mon, 23 May 2022 20:39:32 +0000
Received: from mout.gmx.net ([212.227.15.15])
	by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <fstreitzig@gmx.net>)
	id 1ntEpp-0005Sw-QV
	for pgsql-sql@lists.postgresql.org; Mon, 23 May 2022 20:39:31 +0000
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=gmx.net;
	s=badeba3b8450; t=1653338362;
	bh=0gS/Uk5Pea40HRsY65gtEMlp8vCOfWaa5Dwt31NyRKk=;
	h=X-UI-Sender-Class:Date:From:To:Cc:Subject:References:In-Reply-To;
	b=OptrZYyQA6Av0EfgqiQQmjlUujI4unxpVWNuL85HJlCAkG/57VnZ3rwM/x8hbbu6d
	 Ig9cRg+8LqPMwDiq7aIVvo6Bnhk6br2czA72AuxfsCpachokRUdCACtBIy7MaLE9cW
	 ugyydeBoNxQIaWYM9y/6K3H62C0v8wEJRWYOGL+I=
X-UI-Sender-Class: 01bb95c1-4bf8-414a-932a-4f6e2808ef9c
Received: from frastr-raspi.localdomain ([84.179.150.154]) by mail.gmx.net
 (mrgmx004 [212.227.17.190]) with ESMTPSA (Nemesis) id
 1MKsnP-1o9PJ12deH-00LCqr; Mon, 23 May 2022 22:39:22 +0200
Received: from frastr-dev (p54b3969a.dip0.t-ipconnect.de [84.179.150.154])
	(using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits)
	 key-exchange X25519 server-signature RSA-PSS (2048 bits) server-digest
 SHA256)
	(No client certificate requested)
	by frastr-raspi.localdomain (Postfix) with ESMTPSA id 57E5060242;
	Mon, 23 May 2022 22:39:21 +0200 (CEST)
Date: Mon, 23 May 2022 22:39:19 +0200
From: Frank Streitzig <fstreitzig@gmx.net>
To: kimaidou <kimaidou@gmail.com>
Cc: pgsql-sql@lists.postgresql.org
Subject: Re: Count child objects for each line of a table: LEFT JOIN, LATERAL
 JOIN or subqueries ?
Message-ID: <Yovw9wcx8n6WqP8v@frastr-dev>
References: 
 <CAMKXKO6Sam3WrzCb12yRyW+=OT_7K9zz3MfAQy4PNywuqhkGTQ@mail.gmail.com>
 <YouIuUVD9ivC03pj@frastr-dev>
 <CAMKXKO4xgawFHoVpz6hjeV-uGNf2W0NU90PkVBuCxJDLDUVmWg@mail.gmail.com>
 <CAMKXKO4UoPKACXYVWMxDAn-ZukRVD9S3TV52P93QOYHzC6AqbQ@mail.gmail.com>
 <CAMKXKO56Kc9Y32GEscw4F=mjkB9N3+aO8gVX1w9HmNC9T=1OrA@mail.gmail.com>
 <CAMKXKO67oxVv9mBBEkve9YP7Urv53oFHyWY9qthrmEDCi-5HHA@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CAMKXKO67oxVv9mBBEkve9YP7Urv53oFHyWY9qthrmEDCi-5HHA@mail.gmail.com>
X-Provags-ID: V03:K1:ZNT8nhugPTPEVhqAVoKwcrafw+n4gK+S6GW7ryhxed2HHrjhdHA
 UT4gNWdE3zJrbs9G7yxiXAHM9k6Reih562DmhdG2Dt7qPle8x5OK9NTTi4rNJgVCvNdGcGe
 +fWR9uJZqKp+W0cm5nZZ1ZkrXiml1ag7udb7njOR3XOITwamh+hB70KeKe3l7XQRmGAB7Dj
 3soh8LBaqNbGh65JhTRJg==
X-Spam-Flag: NO
X-UI-Out-Filterresults: notjunk:1;V03:K0:/Xaod/+zs30=:D5OkFUMYdp1+58FlxmaXwU
 qqEATxl8/pe3D9aW93WftVe0zxNr05+jjrmU3C/Oj/NSsZ0uC5ELboXl0ig63bE8AiyC+sQWB
 flqf00BYLYEnpLP2GKEiNObq3MJ9ZZBaE6lhZzDrVuUb8134yfTAV1W8P9H2OE7WOr0sLFOaE
 UiETUBecgW4JBelEwCq1OIsno8daDALA/uPCuK3gdaVZJFJt927u+vHBBz8Dfo6ytftouw2rR
 +zmwoT1UfCZ1r9z/ZIB6waijx0WzDzKNOGRpFwer9yvWoBQ9JO/bm/6wdp4or6ChB3tyqjqon
 ULLttrfybHopcNCeIfUI8++yPt4EYOiXnxzYT7ydu03dZMoXain8EAEEGWfwIpCvOoZkKTH6K
 /hbJinciOa7UcWCaq/lz9YtnIB0xhKLqsmFs4PcQENTBFlPDMNjbrIINtvCcPJNfmSpOK00la
 uKKFnBZaE6xmVJQSLiT7luSEgCYZa/SMVqm8Vrhbu/9HhTlUYamhAyqjmGSOFzcN2Vvf3nbxR
 smkzVf2/7DwyJFr9mCoeDVK/276KIe8gpN4EtLOg902QozvrgLb0m1rnH9mfSkXyM7J/iun6n
 5M9vfO7QDl07nNA41EXbdNb3Aiklaa6DQBFJbttDOtWhZoGH3dqiQHM3M0fgYGWRudTjhGH5v
 nueWP+HLB8UwmyHVofxaaSDYbYNd1C36/HNkFAetJdHP4N6K+yGsuEtsZQJPFKrLzGKpEgRvt
 hMXsvoh9Dc+vs62L8udubsSSZP0CwWroaYJJKPInFTjr+dZHqaA36GucXfdFP4NDUJE370z2O
 IZUVDlxk9N6jDUr3erM4ZNiyuM34Vw4Y7db3oA667hxJ7Na8dsjZD9E9CLbb/16RCvaM4YCn7
 5m+ntl9MnQBSRwivPwcIDaO646WJiGyODfd5BNhqa03P6srGtYvUbWC5fGjqdzPxROUbIcXft
 ozJEu0MyBwvuZTVq0J+1Un0RR1kkhlnFHMAOp8K98UwefyckiWwQkwSA8t8bOk3fODiNZDPwW
 zaeQLAevugD+Oa1ucFfioORq2uIkMpSi7YfsiJjxyytep4OWj5scUwb4mLhml2Jtml+CTEKwH
 VbuXE1X31P2t0s9ddBqc52Wqr9/O019MxfaNbhu3l+kXOxQPoJ37ET+9Q==
List-Id: <pgsql-sql.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-sql@lists.postgresql.org>
List-Owner: <mailto:pgsql-sql-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-sql>
Archived-At: 
 <https://www.postgresql.org/message-id/Yovw9wcx8n6WqP8v%40frastr-dev>
Precedence: bulk

Am Mon, May 23, 2022 at 04:33:16PM +0200 schrieb kimaidou:
> Here is the 4th SQL fiddle with your proposal organized with "WITH" clauses
> http://sqlfiddle.com/#!17/fe902/31/0

yes, you can do it like this.

Regards
Frank