output with condition based sub query in mysql

Question

Below is my query:

select c1, c2, c3, c4, c5 from table1 where c1 =0 //Condition 1
AND c2="abc" //Condition 2
AND c3="xxx" //Condition 3
AND c4 = 5 //Condition 4

Here as we know that condition 2 will be applied on result data given by condition 1, condition 3 will be applied on on result data given by condition 2 and 1 and similarly condition 4 will be applied...

I want query which will execute further if any condition in between fails.

Means if condition 3 will give no result (or null result set) then my condition 4 will be applied on null result set so defiantly final output will be null, but I want if condition 3 will give no result (or null result set) then condition 4 "Should be Applied" to result set return by condition 2 and 1.

Here I can not change sequence of conditions due to performance issue.

Please guide me with query.

I think sample data and desired would be a big help in understanding what you are trying to do. Your explanation is also incorrect: SQL does not guarantee the order of evaluation of expressions. The result set simply guarantees that the `where` clause evaluates to true. — Gordon Linoff, Dec 17 '15 at 11:59
@GordonLinoff i am dealing with table having crores of rows, so i am not able to give you sample data also by AND condition i am minimizing my data.but i hope you understand my question. Is there any other way get desire output from this.. — JP711, Dec 17 '15 at 12:05

score 0 · Answer 1 · answered Dec 17 '15 at 12:01

0

Try this

select c1, c2, c3, c4, c5 from table1 where (c1 =0) or (c2="abc") or (c3="xxx") or (c4 = 5)

answered Dec 17 '15 at 12:01

Karthika

110
12

Here I am dealing with table having crores of rows, so by AND condition i am minimizing my data, so if I use OR it will create performance issue as query will take so much time to get execute... – JP711 Dec 17 '15 at 12:09
@Karthika: While this code may answer the question, it would be better to explain how it solves the problem and why to use it. Code-only answers are not useful in the long run. – Tobias Liefke Dec 17 '15 at 12:47

Y.B. · Answer 2 · 2015-12-17T13:23:15.647

0

As far as I can see you want to have condition 3 optional. That can be done, but would hardly be an efficient solution:

Select c1, c2, c3, c4, c5
From table1
Where c1 = 0
  AND c2 = "abc"
  AND (c3 = "xxx"
       OR NOT Exists (Select '*' From table1 Where c1 = 0 AND c2 = "abc" AND c3 = "xxx")
      )
  AND c4 = 5

Better approach might be to use temporary table filtered on conditions 1 and 2 with further logic applied to this data subset.

edited Dec 17 '15 at 13:23

answered Dec 17 '15 at 13:13

Y.B.

3,526
14
24

your solution is looking feasible but yes you are right... query is taking so much time to get execute. – JP711 Dec 17 '15 at 13:49

score 0 · Accepted Answer · answered Dec 17 '15 at 14:17

0

I got one simple solution for this and it is working in my scenario. Also I am able to reduce time for query execution on my big data. Defiantly two queries will be executed but i find this proper solution.

I used simple Union query.

select c1, c2, c3, c4, c5 from table1 where c1 =0 //Condition 1
AND c2="abc" //Condition 2
AND c3="xxx" //Condition 3
AND c4 = 5 //Condition 4
UNION
select c1, c2, c3, c4, c5 from table1 where c1 =0 //Condition 1
AND c2="abc" //Condition 2
AND c4 = 5 //Condition 4

If someone else has more accurate and proper solution. Please post it.

answered Dec 17 '15 at 14:17

JP711

93
1
10

`Union` the way you wrote it would just yield `Distinct` of the second part alone. – Y.B. Dec 22 '15 at 12:31
@Y.B. can you please explain with any example. Problem which i was facing is getting resolved by UNION as of now. I really appreciate if you can get me better solution. – JP711 Dec 22 '15 at 15:17
**Condition 3** is missing from second part of your `UNION`. Thus second part of the `UNION` would take all the rows selected in first part _and also_ those rows that do not satisfy just **Condition 3**. `UNION` without `ALL` would return `DISTINCT` list of records from both parts. Since first part is a subset of second part the `UNION` is equivalent to `DISTINCT` of second part. – Y.B. Dec 22 '15 at 16:21
Try it: `With table1 As ( Select * From ( Values ((0), ('abc'), ('xxx'), (5), (1)), ((0), ('abc'), ('yyy'), (5), (2)), ((0), ('abc'), ('xxx'), (6), (3)) ) As a (c1, c2, c3, c4, c5) ) Select c1, c2, c3, c4, c5 From table1 Where c1 =0 --Condition 1 AND c2='abc' --Condition 2 AND c3='xxx' --Condition 3 AND c4 = 5 --Condition 4 UNION Select c1, c2, c3, c4, c5 From table1 Where c1 =0 --Condition 1 AND c2='abc' --Condition 2 AND c4 = 5 --Condition 4` – Y.B. Dec 22 '15 at 16:27

output with condition based sub query in mysql

3 Answers3