Sql: nested conditions

Question

0.00/5 (No votes)

See more:

Something probably very simple, however I cannot get it.
I have a query like:

SQL

select * from Table t where t.id = 1

Now, I have a variable:

SQL

declare @type int = 0

Type can be 0 or 1 or whatever else.
I am trying to do something like this:

select everything from Table where id equals 1 and (if type equals 0 then name should be 'something' else the condition is true)

In LINQ I would do it like this:

C#

queryable.Where(x => x.id == 1 && (type != 0 || x.name == "something"))

What I have tried:

So this is what I came up with (which of course is wrong):

SQL

select * Table t where t.id = 1 and (case when @type = 0 then t.name = 'something' else 1=1 end)

Any help is greatly appreciated.
Thanks

P.S. In addition to that, I am trying no to use 'OR' because for some reason SQL server is very slow on 'ORs' and my query is already very big (300 lines) so performance is very important.

Posted 9-Jun-21 19:59pm

csrss

Updated 9-Jun-21 21:22pm

v2

Add a Solution

Comments

Maciej Los 10-Jun-21 2:09am

Like this?

select * Table t where t.id = 1 and (@type <> 0 OR t.name = 'something')

csrss 10-Jun-21 2:17am

I am just considered about using 'OR' in this solution. As I have mentioned, OR is very slow.

Maciej Los 10-Jun-21 2:51am

Logical operator is NOT causing slow execution of query.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2021-06-09T20:23:00

Solution 1

Try:

SQL

... WHERE t.ID = 1 AND ((@Type = 0 AND t.Name = 'something') OR @Type <> 0)

Posted 9-Jun-21 20:23pm

OriginalGriff

v2

Comments

csrss 10-Jun-21 2:24am

Is there a solution without using 'OR' maybe?

Jörgen Andersson · Answer 2 · 2021-06-09T21:22:00

Or isn't always slow, it depends.

If the conditions are on the same table and it's also indexed an or will not be slow.
But if the OR conditions are on different tables in a join, the server will have to join all rows and filter the result, which can be extremely slow.

In this case one of the conditions isn't even on a column but on a variable.

One common workaround is to convert the query into two queries with a union between them. This allows the server to use indexes and to filter on the fly.

SQL

SELECT  *
FROM    Table t
WHERE   t.id = 1
    AND @type <> 0
UNION
SELECT  *
FROM    Table t
WHERE   t.id = 1
    AND t.name = 'something')

Whether this works or not depends on how the actual query looks like