How can I add a column to this union result?

Question

I have this query (which I removed some keys from for brevity's sake):

SELECT id as in_id, out_id, recipient, sender, read_flag 
  FROM received WHERE recipient=1
UNION ALL 
SELECT in_id, id AS out_id, recipient, sender, read_flag  
  FROM sent WHERE sender=1 

Which combines the results from two tables showing messages sent and received by a given user. What I'd like to do is add a column/flag to the result to distinguish which table the row belongs to so when I display them I can show a relevant icon for sent or received messages. How would I add this?

Answer 1

Just add a constant column to each query. It doesn't matter what the type is as long as it's the same in both parts. So you could use 0 and 1 or two strings, for example:

SELECT id as in_id, out_id, recipient, sender, read_flag , 'received' as source
  FROM received WHERE recipient=1
UNION ALL 
SELECT in_id, id AS out_id, recipient, sender, read_flag , 'sent' as source 
  FROM sent WHERE sender=1 

Answer 2

Just add the column in each select with a hard coded value:

SELECT id as in_id, out_id, recipient, sender, read_flag, 'received' as source_table 
  FROM received WHERE recipient=1
UNION ALL 
SELECT in_id, id AS out_id, recipient, sender, read_flag, 'sent' as source_table 
  FROM sent WHERE sender=1 

Answer 3

This will do it:

SELECT 'r' as type, id as in_id, out_id, recipient, sender, read_flag 
  FROM received WHERE recipient=1
UNION ALL 
SELECT 's' as type, in_id, id AS out_id, recipient, sender, read_flag  
  FROM sent WHERE sender=1