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 emp(empid INT, name text, dept_id int, salary int);
Insert into emp values(1,'Rob', 1, 100);
Insert into emp values(2,'Mark', 1, 300);
Insert into emp values(3,'John', 2, 100);
Insert into emp values(4,'Mary', 2, 300);
Insert into emp values(5,'Bill', 3, 700);
Insert into emp values(6,'Jose', 6, 400);

create table department(deptid INT, name text);
Insert into department values(1,'IT');
Insert into department values(2,'Accounts');
Insert into department values(3,'Security');
Insert into department values(4,'HR');
Insert into department values(5,'R&D');

CREATE TABLE

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

CREATE TABLE

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

Select * from emp;

empid
name
dept_id
salary

1
Rob
1
100

2
Mark
1
300

3
John
2
100

4
Mary
2
300

5
Bill
3
700

6
Jose
6
400

SELECT 6

Select * from department;

deptid
name

1
IT

2
Accounts

3
Security

4
HR

5
R&D

SELECT 5

-- INNER JOIN

Select a.empid, a.name, b.name as dept_name
FROM emp a
JOIN department b
ON a.dept_id = b.deptid
;

empid
name
dept_name

1
Rob
IT

2
Mark
IT

3
John
Accounts

4
Mary
Accounts

5
Bill
Security

SELECT 5

-- LEFT JOIN 

Select a.empid, a.name, b.name as dept_name
FROM emp a
LEFT JOIN department b
ON a.dept_id = b.deptid
;

empid
name
dept_name

1
Rob
IT

2
Mark
IT

3
John
Accounts

4
Mary
Accounts

5
Bill
Security

6
Jose
null

SELECT 6

empid	name	dept_name
1	Rob	IT
2	Mark	IT
3	John	Accounts
4	Mary	Accounts
5	Bill	Security
6	Jose	null