How do I add conditional check after AND statement in SQL?

Question

I want to check if certain column exists after AND statement and if it does, add another AND statement. What would be the best way to achieve this?

For example:

SELECT 
  *

FROM
  db.table AS t

WHERE
  t.value < 100
  AND
    t.another = 'hi'

  IF (t.col IS NOT NULL, 'AND t.col = 10', '')
;

Notice the very last line. I want to make sure that t.col column itself exists (not the value of t.col but the actual column in the table) before adding another AND statement.

you want to check if the column exists or if the data in the column is null? there's no way to do the former in a select statement; you'd have to construct a [prepared statement](https://dev.mysql.com/doc/refman/8.0/en/sql-prepared-statements.html) — ysth, Sep 29 '22 at 22:01

O. Jones · Answer 1 · 2022-09-29T22:22:17.667

I think you want the rows where col is null and the ones where col is 10. You can do this.

SELECT *
  FROM db.table AS t
 WHERE t.value < 100
   AND t.another = 'hi'
   AND ( t.col IS NULL OR t.col=10 )

SQL's a declarative language, not procedural. It doesn't really have the concept of IFs in WHERE clauses. Instead you declare the filter criteria you want by writing these SQL-language Boolean expressions.

Declarative? You describe the result you want, with SQL, to your database server software. It figures out how to get it. IF is a procedural concept. You tell the machine how to get what you want, not simply what you want.

There are other ways of writing that last line. For example

   AND COALESCE(t.col, 10) = 10

basically says "the value of t.col with a default value of 10".

But writing out the WHERE clause as a pure Boolean expression makes your queries easy to read and reason about. And the optimizers in the various SQL software packages do a really good job of compiling your query into very efficient execution plans. They've been competing on this performance for decades, so the rest of us can be happy.

Don't use the expression col = NULL. Any equality expression involving NULL is always false. NULL doesn't equal anything, even itself. That's why we have col IS NULL and col IS NOT NULL syntax.

How do I add conditional check after AND statement in SQL?

1 Answers1