How do the Revisions work?

The posts are stored inside the wp_posts table in the database. One of the fields is called ‘post_type’. The latest revision of a post is marked ‘post’, and all prior revisions are marked ‘revision’. So when you write your SQL query, add the clause WHERE post_type=’post’.

I thought the original was “post” and the revisions pointed to it?

The original is ‘post’ in the sense that it is the first db record created for that post, and always has the same ID. As you make revisions, WP continually updates that original record and keeps it current. It also periodically makes copies of the post record, marking them ‘revision’ and giving them unique IDs.

The only relationship between posts and revisions from a db standpoint is that the revisions store the ID of the original (and current) post record in the ‘post_parent’ field. So, if you wanted to see all revisions of post ID 22, sorted by date, you would use a query like this:

SELECT * FROM wp_posts WHERE post_type=’revision’ and post_parent=22 ORDER BY post_date DESC;

The revisions are only there as a backup. The most current version of a post is always stored in the original record. So it does no harm to purge the database of revisions, using a query like this:

DELETE FROM wp_posts WHERE post_type=’revision’;

Going back to your first question, you can retrieve all the info for the current posts simply by querying on post_type=’post’, like this:

SELECT * FROM wp_posts WHERE post_type=’post’ and post_status=’publish’ ORDER BY post_date DESC;

It will also help to be familiar with this:

https://codex.www.ads-software.com/Database_Description

By the way, the queries I gave before are for the simplest possible case (show all posts in the db, without regard to their place in the taxonomy). If you want to sort or filter the posts by category or by tags, that is more complicated.