-2

I'm building library which generates SQL queries and found case which i don't understand why it is happening so. I agree that in general you should filter out by "where" and not "on" yet ON allows to filter out for LEFT join. Though for RIGHT join i get results that do not match ON request. I wonder why RIGHT join works this way.

Users (table name _user):

enter image description here

Invoices (table name _invoice):

enter image description here

Query:


 SELECT
    _user.id AS userId,
    _user.name AS userName,
    _user.state AS userState,
    _invoice.id AS invoiceId,
    _invoice.userId AS invoiceUserId,
    _invoice. `state` AS invoiceState
FROM
    _user
    RIGHT JOIN _invoice  on _invoice.state = 'pending'

Response:

enter image description here

Question:

Would be best to get step-by-step execution for this query-result to understand how exactly it happen.

Lukas Liesis
  • 24,652
  • 10
  • 111
  • 109
  • 1
    That's just how a RIGHT JOIN works, it will return rows from right side table even without a matching left side table row. – jarlh Sep 06 '21 at 14:00
  • so for RIGHT join can't use ON to filter out? I've been using ON as filter for left joins for quite few times successfully – Lukas Liesis Sep 06 '21 at 14:01
  • It's symmetric. LEFT JOIN returns rows from left side table even without a matching right side table row. – jarlh Sep 06 '21 at 14:02
  • What makes you think that it should be otherwise? – Nico Haase Sep 06 '21 at 14:03
  • @NicoHaase because LEFT join works allows to filter by ON condition, i wonder why RIGHT join does not. I understand that you should use where clause and join via user id yet i don't know why RIGHT join does not allow to filter out just like LEFT does – Lukas Liesis Sep 06 '21 at 14:04
  • @NicoHaase I totally agree, yet i'm building lib which builds JOIN queries and i want to find out use case for such and to catch edge cases upfront. – Lukas Liesis Sep 06 '21 at 14:07
  • Why not use a `WHERE` clause to filter out the rows you don't want to receive? – Nico Haase Sep 06 '21 at 14:08
  • If you use WHERE to filter out rows, watch out for turning the outer join unintentionally into an inner join. – jarlh Sep 06 '21 at 14:10
  • @jarlh updated question to clarify it. I agree that one should use where clause. Yet i want to find out *why* right join is returning results that do not follow ON case. – Lukas Liesis Sep 06 '21 at 14:12
  • Right outer joins are often very hard to read, and the best thing you can do with them is to forget they exist at all :-) Really, it is usually considered bad style using right outer joins. `FROM _user RIGHT JOIN _invoice on _invoice.state = 'pending'` is a strange example, because the join condition doesn't relate the tables. A better example is `FROM a RIGHT JOIN b ON a.aa = b.bb` which is exactly the same as `FROM b LEFT JOIN a ON a.aa = b.bb`. – Thorsten Kettner Sep 06 '21 at 14:41
  • @ThorstenKettner through app of 1000 endpoints and 30 services i would use right join once in some unique edge case yet it is there and sometimes it is useful. I totally agree with all the comments here but i do abstract layer which generates SQL and i want to support all main joins: left, right and inner. So trying to find the cases like this. I wonder why SQL allows join from sample which i can't think of real world use case. Is it ever the way to go. Feels like no, but trying to explore. For now i won't allow such case in my app and will wait if anyone will need it ever. – Lukas Liesis Sep 07 '21 at 06:44

1 Answers1

3

A right join keeps all rows from the second table regardless of whether the on clause evaluates to "true", "false", or NULL.

Presumably, though, you want a valid join condition. My guess is you want a list of all users and any pending invoices. If so, the correct logic would be:

SELECT . . .
FROM _user u LEFT JOIN
     _invoice i
     ON i.userid = u.id AND i.state = 'pending';

At the very least, this produces a result that seems usable.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • yes, this query is not real-world use, yet trying to understand the very detail on why this is different from left join. While with left join it's possible to use ON statement as filter, do you know the reason on why RIGHT join works this way? – Lukas Liesis Sep 06 '21 at 14:03
  • 1
    @LukasLiesis . . . They work *exactly* the same way. Try the corresponding `LEFT JOIN` with a filter on the *first* table. – Gordon Linoff Sep 06 '21 at 14:30
  • That's all i needed, thanks Gordon :) the *first* table is the answer i was looking for, it's all clear now – Lukas Liesis Sep 07 '21 at 06:39