SQL query where WHERE has 2 options

Question

I intend to make an SQL query where WHERE has 2 situations, example:

SELECT * FROM products WHERE name = "%Explosion%" OR tag = "%Explosion%"

In this consultation I would specify that, first the consultation checks whether there is any product under the name of explosion if there were to stop there and return the result without searching tag if it also exists, but if not found in the name the query would search in tag.

I researched ways to do this and the only way that came to me is to make 2 queries. Doing the research first in name and check if anything returns and then do in tag if name return nothing.

Perhaps using the Cláusula join along with the Cláusula outer should be able to but could not understand the use of Outer well to develop this correct query.

Someone would have an idea to do this in just 1 consultation without having to juggle with 2?

Answer 1

I don’t see a way to do it without using Join or subquery. Here is a solution using the EXISTS clause.

SELECT * 
  FROM products 
 WHERE name = "%explosion%"      -- Devolve os produtos com nome = '%explosion%'
    OR (                         -- Devolve os produtos com tag = '%explosion' apenas se não existir qualquer produto com nome = '%explosion%'
           tag = "%explosion%" 
       AND NOT EXISTS ( SELECT 1 FROM products WHERE name = '%explosion%' )
    )

Did not indicate which DBMS, but the proposed solution is portable and should work in any DBMS implementing the standard.

I didn’t know if the name and the tag of the product are really '%Xplosion%' or if you want to find the records that contain that word. If that is the purpose, you should exchange the = for LIKE.