Return a result even if Select is empty

Question

I have this query :

SELECT p.id, p.name, p.img, p.template_usage, t.name as tpl_name FROM platforms as p, xml_template as t WHERE p.template_usage = t.id

It returns required results, however if the t.id does not exist, the whole row is not returned. Is it possible to add some kind of parameter that says

"If t.id does not exist then tpl_name = ''" - so the row is returned, but the value of the tpl_name is empty.

Donal · Accepted Answer · 2014-08-12T17:58:02.633

4

SELECT p.id, p.name, p.img, p.template_usage, t.name as tpl_name
FROM platforms as p 
left join xml_template as t on t.id = p.template_usage

edited Aug 12 '14 at 17:58

answered Aug 12 '14 at 17:54

Donal

31,121
10
63
72

score 3 · Answer 2 · edited Jun 20 '20 at 09:12

3

Your issue stems from the fact that you're using an implicit inner join in your query. Using a left join would be more apt for your situation.

SELECT p.id, p.name, p.img, p.template_usage, t.name as tpl_name
FROM platforms as p
LEFT JOIN xml_template as t ON p.template_usage = t.id

I would suggest reading Explicit vs implicit SQL joins as well as looking up what each SQL join accomplishes.
_{(source: geekphilip.com)}

Note: As John mentioned below, MySQL does not support a FULL OUTER JOIN.

There can be quirks between DBMSs when using some joins, however I would suggest knowing what each join type is trying to accomplish. Once you know how you want to join the tables, you will just need to look up how to accomplish it within your particular database.

edited Jun 20 '20 at 09:12

Community

1
1

answered Aug 12 '14 at 18:00

Chris

788
4
11

1

you should actually try running these joins.. mysql doesn't support FULL OUTER JOIN – John Ruddell Aug 12 '14 at 18:00
you are correct John; I included the picture more as a reference for explaining SQL joins rather than purely for MySQL. I will update my answer to include your feedback. – Chris Aug 12 '14 at 18:18
Yea I would just specify when showing that that it doesn't work in MySQL :) – John Ruddell Aug 12 '14 at 18:19
not a problem! gotta give credit where credit is due! happy coding! :D – Chris Aug 12 '14 at 18:33

score 0 · Answer 3 · edited Aug 12 '14 at 18:04

0

Use a left join.

select p.id, p.name, p.img, p.template_uage, t.name as tpl_name from platforms p left outer join xml_template t on p.template_uage=t.id;

A left outer join returns all rows from the left table regardless of whether there is a match in the right table. The corresponding columns from the right table that do not match the join condition show up as nulls.

edited Aug 12 '14 at 18:04

John Ruddell

25,283
6
57
86

answered Aug 12 '14 at 17:59

Moon_Watcher

416
1
6
19

score 0 · Answer 4 · answered Aug 12 '14 at 18:01

just as a side note.. use a LEFT JOIN to combine the tables whether there is matching or not... use COALESCE() for the null values from the left join.. you can specify anything you want to show up.. in this case I just added a blank space

SELECT 
    p.id, 
    p.name, 
    p.img, 
    p.template_usage, 
    COALESCE(t.name, ' ') AS tpl_name
FROM platforms as p 
LEFT JOIN xml_template as t 
  ON t.id = p.template_usage

score 0 · Answer 5 · edited Aug 12 '14 at 18:06

0

Looks like a job for an outer join

something like

SELECT p.id, p.name, p.img, p.template_usage, t.name as tpl_name 
  FROM platforms as p RIGHT OUTER JOIN xml_template as t 
    ON p.template_usage = t.id

edited Aug 12 '14 at 18:06

Arth

12,789
5
37
69

answered Aug 12 '14 at 18:02

JSS

15
5

@JohnRuddell Lol, `RIGHT OUTER JOIN` is synonymous for `RIGHT JOIN`.. although I believe a `LEFT JOIN` is in order here. – Arth Aug 12 '14 at 18:05

Return a result even if Select is empty

5 Answers5