If first condition is true then stop fetching from the other conditions in where clause of a SQL query

Question

Query:

where  (table1.subject_1 like '%TEST1%'  OR 
        table1.subject_1 like '%TEST2%'  OR 
        table1.subject_1 like '%TEST3%'  OR 
        table1.subject_1 like '%TEST4%'
       ) 
       OR 
       (table1.subject_2 like '%TEST1%'  OR 
        table1.subject_2 like '%TEST2%'  OR 
        table1.subject_2 like '%TEST3%'  OR 
        table1.subject_2 like '%TEST4%'
       )

Here if subject_1 = TEST1 then no need to search for the remaining conditions, if not found then search for the other conditions.

I need a record having either of subject_1 from the above query. If subject_1 does not match with any of the results then search for subject_2.

My problem: from the above query, multiple records are being returned where subject_1 matches TEST1 and TEST2 both.

Example:

no,  name,     add1,    occ,   date,   subject_1,subject_2,Exclusion_number        
-----------------------------------------------------------------------------
446 REBECCA   street1    Y    1/1/2001   TEST1   AB               10
446 REBECCA   street1    Y    1/1/2001   TEST2   A                11

I should be able to fetch one row as subject_1 like '%TEST1%' match found. I should not get the second row, as the first condition satisfied already.

Currently with my query, I am getting 2 rows, where the requirement is to get only one row.

In case first condition fails then I should check the second condition subject_2 like '%TEST2%'.

Which database are you using? Can you post the entire query? A record can only be included once in the result set, so from a quick glance having more than one condition in your `WHERE` clause matching by itself should not be a problem. Are you doing a join in your query? — Tim Biegeleisen, Mar 20 '18 at 01:28
yes i am doing a join and combining multiple tables. i am working with oracle database — jyothsna, Mar 20 '18 at 01:33
**Edit** your question and show the full query, and include your current and expected output, or at least a sample of that data. — Tim Biegeleisen, Mar 20 '18 at 01:33
"query: `where`" ... most people (me included) won't read much past that point. A query can't begin with the WHERE clause; they teach that in the first 30 minutes of the first introductory class in SQL. — , Mar 20 '18 at 01:39
I am trying to edit and copying the whole query in the editor but its expecting the code formatting. — jyothsna, Mar 20 '18 at 02:29
select fc.no,fl.life,fc.add1,fs.occ,fb.date,fe.subject_1,fe.subject_2,fe.no from FCLNT fc join FS fs on (fs.client_no = fc.no) and (fs.bene = 'I') and fc.prod = 'R' and fc.date BETWEEN '4/AUG/16' AND '7/AUG/16' join FN fn on fn.client_no=fc.no and (fn.no_insured = fs.no_insured) join table1 fe on (fe.no = fn.exclsn_no) join FL fl on (fl.client_no = fs.client_no) and (fl.life_no = fs.life_no) join FB fb on (fb.client_no = fc.no) join FA fd on fd.no = fs.agent_no — jyothsna, Mar 20 '18 at 02:31
You are not getting multiple records. A `where` clause does not multiply data. Sample data and desired results out help us understand what you really are getting. — Gordon Linoff, Mar 20 '18 at 02:51

score 0 · Answer 1 · answered Mar 20 '18 at 01:25

0

This will return the first row that matches any of the criteria.

SELECT TOP 1 *
FROM TABLE1
where  
(table1.subject_1 like '%TEST1%' OR table1.subject_1 like '%TEST2%' OR 
table1.subject_1 like '%TEST3%' OR table1.subject_1 like '%TEST4%') OR 
(table1.subject_2 like '%TEST1%' OR table1.subject_2 like '%TEST2%' OR 
table1.subject_2 like '%TEST3%' OR table1.subject_2 like '%TEST4%')

answered Mar 20 '18 at 01:25

Troy Turley

659
10
28

Problem is i am fetching from different columns and tables by using joins. – jyothsna Mar 20 '18 at 02:34

cowbert · Answer 2 · 2018-03-20T01:35:46.260

0

OR is completely associative, and is short-circuited (given A or B, if A is true, then B is never checked) so your parentheses don't do anything, given (A or B) or (C or D), if A is true, then (A or B) is automatically true, then the entire expression is true, and the row is returned.

In a case like A or B or C or D, if A is true, the row is returned, if A is false but B is true, the row is returned, if A and B are false but C is true, the row is returned, etc.

Hope that helps.

EDIT:

In some cases, the query optimizer may choose not the short circuit the OR (so if the 2nd predicate might throw an exception it may sometimes be executed first): (see also: OR Operator Short-circuit in SQL Server), but for the OP's case, associative property still determines evaluation of the expression.

edited Mar 20 '18 at 01:35

answered Mar 20 '18 at 01:25

cowbert

3,212
2
25
34

1

I'm not sure you can rely on the ordering you describe in your answer. That is, the database may be free to choose which side of the OR to evaluate first. – Tim Biegeleisen Mar 20 '18 at 01:27
well associative-ness means it doesn't really matter either. If any side of the OR is false, the other will be checked and the expression will be true if the other side is true. – cowbert Mar 20 '18 at 01:28

Kaushik Nayak · Answer 3 · 2018-03-20T06:02:58.847

You probably want to restrict the result set based on the first comparison that succeeds. But, as already said in some of the comments, you can't assure which condition will be evaluated first and returned, unless there is a predefined ordering.

So, I suppose you could define and assign an ordering based on your priority and then use ROWNUM = 1 to get the first row matched in the order.

SELECT *
FROM (
    SELECT t.*
    FROM Table1 t
    ORDER BY CASE 
            WHEN subject_1 LIKE '%TEST1%'
                THEN 1
            WHEN subject_1 LIKE '%TEST2%'
                THEN 2
            WHEN subject_1 LIKE '%TEST3%'
                THEN 3
            END
        ,CASE 
            WHEN subject_2 LIKE '%TEST1%'
                THEN 4
            WHEN subject_2 LIKE '%TEST2%'
                THEN 5
            WHEN subject_2 LIKE '%TEST3%'
                THEN 6
            END
    )
WHERE rownum = 1

Demo

score 0 · Answer 4 · answered Jul 24 '20 at 07:41

In where clause, you can not be sure about which condition will be evaluated first. I will suggest using WITH query and UNION. You can write your query something like this:

WITH tableData as (
  SELECT * FROM table1
)

SELECT * FROM (
  SELECT tableData.*, 1 as priority from tableData where subject_1 like '%TEST1'
  UNION
  SELECT tableData.*, 2 as priority from tableData where subject_1 like '%TEST2'
) ORDER BY priority LIMIT 1

The above query will return only one record where subject_1 like '%TEST1' matches. The same query could also be written for subject_2 as well.

If first condition is true then stop fetching from the other conditions in where clause of a SQL query

4 Answers4