SQl - Intersect, Need more columns from table 1-CodePudding

I have this query that returns all records that intersects these tables but I want more columns from my table 1 (say d,e,f) but these columns are not present in table 2.

(select a,b,c from tab1) where d<'31-dec21'
intersect
(Select a1,b1,c1 from tab2)

What query should I use to get column a,b,c,d,e,f from tab1 that has a=a1,b=b1 and c=c1 in tab2?

CodePudding user response：

Use a subquery:

select a, b, c, d, e
from tab1
where (a, b, c) in (select a, b, c from tab1 where d < date '2021-12-31'
                    intersect
                    select a1, b1, c1 from tab2
                   );

CodePudding user response：

INTERSECT can be rewritten to an EXISTS condition:

select tab1.* 
from tab1
where d < '31-dec21'
  and exists (select *
              from tab2
              where (tab1.a,tab1.b,tab1.c) = (tab2.a1,tab2.b1,tab2.c1))

CodePudding user response：

Your query does two things:

select all a/b/c tuples that exist in both tables
remove duplicates in case there are any (in contrast to INTERSECT ALL; see https://docs.oracle.com/en/database/oracle/oracle-database/21/nfcon/details-enhanced-sql-set-operators-282451515.html on this)

You can hence select from one table and look up the rows in the other table with IN or EXISTS and then apply DISTINCT on the result:

select distinct a, b, c
from tab1 
where d < date '2021-12-31'
and (a, b, c) in (select a1, b1, c1 from tab2);

This enables you to select more columns from tab1 without breaking the query. Simply replace

select distinct a, b, c

select distinct a, b, c, d, e, f

And if it is guaranteed that there can be no duplicate (a, b, c, d, e, f) tuples in tab1, you can even remove DISTINCT.

CodePudding user response：

Try This

(select a,b,c,d,e,f from tab1) where d<'31-dec21'
intersect
(Select a1,b1,c1,null,null,null from tab2)