How to find Null values without specifying attribute (Mariadb)

Asked

Viewed 1,368 times

0

I would like to do a search to find NULL values across the table without having to determine which of the attributes might contain NULL.

NOTE: By ALL table I mean that somewhere in it you have null, not that the table should be composed entirely of null elements. OK?

Thanks in advance

  • Lucas, please specify which database manager you are using, as there are different ways to do it in each of them. For example: Oracle, Mysql, Postgresql, Sqlite, etc...

  • Ok sorry, I am using Mariadb, which is based on Mysql

1 answer

2

Lucas, to perform the procedure you want a bit of manual work is required (or a stored Procedure, which escapes some of my knowledge in Mysql).

To do so you can follow the following steps:

1) Run the following select by changing the table name (your table) to find out which columns can receive null values in the desired table.

SELECT GROUP_CONCAT(column_name) FROM information_schema.columns WHERE table_name = 'sua_tabela' AND IS_NULLABLE = 'YES';

Once done, replace "columns..." with the result of the first select and change the name of "sua_table" again. You now need to add an OR (OR) condition for each column you select, followed by the IS NULL condition, as an example below:

SELECT colunas... FROM sua_tabela
WHERE coluna1 IS NULL 
OR coluna2 IS NULL
or colunaN... IS NULL

It is not a difficult job, but the more columns there are and the more repetitive this work will be more tiringso if it is necessary to perform this procedure routinely I advise you to study stored procedures of Mysql so that you can implement this routine that I passed to you in an automated way.

I hope I helped you.

  • I understood what you mean, I really needed a function that would return the null values found randomly in the table with no specific column definition. Then I will see a little more about stored procedures to remedy my need. Thank you

Browser other questions tagged

You are not signed in. Login or sign up in order to post.