How to return Null instead of nothing in Postgres?

Question

I am writing a postgres query where I ask to return each order for each user. In case of the user hasn't a order I would like to return Null instead of no return.

My current query:

Select Order.user_id, 
       User.name, 
       Order.id, 
       Order.value, 
       Order.item
FROM Order
       Left Join User
       On Order.user_id = User.id

it returns each user order but in case of user with no order, nothing is returned. How I can return Null (or any string) instead of no return? Thank you so much.

Use right instead of left if you want all rows from the rightmost table (users) — Abelisto
– Abelisto, Commented Jun 15, 2021 at 21:55

Gordon Linoff · Accepted Answer · 2021-06-15 21:53:22Z

3

You want a left join, but you want user first:

SELECT u.id as user_id, u.name, o.id, o.value, o.item
FROM User u LEFT JOIN
     Order o
     ON o.user_id = u.id;

I also introduced table aliases so the query is easier to write and to read. And . . . change o.user_id to u.id in the SELECT so it has a value when there are no orders.

answered Jun 15, 2021 at 21:53

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to return Null instead of nothing in Postgres?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related