SQL AND Clause only if another value exists

Question

I have a basic SQL query where I am selecting data from a core records table. I want to add an AND clause to my statement to filter out the results based on a table variable, only if data actually exists in there.

SELECT 
    * 
FROM 
    TABLE
WHERE 
    field = '123'
AND
    (gender IN (SELECT gender FROM @genders))

In this case, I am looking for all records where field = 123. My goal here is to say that if @genders contains records, filter by that as well.

However, if @genders is empty and we don't have any data in it, it should include all records.

How can I go about doing this? The temp tables are created based on the user selecting one or more optional pieces of criteria from the UI. If they choose a gender for example, I put their selections into a temp table and then I need to search records that meet that criteria. However, if they don't select a gender, I want to include all records, regardless of what the main record has for the gender field.

Couldn't you just do a LEFT JOIN of @genders on your table TABLE — Ryan Wilson, Mar 07 '18 at 17:25

score 4 · Answer 1 · answered Mar 07 '18 at 17:23

4

SELECT 
    * 
FROM 
    TABLE
WHERE 
    field = '123'
AND ((SELECT count(1) FROM @genders) = 0 OR
    (gender IN (SELECT gender FROM @genders)))

answered Mar 07 '18 at 17:23

JBrooks

9,901
2
28
32

I think `0 = 0` would be simplier condition :) – Michał Turczyn Mar 07 '18 at 19:27

score 2 · Answer 2 · answered Mar 07 '18 at 17:27

2

You can use IF condition:

IF EXISTS(SELECT gender FROM @genders)
BEGIN
  SELECT * FROM TABLE
  WHERE field = '123'
  AND
  (gender IN (SELECT gender FROM @genders))
END
ELSE
BEGIN
  SELECT *  FROM TABLE
END

answered Mar 07 '18 at 17:27

Elham Kohestani

3,013
3
20
29

score 1 · Accepted Answer · answered Mar 07 '18 at 18:37

1

You are going to think this is odd but it is efficient

SELECT t.* 
FROM TABLE t
JOIN GENDERS g 
  on g.gender = t.gender 
 and t.field = '123'
union all 
SELECT t.* 
FROM TABLE t
where not exists (select 1 from genders)

answered Mar 07 '18 at 18:37

paparazzo

44,497
23
105
176

score 0 · Answer 4 · answered Mar 07 '18 at 17:43

0

Maybe I'm under-thinking it, but isn't it just this?

SELECT 
    *
FROM 
    TABLE AS t
LEFT JOIN
    @genders AS g
        ON
            g.gender = t.gender
WHERE 
    field = '123'
    AND
    (g.gender = t.gender OR g.gender IS NULL);

answered Mar 07 '18 at 17:43

Eric Brandt

7,886
3
18
35

score 0 · Answer 5 · answered Mar 07 '18 at 23:28

If your query doesn't get too complicated, I'd recommend an if statement. Once you find yourself continually adding if else statement in there, I'd recommend looking into dynamic SQL.

IF EXISTS(SELECT gender FROM @genders)
BEGIN
  SELECT * FROM TABLE
  WHERE field = '123'
  and gender IN (SELECT distinct gender FROM @genders)
END
ELSE
BEGIN
  SELECT * FROM TABLE WHERE field = '123'
END

SQL AND Clause only if another value exists

5 Answers5

Linked