MySQL SELECT only not null values

by

You should use IS NOT NULL. (The comparison operators = and <> both give UNKNOWN with NULL on either side of the expression.)

SELECT * 
FROM table 
WHERE YourColumn IS NOT NULL;

Just for completeness I’ll mention that in MySQL you can also negate the null safe equality operator but this is not standard SQL.

SELECT *
FROM table 
WHERE NOT (YourColumn <=> NULL);

Edited to reflect comments. It sounds like your table may not be in first normal form in which case changing the structure may make your task easier. A couple of other ways of doing it though…

SELECT val1 AS val
FROM  your_table
WHERE val1 IS NOT NULL
UNION ALL
SELECT val2 
FROM  your_table
WHERE val2 IS NOT NULL
/*And so on for all your columns*/

The disadvantage of the above is that it scans the table multiple times once for each column. That may possibly be avoided by the below but I haven’t tested this in MySQL.

SELECT CASE idx
         WHEN 1 THEN val1
         WHEN 2 THEN val2
       END AS val
FROM   your_table
        /*CROSS JOIN*/
       JOIN (SELECT 1 AS idx
                   UNION ALL
                   SELECT 2) t
HAVING val IS NOT NULL  /*Can reference alias in Having in MySQL*/

More Related Contents:

  1. Recreating a query using INNER JOIN? [duplicate]
  2. What is better subquery vs literal on IN clause in mysql
  3. SQL query return data from multiple tables
  4. quick selection of a random row from a large table in mysql
  5. Ordering by specific field value first
  6. SELECT / GROUP BY – segments of time (10 seconds, 30 seconds, etc)
  7. Checking multiple columns for one value
  8. MySQL #1093 – You can’t specify target table ‘giveaways’ for update in FROM clause
  9. MySQL INSERT INTO … VALUES and SELECT
  10. MySQL error 1241: Operand should contain 1 column(s)
  11. How to select one row randomly taking into account a weight?
  12. quick selection of a random row from a large table in mysql
  13. Compare only day and month with date field in mysql
  14. Syntax error when using except in a query
  15. SELECT query return 1 row from each group
  16. MySQL INNER JOIN select only one row from second table
  17. MySQL Select Query – Get only first 10 characters of a value
  18. SQL query to check if a name begins and ends with a vowel
  19. How to SELECT by MAX(date)?
  20. SQL Query needed for fetch value from Two identical table but not fully identical table
  21. How to reset AUTO_INCREMENT in MySQL
  22. MySQL Query GROUP BY day / month / year
  23. How do I use on delete cascade in mysql?
  24. Adding a leading zero to some values in column in MySQL
  25. Update one MySQL table with values from another
  26. MySQL Group By and Sum total value of other column
  27. Return a default value if single row is not found
  28. MySQL – Select the last inserted row easiest way
  29. MySQL query, MAX() + GROUP BY
  30. ALTER table – adding AUTOINCREMENT in MySQL

Leave a Comment