How to return different result in the same query?

Question

I'm trying to return different result in one query, in particular the query return the ranking of a particular competition using round.id field, now sometimes this table can have the field group.id valorized, if so I need to return only the ranking which have as group.id the minimum value available, so I create this design:

SELECT l.*,
t.name as team_name,
r.name AS rank_name,
r.color AS rank_color
FROM league_ranking l
LEFT JOIN team t ON l.team_id = t.id
LEFT JOIN competition_ranks r ON l.rank = r.id
INNER JOIN competition_groups g 
WHERE l.round_id = :round_id
AND l.group_id = (
  SELECT MIN(l2.group_id)
  FROM league_ranking l2
  WHERE l2.round_id = :round_id
)

this working if the ranking records have the group.id available, but if this field is NULL nothing will be returned, a little example of league_ranking table data:

| round_id | group_id | team_id
     5         3         1045
     5         3         1046
     6         NULL      1047
     6         NULL      1048

if I search as round.id 5, will be returned the first two records, but if instead I search for round.id 6, nothing will be returned. How can I structure my query to return the result also if there is no group.id associated?

Answer 1

null isn't a value, it's the lackthereof. null = null returns null, not true, so for groups without an id, this query won't work.

You can use the <=> instead of =, though, to evaluate two nulls as being equal:

SELECT l.*,
t.name as team_name,
r.name AS rank_name,
r.color AS rank_color
FROM league_ranking l
LEFT JOIN team t ON l.team_id = t.id
LEFT JOIN competition_ranks r ON l.rank = r.id
INNER JOIN competition_groups g 
WHERE l.round_id = :round_id
AND l.group_id <=> ( -- <=> used here instead of =
  SELECT MIN(l2.group_id)
  FROM league_ranking l2
  WHERE l2.round_id = :round_id
)