SQL Intersect only if some values given for the parameter (Conditional Intersect)

Question

I've query to intersect three queries based on three parameters being given.

So Ive done it using simple intersect as something like this

Select name, email from table1 where param='p1'
intersect
Select name, email from table1 where param='p2'
intersect
Select name, email from table1 where param='p3'

But the problem is sometimes few of the parameters having no data or blank. in that case the query results nothing because of intersect with blank data. So how can I handle this situation, so that if no data for one parameter (Among any of the 3), that query should not be considered in the whole

even if you default values for non-existent data intersect wouldn't give you a result, unless all the 3 parameters are missing and you use the same default values for all of them. — Vamsi Prabhala
– Vamsi Prabhala, Commented Jun 13, 2016 at 15:42
There's no need to explain it to me again like I'm five. I understood what you were saying and clearly stated that I was talking about different rows, not the same row. — Martin Smith
– Martin Smith, Commented Jun 13, 2016 at 18:06

clifton_h · Accepted Answer · 2016-06-14 07:35:46Z

2

Why are you using INTERSECT on the same column three times? INTERSECT is essentially an INNER JOIN. As already noted, INNER JOINS according to the VENN diagram only return result sets that are found in each successive query (meaning {A}U{B} is NOT the same as {A}U{C} )

Actually, your question does not answer whether the param is a @variable, in which case for index reasons you should use transfer the parameter to a variable before passing it into the query. Otherwise, the query will not be using a SARG

Instead, use the following:

DECLARE @Param  [DATA_TYPE]{#}
      , @Param2 [DATA_TYPE]{#}
      , @Param3 [DATA_TYPE]{#}

SET @Param = [USP_Proc @param = 'p1']
  , @Param2 = [USP_Proc @param = 'p2']
  , @Param3 = [USP_Proc @param = 'p3']

SELECT  NAME
      , EMAIL
 FROM table1
 WHERE col_1 = @param
    OR col_1 = @param2
    OR col_1 = @param3

edited Jun 14, 2016 at 7:35

answered Jun 14, 2016 at 7:30

clifton_h

1,2988 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Arleigh Reyna · Accepted Answer · 2016-06-13 16:13:21Z

0

Intersect only returns values if the values in query A also exist in query B and they also exist in query C

So in the first example the value 'A' exists in all three queries so it will return 'A'. In the second example, as you are intersecting three queries and 'A' does not exist in all three queries so it return no values. You may want to use "Union" instead of "Intersect" as it returns data when the values exist in query A or B or C as shown in the third example which would return values of both 'A' and 'B'.

Example 1:

SELECT 'A'
intersect
SELECT 'A'
intersect
SELECT 'A'

Example 2:

SELECT 'A'
intersect
SELECT 'A'
intersect
SELECT 'B'

Example 3:

SELECT 'A'
UNION
SELECT 'A'
UNION
SELECT 'B'

answered Jun 13, 2016 at 16:13

Arleigh Reyna

3691 silver badge6 bronze badges

Collectives™ on Stack Overflow

SQL Intersect only if some values given for the parameter (Conditional Intersect)

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related