LEFT JOIN does not return EXPECTED RESULT

Question

I wrote a query to update the imageSrc of an object on database : UPDATE BOXES SET BOXES.IMAGESRC = ?1 WHERE BOXES.ID = ?2 , and it actually works, because I checked on the database my table Boxes and it updated correctly the imageSrc of the Box with ID=1. (Result here)

My problem comes when I execute the following LEFT JOIN query SELECT * FROM BOXES AS A LEFT JOIN MATCHES_BOXES AS B ON A.ID=B.MATCH_ID WHERE B.MATCH_ID LIKE ?1 Because it gives me the Box with ID=1 with is OLD imageSrc (Result of LEFT JOIN)

As you can see, the BOX with ID=1 does not cointains the correct imageSrc (/resources/images/cards/blue-01.png), instead, it cointains the old imageSrc (/resources/images/celda.png)

And it does not make sense to me, because I LEFT JOINED with the table Boxes, where the Box with ID=1 already have the correct imageSrc, but returns the old.

The second query is malformed. The LEFT JOIN is silently converted into an INNER JOIN by the predicate `B.MATCH_ID LIKE ?1`. — The Impaler, Dec 30 '21 at 15:14
`SELECT * FROM BOXES AS A LEFT JOIN MATCHES_BOXES AS B ON A.ID=B.MATCH_ID AND B.MATCH_ID LIKE ?1` — Koushik Roy, Dec 30 '21 at 15:15
Next time, [please do not upload images of code/errors when asking a question.](//meta.stackoverflow.com/q/285551). Paste the result tables as formatted text into your request instead. — Thorsten Kettner, Dec 30 '21 at 15:43
@Barmar: You had mistakenly closed this as a duplicate of a mutilated outer-join request. But the error here stems from using the wrong ID in the join condition instead. I've reopened the request hence. (Well, I admit it won't help future readers much, if they don't happen to make a very similar mistake and stumble upon this request incidentally, which is quite unlikely.) — Thorsten Kettner, Dec 30 '21 at 15:49
@ThorstenKettner Yeah, I didn't read the question carefully, I was going by the comments and initial answer. — Barmar, Dec 30 '21 at 15:56

score 1 · Answer 1 · answered Dec 30 '21 at 15:23

This happens as your where clause on the left join converts the query into an inner join.

   SELECT * 
     FROM BOXES AS A 
LEFT JOIN MATCHES_BOXES AS B 
       ON A.ID=B.MATCH_ID 
    WHERE B.MATCH_ID LIKE ?1

Analyze the scenarion, if you wanted to get recrods in Boxes regardless of whether it was present in MATCHES_BOXES then the resulting rows from B will all have been NULL for the missing MATCH_ID in B(ie no_row_exists).

But since you got a WHERE B.MATCH_ID LIKE '?1' condition after the JOIN, it will negate those missing MATCH_ID as you are basically check WHERE NULL like '?1' and since NULL cannot get equated with anything, the missing recrods are not retreived in the query.

If your intention is to get all records from BOXES even if they dont exist in MATCHES_BOXES, and also those records from BOXES which exist in MATCHES_BOXES on the ID and you wish to bring them only if MATCH_ID LIKE '?1', the you would JOIN on the AND condition as follows

   SELECT * 
     FROM BOXES AS A 
LEFT JOIN MATCHES_BOXES AS B 
       ON A.ID=B.MATCH_ID 
      AND B.MATCH_ID LIKE ?1 /*use AND instead of WHERE*/

score 0 · Accepted Answer · answered Dec 30 '21 at 15:39

The outer join doesn't seem to make much sense. Anyway, you are selecting MATCHES_BOXES looking for a particular MATCH_ID (3 in your sample result). You join the BOXES table on the same column. Thus you are showing BOXES with the ID 3. MATCHES_BOXES also has a column BOXES_ID. This has nothing to do with the joined BOXES row, however. When you see the IMAGESRC for BOXES row for ID 3, it is because you are joining this row.

I suppose you simply want to join on BOXES_ID instead:

SELECT * 
FROM boxes b
JOIN matches_boxes mb ON mb.boxes_id = b.id
WHERE mb.match_id = ?1;

LEFT JOIN does not return EXPECTED RESULT

2 Answers2