0

Lets'suppose 2 (simplified) tables, user and house :

     id | firstname
     1  | Charles
     2  | Frank
     3  | Mark

user_id | city
    1   | London
    3   | Paris

select firstname, house.city
from user 
left join house on user.id = house.user_id;

the column city is declared "DEFAULT NULL"; the result is :

firstname | city
  Charles | London
  Frank   | NULL
  Mark    | Paris

This result is stored in an other table

In this table, later, I would want to test "city is null" but I have to test "city = 'NULL' "

So how can I do to force the left join to set real a null value when there's no match ?

Minstrel
  • 369
  • 1
  • 2
  • 14
  • 1
    Do you mean your data uses a literal `'NULL'` string rather than an actual `NULL`? – Álvaro González Feb 08 '16 at 10:47
  • 1
    How do you store this in the other table? The SELECT itself most certainly returns proper NULL (and not some string). It must have been lost somewhere after that. – Thilo Feb 08 '16 at 10:50
  • ...sorry, I read your 2nd last line totally the wrong way around... – underscore_d Feb 08 '16 at 10:54
  • Try `where city is NULL` instead `city='NULL'` – Abhishek Ginani Feb 08 '16 at 10:57
  • @Code-Monk I thought the same thing originally, but it seems the opposite: that's what they're trying to do, but _something_ is converting the real `null` into the string `'NULL'. – underscore_d Feb 08 '16 at 10:59
  • @underscore_d: There is trick to do that, but may be people not like .We can try something `where if(city is null,'NULL','')='NULL'`. If OP only wants to check only string 'NULL' :-) – Abhishek Ginani Feb 08 '16 at 11:03
  • I've got several other native tables (ie not resulting from a join), and when I make a select under the shell for example, you see the NULL word in some columns, but I can use the test "where columnNme is null', that works, 'where columnName = 'NULL' does not work. It's the opposite with the join-resulting table, and I see the term NULL under the shell too ! – Minstrel Feb 08 '16 at 11:03
  • "This result is stored in an other table" Provide the definition of that table. – underscore_d Feb 08 '16 at 11:07
  • @Code-Monk Did you mean `IIF()`? I've not seen any dialect where normal `if()` works in this way: `if(city is null,'NULL','')` Anyway, that's not the problem the OP has; the string `'NULL'` shouldn't be appearing at all. – underscore_d Feb 08 '16 at 11:12
  • @underscore_d: `if(city is null,'NULL','')` this will convert real null to string null in query. – Abhishek Ginani Feb 08 '16 at 11:15
  • Indeed, MySQL overloads `if()` in that way. Maybe I do learn something new every day ;-) – underscore_d Feb 08 '16 at 11:16
  • Check this : http://stackoverflow.com/questions/8444570/using-an-if-statement-in-a-mysql-select-query – Abhishek Ginani Feb 08 '16 at 11:16
  • LOL I don't want to use any if() statement, Iknow there's always any workaround with a piece of code. I just want to understand why there are two types of null values that we have to test 2 different ways, "is nul" or "= 'NULL' ", and having the same display with a select, one from native tables, others from joined tables. The great solution would be to set the unmatched column in the join to the native null value, to avoid asking the good question at each time. – Minstrel Feb 08 '16 at 13:15
  • In fact, I think you can only test with "is null" for native tables. As soon as a table is a result of a query (join, load, etc...), and even if a column is declared DEFAULT NULL, the value takes the string "NULL" if column is a string type or 0 if numeric. – Minstrel Feb 10 '16 at 15:45
  • @Minstrel I can't believe this without a source as it sounds preposterous. If that were true, it would go completely against basic RDBMS principles (and rule out MySQL and all relatives from my next project). I really feel the problem must be elsewhere in your workflow. This answer, for instance, does not suggest any such role of `default null`: http://stackoverflow.com/a/25968248/2757035 – underscore_d Feb 11 '16 at 11:22
  • thanks u_d, if you have time please do the test : declare a column as varchar(x) default null , load the table as you want (with some null values as results of a left join for ex), you'll always obtain the "NULL" string in it, not a nul value. – Minstrel Feb 11 '16 at 16:55

0 Answers0