I want to select columns from a table where the values of columns are NULL.
For example:
SELECT * FROM table1 WHERE column1 IS NULL;
the above query returns rows where column1 doesn't have any value.
But I want to return multiple columns(where column1,column2,....)
I want to use this in MYSQL
SELECT *
FROM table1
WHERE coalesce(column1, column2, column3) IS NULL;
You will have to enumerate all required columns. (I should confess this is a hack and should not be used in production code)
UPD
IF you want to check if at least a single column is null you should use OR:
SELECT *
FROM table1
WHERE column1 IS NULL or column2 IS NULL;
coalesce is null may fail.
COALESCE and IFNULL may fail on incompatible datatypes, for example, in T-SQL. Anyway, I should confess, that the solution smells and can be used only as a short hack in mysql console.COALESCE('qweqwe', 10) should throw an error.There is no fancy way: you have to test all columns
SELECT * FROM table1
WHERE
column1 IS NULL AND
column2 IS NULL AND
...
columnn IS NULL;
You can also use SELECT CONCAT(Column1,Column2)
or for Variables SELECT CONCAT(@Var1,@Var2):
If there is even 1 NULL the result will be NULL
Set @var1 = 'a',
@var2= NULL;
SET @AnyNullValue = (SELECT CONCAT(@Var1,@Var2));
SELECT IF(@AnyNullValue IS NULL , 'YES', 'NO'); //YES
IF(@AnyNullValue IS NULL) THEN
--your code-- ;
END IF;
