CODEWITHSUNDEEP
mysql
jearca
jearca

Reputation: 235

MySql Always return records in select statement

Hello im working with leftjoin in Mysql and im looking a way for return records for example i have it,

SELECT table1.value
FROM table1
LEFT OUTER JOIN table2
ON `table1`.`id`=`table2`.`id`
where `table1`.`id`='this value no exist'

this return nothing, but i want a null or "" ,

like it

+--------+
| table1 |
+--------+
| null   |
+--------+
|        |
+--------+

Upvotes: 0

Views: 62

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271131

One method is to use aggregation to get one row with all possible values. If there are none, then the value is NULL:

SELECT GROUP_CONCAT(table1.value)
FROM table1 LEFT OUTER JOIN
     table2
     ON `table1`.`id` = `table2`.`id`
where `table1`.`id` = 'this value no exist';

If you are expecting only one value, then this will always return one row either with that value or NULL.

The LEFT OUTER JOIN is utterly useless in this query, unless you are looking for duplicate matches (which I doubt). It is used neither for filtering nor for fetching columns.

Because the LEFT OUTER JOIN isn't needed, you can also do what you want as:

SELECT table1.value
FROM table1
WHERE table1.id = 'this value no exist'
UNION ALL
SELECT NULL
FROM table1
WHERE NOT EXISTS (SELECT 1 FROM table1 WHERE table1.id = 'this value no exist');

Note that the LEFT OUTER JOIN is superfluous in your query.

Upvotes: 2

trincot
trincot

Reputation: 351369

Use an aggregate function, such as MAX:

SELECT MAX(table1.value)
...etc.

This is guaranteed to return exactly 1 row always.

Upvotes: 0

Related Questions