mysql SELECT and summarize if column not null

Question

I don't know if this is possible with mySQL but I want to return a true|false value if a column is not null.

Suppose you have this data:

 ------------------------------------
| id | path        | tags            |
 ------------------------------------
| 1  | img-001.jpg | cats,animals    |
| 2  | img-002.jpg | baby,family,boy |
| 3  | img-003.jpg | null            |
| 4  | img-004.jpg | cars            |
 ------------------------------------

I'd like to SELECT to get a results array something like:

{id:1, path:img-001.jpg, hasTag: true},
{id:2, path:img-002.jpg, hasTag: true},
{id:3, path:img-003.jpg, hasTag: false},
{id:4, path:img-004.jpg, hasTag: true}

Can mySQL do that for me without needing the application to iterate over the data?

Admittedly I'm more familiar with T-SQL, but from a quick search it seems like MySQL has a similar CASE syntax: SELECT id, path, CASE WHEN tags IS NOT NULL THEN true ELSE false END AS hasTag. [Documentation] — Tyler Roper
– Tyler Roper, Commented Jun 7, 2019 at 3:21
Like @TylerRoper mentioned, MySQL does support CASE WHEN ... END CASE. Official documentation here. — manish
– manish, Commented Jun 7, 2019 at 3:28

Dennis · Accepted Answer · 2019-06-07 03:46:45Z

2

I think this sort of ternary if statement might work:

SELECT
    id
    , path
    , IF(tags is null, FALSE, TRUE) as hasTag
FROM
    ...
;

edited Jun 7, 2019 at 3:46

answered Jun 7, 2019 at 3:26

Dennis

2,3148 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Vikash Pathak · Accepted Answer · 2019-06-07 03:29:52Z

0

You can try IFNULL() with select. Like.

SELECT id, path, (case when (IFNULL(tags, false) = false) then false else true END) as hasTag from tableA ;

Change the tableA with your actual table name.

answered Jun 7, 2019 at 3:29

Vikash Pathak

3,6321 gold badge22 silver badges33 bronze badges

Comments

Rubin Porwal · Accepted Answer · 2019-06-07 05:49:24Z

0

According to documentation of MYSQL

The ISNULL() function returns 1 or 0 depending on whether an expression is NULL

According to description as mentioned above as a solution to it,please try executing following SQL select statement.

SELECT id,path,IF(ISNULL(tags),false,true) as hasTag FROM `table`

answered Jun 7, 2019 at 5:49

Rubin Porwal

3,8451 gold badge27 silver badges27 bronze badges

Collectives™ on Stack Overflow

mysql SELECT and summarize if column not null

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related