How do I create a special case Intersect clause in SQL?

Question

I need a set of values, only when they are available in two different select statements, so I used "intersect"

(select statement 1) intersect (select statement 2)

but I want all the values from one of the select statements when the other select statement returns with no value.

Example 1:

Statement 1 returns(1,2,3,4,5) and Statement 2 returns(2,5,8,9);
Expected result 1: (2,5)

Example 2:

Statement 1 returns(NULL) and Statement 2 returns(2,5,8,9);
Expected result 1: (2,5,8,9)

---Edit: Adding 3rd example for clarity---

Example 3:

Statement 1 returns(2,5,8,9) and Statement 2 returns(NULL);
Expected result 1: (2,5,8,9)

---Edit 2--- I am using this select in a

Which database are you using? – Magnus Aug 14 '19 at 07:46 — Magnus, Aug 14 '19 at 07:46
@Magnus I'm using sql-server. – Ardis thrash Aug 14 '19 at 08:09 — Ardis thrash, Aug 14 '19 at 08:09

ScaisEdge · Answer 1 · 2019-08-14T07:19:57.520

for firts case you could use INNER JOIN

select  col 
from  (
  select 1 col union all 
  select 2 union all 
  select 3 union all 
  select 4 union all 
  select 5
) t1  
inner join  (
  select 2 col union all 
  select 5 union all 
  select 8  union all 
  select 9
) t2  ON t1.col = t2.col

for the second you could try using right join checking for null value

select  col 
from  (
  select 1 col union all 
  select 2 union all 
  select 3 union all 
  select 4 union all 
  select 5
) t1  
right join  (
  select 2 col union all 
  select 5 union all 
  select 8  union all 
  select 9
) t2  ON t1.col = t2.col
where t1.col is null

Shouldn't you be using an outer join, if the user wants values on no match? — Matthias Bö, Aug 14 '19 at 07:19

forpas · Answer 2 · 2019-08-14T08:22:56.350

Use UNION ALL like this:

(
  select statement 1
  union all
  select statement 2  
  where not exists (
    select statement 1
  )
)
intersect
(
  select statement 2
  union all
  select statement 1  
  where not exists (
    select statement 2
  )
)

If select statement X already contains a WHERE clause use AND at the end to apply the condition not exists (...).
See the demo.
For these tables:

create table table1(id int);
insert into table1(id) values (1), (2), (3), (4), (5);

create table table2(id int);
insert into table2(id) values (2), (5), (8), (9);

For the case:

(
  select * from table1
  union all
  select * from table2  
  where not exists (
    select * from table1
  )
)
intersect
(
  select * from table2
  union all
  select * from table1  
  where not exists (
    select * from table2
  )
)

the result is:

> | id |
> | -: |
> |  2 |
> |  5 |

for the case:

(
  select * from table1 where id > 9
  union all
  select * from table2  
  where not exists (
    select * from table1 where id > 9
  )
)
intersect
(
  select * from table2
  union all
  select * from table1 where id > 9 
  and not exists (
    select * from table2
  )
)

the result is:

> | id |
> | -: |
> |  2 |
> |  5 |
> |  8 |
> |  9 |

and for the case:

(
  select * from table1
  union all
  select * from table2 where id > 9 
  and not exists (
    select * from table1
  )
)
intersect
(
  select * from table2 where id > 9
  union all
  select * from table1
  where not exists (
    select * from table2 where id > 9
  )
)

the result is:

> | id |
> | -: |
> |  1 |
> |  2 |
> |  3 |
> |  4 |
> |  5 |

score 0 · Answer 3 · answered Aug 14 '19 at 07:42

There are a few ways to do what you want, but you need to place the tables correctly based on where you expect the empty statements to be:

DECLARE @t1 TABLE
(
ID INTEGER
);

DECLARE @t2 TABLE
(
ID INTEGER
);

DECLARE @t3 TABLE
(
ID INTEGER
);

INSERT INTO @t1 VALUES (1),(2),(3),(4),(5);

INSERT INTO @t2 VALUES (2),(5),(8),(9);

SELECT *
FROM @t1 t1
INNER JOIN @t2 t2 ON t1.ID = t2.ID;

--Example 2, you can use either a FULL JOIN
SELECT *
FROM @t1 t1
FULL JOIN @t3 t3 ON t1.ID = t3.ID;

--Example 2, or you can use a LEFT JOIN. But you need to put the table that is likely to have data as the 
--main table in a LEFT JOIN
SELECT *
FROM @t1 t1
LEFT JOIN @t3 t3 ON t1.ID = t3.ID;

Serg · Answer 4 · 2019-08-14T07:49:02.940

Pseudo code

bool flag := exists (select 1 from Statement_2);
select *
from Statement_1 t1
where exists (select 1 from Statement_2 t2 where t1.key = t2.key) or not flag;

Sql server version, replace key comparison with a real key.

declare @flag bit = case when exists (select 1 from Statement_2)
                   then 1 else 0 end;
select *
from Statement_1 t1
where exists (select 1 from Statement_2 t2 where t1.key = t2.key) or @flag = 0;

score 0 · Answer 5 · answered Aug 14 '19 at 08:17

This would be another way:

--Setup
create table #table1(id int);
insert into #table1(id) values (1), (2), (3), (4), (5);

create table #table2(id int);
insert into #table2(id) values (2), (5), (8), (9);

--Query
select 
    ISNULL(a.id, b.id) id
from
    #table1 a full outer join #table2 b on a.id = b.id
where 
    not exists(select null from #table1) or 
    not exists(select null from #table2) or
    (a.id is not null and b.id is not null)

score 0 · Answer 6 · answered Aug 14 '19 at 08:42

You might want to try this to see if it's faster:

with
    s1 as (select count(*) over () c1, <query1> ...),
    s2 as (select count(*) over () c2, <query2> ...),
)
select coalesce(t1.id, t2.id) as id
from table1 t1 full outer join table2 t2
    on t2.id = t1.id and c1 > 0 and c2 > 0
);

score 0 · Answer 7 · answered Aug 14 '19 at 11:51

I think I would do:

select t1.id
from t1
where exists (select 1 from t2 where t2.id = t1.id)
union all
select t1.id
from t1
where not exists (select 1 from t2)
union all
select t2.id
from t2
where not exists (select 1 from t1);

If your statements are tables, then the optimizer should be able to produce efficient execution plans. If the statements are complicated expressions, then you might need a different approach. If so, it would be helpful to know what the statement is like. There may be an optimal way to express the logic with more information.

How do I create a special case Intersect clause in SQL?

7 Answers7