How to select all data from one table and records from another table matching data in first selection. All in one query

Question

I have two tables Messages and Files. The Files table references Messages through its message_id foreign key which corresponds to the Messages's primary key named message_id as well. A message entity may or may not have a file but there cannot be a file without a message.

So I want to select everything in one query. All the messages and if there is a file for a message then select a file as well. As far as I understand the resulting query should look something like this:

select * from Messages union select * from Files where message_id = Messages.message_id

unfortunately this simple query is not valid. I also tried using joins:

select * from Messages 
left outer join Files on Messages.message_id = Files.message_id 
union 
select * from Files 
left outer join Messages on Messages.message_id = Files.message_id

but it gives me only those messages which have a file. Using subqueries doesn't seem to be a solution. So how do I do this?

Answer 1

If you want data from one table and, if it exists, data from another table then why does a simple left outer join not work?

select * from Messages 
left outer join Files on 
Messages.message_id = Files.message_id

Answer 2

You want a full outer join here, which SQLite does not directly support. You may emulate it with a union, along the lines of what you have already tried:

SELECT m.*, f.*
FROM Messages m
LEFT JOIN Files f ON m.message_id = f.message_id
UNION ALL
SELECT m.*, f.*
FROM Files f
LEFT JOIN Messages m ON m.message_id = f.message_id
WHERE m.message_id IS NULL;

Answer 3

The key point of your requirement is:

A message entity may or may not have a file but there cannot be a file without a message

which is the definition of a LEFT join of Messages to Files:

SELECT * 
FROM Messages m LEFT JOIN Files f
ON f.message_id = m.message_id;