Postgres 11

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

create table it191211a (
  id int not null primary key
);

create table it191211b (
  id int not null primary key
);

insert into it191211a values
(1),(2),(3),(4),(5);

insert into it191211b
select id + 2
from it191211a;

select a.id
from it191211b b , it191211a a
where a.id = b.id;
5 rows affected

5 rows affected

id

3

4

5

--=以in取代 或是 like
select a.id
from it191211b b , it191211a a
where a.id in (b.id);

id

3

4

5

--not + <> 
select a.id
from it191211b b , it191211a a
where not ( a.id <> (b.id)  );

id

3

4

5

--union + in : 模擬inner join
select id
from it191211b
where id in (select id from it191211a)
union
select id
from it191211a
where id in (select id from it191211b)

id

5

4

3

--除法 = 1
select a.id
from it191211b b , it191211a a
where a.id * 1.00 / b.id * 1.00 = 1;

id

3

4

5

--innert join + using
select a.id
from it191211b b 
inner join it191211a a using (id)

id

3

4

5

--INTERSECT 交集
SELECT id FROM it191211a 
INTERSECT
SELECT id FROM it191211b 

id

5

4

3