StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHow to accommodate null values in an SQL SELECT statement with a WHERE that includes an operator
text
Body
copied!<p>I can successfully avoid broken SQL statements if I am querying a row that includes a null column value with </p> <pre><code>IFNULL('column_name',''), </code></pre> <p>but when I try this with an SQL statement that contains a comparison operator, such as</p> <pre><code>"SELECT * FROM table WHERE IFNULL('pay','') <= $pay" </code></pre> <p>if pay is null, I get a "not a number error" like</p> <pre><code>ERROR: Unknown column 'NaN' in 'where clause' </code></pre> <p>As you can see from the SQL statement, there is a NaN contained within.</p> <pre><code>SELECT * FROM (SELECT DISTINCT ts.users_user_id FROM subjects s LEFT JOIN teachers_subjects ts ON s.subject_ID = ts.subject_id JOIN users_teachers ut ON ut.user_id = ts.users_user_id LEFT JOIN stations st ON st.station_id = ut.station_id LEFT JOIN majors mj ON mj.major_id = ut.major_id WHERE 1 = 1 AND IFNULL(ut.pay, 10) >= NaN AND (ut.pay <= 500 OR ut.pay IS NULL)) qualified JOIN users u ON qualified.users_user_id = u.user_id JOIN users_teachers ut ON qualified.users_user_id = ut.user_id JOIN teachers_subjects ts2 ON qualified.users_user_id = ts2.users_user_id JOIN subjects s2 ON ts2.subject_id = s2.subject_id LEFT JOIN stations st ON ut.station_id = st.station_id </code></pre> <p>I can't replace <code>pay</code> with an arbitrary number value because I am using this in a pay filter. I need the SQL statement to just return rows that include a null value for pay, all other WHERE conditions being met, of course.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload