SQL Basics: How to get details from multiple tables in one query?

Question

I've used SQL for years but have never truly harnessed its potential.

For this example let's say I have two tables:

CREATE TABLE messages (
    MessageID INTEGER NOT NULL PRIMARY KEY,
    UserID INTEGER,
    Timestamp INTEGER,
    Msg TEXT);

CREATE TABLE users (
    UserID INTEGER NOT NULL PRIMARY KEY,
    UserName TEXT,
    Age INTEGER,
    Gender INTEGER,
    WebURL TEXT);

As I understand it, PRIMARY KEY basically indexes the field so that it can be used as a rapid search later on - querying based on exact values of the primary key gets results extremely quickly even in huge tables. (which also enforces that the field must be unqiue in each record.)

In my current workflow, I'd do something like

SELECT * FROM messages;

and then in code, for each message, do:

SELECT * FROM users WHERE UserID = results['UserID'];

This obviously sounds very inefficient and I know it can be done a lot better.

What I want to end up with is a result set that contains all of the fields from messages, except that instead of the UserID field, it contains all of the fields from the users table that match that given UserID.

Could someone please give me a quick primer on how this sort of thing can be accomplished?

If it matters, I'm using SQLite3 as an SQL engine, but I also would possibly want to do this on MySQL.

Thank you!

Answer 1

Not sure about the requested order, but you can adapt it.
Just JOIN the tables on UserID

SELECT MESSAGES.*, 
       USERS.USERNAME, 
       USERS.AGE, 
       USERS.GENDER, 
       USERS.WEBURL 
FROM   MESSAGES 
       JOIN USERS 
         ON USERS.USERID = MESSAGES.USERID 
ORDER  BY MESSAGES.USERID, 
          MESSAGES.TIMESTAMP