-1

Maybe I'm foolish, maybe I'm blind, but I'm only Swift developer who tries to deal with some aspects of MySQL.

I've got a simple table with several columns:

id INT NOT NULL
name VARCHAR NOT NULL
age INT ALLOW NULL
domain VARCHAR NOT NULL

So the only column 'age' may be NULL. When I do SELECT *, I receive (id, name, domain) or (id, name, age, domain) according to 'age' value.

How can I get all 4 columns in row even if age is NULL? So it may be something like: (453, John, NULL, johnDoe) or (453, John, 37, johnDoe).

To access MySQL server, I use Swift Perfect's native MySQL framework.

EDIT: Let my table have these columns also:

randomValue1 INT ALLOW NULL
randomValue2 INT NOT NULL
randomValue3 INT ALLOW NULL

So if I fetch data, it may be 7 values (if everything is set) or 6 values if smth is not set, so how will know what is set and what is not?

  • It would be useful to see the code you use to make your query – RiggsFolly Feb 23 '17 at 09:40
  • `SELECT id,name,age,domain from TABLENAME`. It's working fine. – Sagar Gangwal Feb 23 '17 at 09:40
  • Select * will give you all the 4 columns including rows with null in age. Do you mean you want to get string NULL if value is absent? If so, why? If you are doing this, you ll be converting age in all the rows to string. – Gurwinder Singh Feb 23 '17 at 09:43
  • SELECT * FROM list WHERE id = '\(id)' –  Feb 23 '17 at 09:43
  • @GurV it's an example of simple one. I've got another table, much bigger and it contains 3 columns with NULL values, so when I receive data, I need to parse it. If I use simple "count" function, it doesn't represent which columns are NULL. –  Feb 23 '17 at 09:46
  • @SagarGangwal it always returns only those values, which are not null. So an array I receive may contain 6 values, or 7, or 8. And to guess which one has the values I need is impossible. –  Feb 23 '17 at 09:49
  • Count doesnt include nulls. Use count(1). If you have further query, please provide sample data, operation you want to perform on it and expected output. – Gurwinder Singh Feb 23 '17 at 09:49
  • @GurV sorry, I meant swift's Array "count" function –  Feb 23 '17 at 09:58
  • @JohnMurcielago Who told you that It's only returns those value,which are not null ?. Have you tried once? As far as we didn't apply any where condition like `IS NULL` OR `IS NOT NULL` it will returns all required value. – Sagar Gangwal Feb 23 '17 at 10:00
  • @SagarGangwal but it doesn't! I think may be it's a particularity of Perfect framework, 'cos it receives only whose values which are set –  Feb 23 '17 at 10:03
  • See http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query – Strawberry Feb 23 '17 at 10:06

2 Answers2

0

You can use simple query for getting the result of all four columns. If "age" column does not contain any value then it will return "null" otherwise all column's value will be there in output.

SELECT id, name, age, domain FROM TABLE_NAME;
Rahul Khandelwal Systematix
  • 141
  • 10
0

If you are OK to have a default value, say -1 (or 0 or whatever), you can use coalesce(age, -1) to get that value instead of null.

select id, name, coalesce(age, -1) age, domain from t;

and handle -1 age properly in your application.

Gurwinder Singh
  • 38,557
  • 6
  • 51
  • 76