CODEWITHSUNDEEP
postgresql
mahan
mahan

Reputation: 14975

Select some columns of two tables and FULL OUTER JOIN them

I have two tables that have only id in common. I want to FULL OUTER JOIN them.

SELECT *
FROM message m
FULL OUTER JOIN
  (SELECT id, result, start, finish, message
   FROM  message_execution) ex ON ex.id = m.id
WHERE  m.id = '1626'

It seems that the above statement work. However, I need only a few columns of the first table, not all of them. I have tried to specify the columns' name, but it did not work.

How can I select the columns that I need?

Upvotes: 1

Views: 1228

Answers (1)

user330315
user330315

Reputation:

Don't use select * - select the columns you want:

select m.col_1, m.col_2, 
       ex.*
from message m 
  full outer join (
    select id, result, start, finish, message
    from message_execution
  ) ex on ex.id = m.id
where m.id = 1626;

Or you can simplify that to:

select m.col_1, m.col_2, 
       ex.id, ex.result, ex.start, ex.finish, ex.message
from message m 
  full outer join message_execution ex on ex.id = m.id
where m.id = 1626;

If id is defined as a number, you shouldn't compare it to a string constant. '1626' is a string constant, 1626 is a number.

Note that you where condition on the message table essentially turns the full join into a left join, so the above is equivalent to:

select m.col_1, m.col_2, 
       ex.id, ex.result, ex.start, ex.finish, ex.message
from message m 
  left outer join message_execution ex on ex.id = m.id
where m.id = 1626;

Upvotes: 3

Related Questions