CODEWITHSUNDEEP
sql-servert-sql
Arianule
Arianule

Reputation: 9043

Alternative to multiple AND operator

is there a cleaner(better) alternative to using multiple AND operator in T-sql.

for example

use Portal
go
SELECT
U.Id,
U.FirstName,
U.LastName,
U.Email,
U.Gender, 
U.Country

FROM AspNetUsers U
WHERE (U.M01 >= 80 and U.M02 >= 80 and u.M03 >= 80 AND U.M04 >= 80)

Upvotes: 1

Views: 2397

Answers (3)

st4hoo
st4hoo

Reputation: 2204

There are few options to simplify this particular selection syntax but it is hard to say if they make sense without knowing your entire context and your definition of better/cleaner...

Let's assume that cleaner/better means "selection without conjunction operators" for you.

Some of possible options:

  1. Create a view that will prefilter your AspNetUsers table. Then your query syntax could be simplified to:

    SELECT Id, FirstName, LastName, Email, Gender, Country FROM AspNetUsersPerfiltered

  2. Move filtering logic to dedicated function that will check the condition. Then your query syntax could be simplified to:

    SELECT Id, FirstName, LastName, Email, Gender, Country FROM AspNetUsers WHERE myFunction(M01, M02, M03, M04) = 1

  3. Create additional column(flag) that will be set during row insert/update or by some background process. Then your query syntax could be simplified to:

    SELECT Id, FirstName, LastName, Email, Gender, Country FROM AspNetUsers WHERE FlagColumn = 1

HTH

Upvotes: 1

paparazzo
paparazzo

Reputation: 45096

If AND is what you need then AND is the proper operator.

AND is efficient as it can give up as soon as it gets a single false. The query optimizer is (typically) smart enough to look for the easy false first.

OR is not as efficient so some times you will refactor to get rid of the OR but I have never had a reason to refactor an AND.

If the value changes then maybe go with this for convenience but it should not effect execution 

declare @count int = 80;
SELECT
U.Id,
U.FirstName,
U.LastName,
U.Email,
U.Gender, 
U.Country
FROM AspNetUsers U
WHERE U.M01 >= @count 
  and U.M02 >= @count  
  and u.M03 >= @count  
  AND U.M04 >= @count;

Upvotes: 1

Piotr Lasota
Piotr Lasota

Reputation: 201

No, if result depends on columns that you have in WHERE. If there will be good index it will be fast too (good index for this query is 

create index [some name] on AspNetUsers (M01, M02, M03, M04) include (Id, FirstName, LastName, Email, Gender, Country)

But this query looks strange and probably it is problem with design/normalization

Upvotes: 1

Related Questions