NATURAL JOIN works with LIMIT and OFFSET
postgres=# CREATE TABLE "editions" (
postgres(# "isbn" text NOT NULL,
postgres(# "book_id" integer,
postgres(# "edition" integer,
postgres(# "publisher_id" integer,
postgres(# "publication" date,
postgres(# "type" character(1),
postgres(# CONSTRAINT "integrity" CHECK (((book_id NOTNULL) AND (edition NOTNULL))),
postgres(# Constraint "pkey" Primary Key ("isbn")
postgres(# );
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pkey" for table "editions"
CREATE TABLE
postgres=#
postgres=# insert into editions values('039480001X', 1608, 1, 59, '1957-03-01', 'h');
INSERT 0 1
postgres=# insert into editions values('0451160916', 7808, 1, 75, '1981-08-01', 'p');
INSERT 0 1
postgres=# insert into editions values('0394800753', 1590, 1, 59, '1949-03-01', 'p');
INSERT 0 1
postgres=# insert into editions values('0451198492', 4267, 3, 101, '1999-10-01', 'h');
INSERT 0 1
postgres=# insert into editions values('0823015505', 2038, 1, 62, '1958-01-01', 'p');
INSERT 0 1
postgres=# insert into editions values('0596000855', 41473, 2, 113, '2001-03-01', 'p');
INSERT 0 1
postgres=#
postgres=# select * from editions;
isbn | book_id | edition | publisher_id | publication | type
------------+---------+---------+--------------+-------------+------
039480001X | 1608 | 1 | 59 | 1957-03-01 | h
0451160916 | 7808 | 1 | 75 | 1981-08-01 | p
0394800753 | 1590 | 1 | 59 | 1949-03-01 | p
0451198492 | 4267 | 3 | 101 | 1999-10-01 | h
0823015505 | 2038 | 1 | 62 | 1958-01-01 | p
0596000855 | 41473 | 2 | 113 | 2001-03-01 | p
(6 rows)
postgres=#
postgres=#
postgres=# CREATE TABLE "books" (
postgres(# "id" integer NOT NULL,
postgres(# "title" text NOT NULL,
postgres(# "author_id" integer,
postgres(# "subject_id" integer,
postgres(# Constraint "books_id_pkey" Primary Key ("id")
postgres(# );
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "books_id_pkey" for table "books"
CREATE TABLE
postgres=#
postgres=#
postgres=# insert into books values (1590, 'Java', 4156, 9);
INSERT 0 1
postgres=# insert into books values (7808, 'Javascript', 1866, 15);
INSERT 0 1
postgres=# insert into books values (1608, 'Perl Cookbook', 7806, 4);
INSERT 0 1
postgres=# insert into books values (41473, 'Practical PostgreSQL', 1212, 4);
INSERT 0 1
postgres=#
postgres=# select * from books;
id | title | author_id | subject_id
-------+----------------------+-----------+------------
1590 | Java | 4156 | 9
7808 | Javascript | 1866 | 15
1608 | Perl Cookbook | 7806 | 4
41473 | Practical PostgreSQL | 1212 | 4
(4 rows)
postgres=#
postgres=# SELECT isbn, title, publication
postgres-# FROM editions NATURAL JOIN books AS b (book_id)
postgres-# ORDER BY publication DESC
postgres-# LIMIT 5
postgres-# OFFSET 2;
isbn | title | publication
------------+---------------+-------------
039480001X | Perl Cookbook | 1957-03-01
0394800753 | Java | 1949-03-01
(2 rows)
postgres=#
postgres=#
postgres=#
postgres=# drop table books cascade;
DROP TABLE
postgres=# drop table editions cascade;
DROP TABLE
postgres=#
Related examples in the same category