Comparing with NULL values

by

Any comparison with NULL yields NULL. To overcome this, there are three operators you can use:

  • x IS NULL – determines whether left hand expression is NULL,
  • x IS NOT NULL – like above, but the opposite,
  • x <=> y – compares both operands for equality in a safe manner, i.e. NULL is seen as a normal value.

For your code, you might want to consider using the third option and go with the null safe comparison:

SELECT * FROM mycompare 
WHERE NOT(name <=> fname OR name <=> mname OR name <=> lname)

More Related Contents:

  1. NOT LIKE not working with SHOW [duplicate]
  2. MySQL Error 1093 – Can’t specify target table for update in FROM clause
  3. How to define a custom ORDER BY order in mySQL
  4. Comparison of full text search engine – Lucene, Sphinx, Postgresql, MySQL? [closed]
  5. How to get the number of days of difference between two dates on MySQL?
  6. MySQL Error 1153 – Got a packet bigger than ‘max_allowed_packet’ bytes
  7. conditional join in mysql
  8. How to grant all privileges to root user in MySQL 8.0
  9. MySQL Group by SUM
  10. Search for all occurrences of a string in a mysql database [duplicate]
  11. MySQL Aggregate Functions without GROUP BY clause
  12. Count days between two dates, excluding weekends (MySQL only)
  13. MySQL: How do I join same table multiple times?
  14. Difference in minutes from two time fields in MySQL
  15. Is there a way to “limit” the result with ELOQUENT ORM of Laravel?
  16. Fixing “Lock wait timeout exceeded; try restarting transaction” for a ‘stuck” Mysql table?
  17. rails + MySQL on OSX: Library not loaded: libmysqlclient.18.dylib
  18. Amazon EC2, mysql aborting start because InnoDB: mmap (x bytes) failed; errno 12
  19. Access mysql remote database from command line
  20. the MySQL service on local computer started and then stopped
  21. SQL date format convert? [dd.mm.yy to YYYY-MM-DD]
  22. Storing images in MySQL [closed]
  23. Would it help to add index to BIGINT column in MySQL?
  24. MySQL – Meaning of “PRIMARY KEY”, “UNIQUE KEY” and “KEY” when used together while creating a table
  25. GRANT with database name wildcard in MySQL?
  26. Using SUM on FLOAT data
  27. Duplicate entry for key ‘group_key’
  28. SQL query with avg and group by
  29. How can I rebuild indexes and update stats in MySQL innoDB?
  30. mysql – move rows from one table to another

Leave a Comment