Message-ID: <40c19c0217025001681c78a7b65f30bc02ea57fc.camel@cybertec.at>
Subject: Re: Functions and Indexes
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Moreno Andreo <moreno.andreo@evolu-s.it>, 
	pgsql-general@lists.postgresql.org
Date: Tue, 19 Nov 2024 18:44:03 +0100
In-Reply-To: <92aa9401-e44d-4a45-9851-0617935e06a2@evolu-s.it>
References: <2e36a2fe-b30f-46a5-937f-3b7d94e75cb6@evolu-s.it>
	 <8dfd0bd5-87e5-4de7-9f60-8a680a32bc11@evolu-s.it>
	 <61cb94a962667788c9c09107fa9937300e54d3cd.camel@cybertec.at>
	 <14fa809c-a2b7-4d36-9382-d08a5df4718a@evolu-s.it>
	 <c58ac11e7fe0c11d84b3e5fb3244a434d8d87509.camel@cybertec.at>
	 <92aa9401-e44d-4a45-9851-0617935e06a2@evolu-s.it>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.52.4 (3.52.4-2.fc40) 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/40c19c0217025001681c78a7b65f30bc02ea57fc.camel%40cybertec.at>
Precedence: bulk

On Tue, 2024-11-19 at 14:30 +0100, Moreno Andreo wrote:
> Inhttps://www.cybertec-postgresql.com/en/join-strategies-and-performance-=
in-postgresql/
> =C2=A0you say=20
> =C2=A0"Note that for inner joins there is no distinction between the join=
 condition and the=C2=A0WHERE=C2=A0condition, but that doesn't hold for out=
er joins."
> =C2=A0What do you mean?

CREATE TABLE a (id integer);

INSERT INTO a VALUES (1), (2), (3);

CREATE TABLE b (id integer);

INSERT INTO b VALUES (1), (2), (4);

SELECT * FROM a JOIN b ON a.id =3D b.id AND b.id < 2;
 id =E2=94=82 id=20
=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=AA=E2=95=90=E2=95=90=E2=95=90=E2=
=95=90
  1 =E2=94=82  1
(1 row)

SELECT * FROM a JOIN b ON a.id =3D b.id WHERE b.id < 2;
 id =E2=94=82 id=20
=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=AA=E2=95=90=E2=95=90=E2=95=90=E2=
=95=90
  1 =E2=94=82  1
(1 row)

SELECT * FROM a LEFT JOIN b ON a.id =3D b.id AND b.id < 2;
 id =E2=94=82 id=20
=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=AA=E2=95=90=E2=95=90=E2=95=90=E2=
=95=90
  1 =E2=94=82  1
  2 =E2=94=82  =E2=88=85
  3 =E2=94=82  =E2=88=85
(3 rows)

SELECT * FROM a LEFT JOIN b ON a.id =3D b.id WHERE b.id < 2;
 id =E2=94=82 id=20
=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=AA=E2=95=90=E2=95=90=E2=95=90=E2=
=95=90
  1 =E2=94=82  1
(1 row)

Yours,
Laurenz Albe