CODEWITHSUNDEEP
sqloracle-databasecasesql-like
jyothsna
jyothsna

Reputation: 41

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

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%'.

Upvotes: 4

Views: 8116

Answers (4)

Sunny
Sunny

Reputation: 932

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.

Upvotes: 0

Kaushik Nayak
Kaushik Nayak

Reputation: 31666

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

Upvotes: 0

cowbert
cowbert

Reputation: 3452

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.

Upvotes: 0

Troy Turley
Troy Turley

Reputation: 743

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%')

Upvotes: 0

Related Questions