Finding if some row fields in Table A is the same of some fields in any row in Table B-CodePudding

I Have 2 Tables:

Table A {contains fields A1 , A2}

Table B {contains fields B1 , B2 , B3 , B4 , B5 , B6}

My application needs a fast and performant business logic that needs to implement this difficult SQL Query:

select * from Table B where ( BOTH A1 and A2 in any Row in Table A) Exist in any of the fields of Table B Rows.

as in this Example:

CodePudding user response：

In MySQL you can do somethin like this, all other databases system can do something similar

CREATE Table A ( A1 int , A2 int);
INSERT INTO A VALUES( 5,2),(7,1);

CREATE Table B ( B1 int, B2 int, B3 int, B4 int, B5 int, B6 int);
INSERT INTO B VALUES(5,4,6,1,2,0)

SELECT * FROM B 
WHERE EXISTS (SELECT 1 FROM A WHERE FIND_IN_SET(A1,CONCAT(B1,',',B2,',',B3,',',B4,',',B5,',',B6 )))
AND EXISTS (SELECT 1 FROM A WHERE FIND_IN_SET(A2,CONCAT(B1,',',B2,',',B3,',',B4,',',B5,',',B6 )))

B1 | B2 | B3 | B4 | B5 | B6
-: | -: | -: | -: | -: | -:
 5 |  4 |  6 |  1 |  2 |  0

db<>fiddle here

CodePudding user response：

Isn't this just

Select * From A,B
Where A.Field1 In (B.Field1, B.Field2, B.Field3, B.Field4, B.Field5, B.Field6, ...)
  And A.Field2 In (B.Field1, B.Field2, B.Field3, B.Field4, B.Field5, B.Field6, ...)

As far as performance goes, there's probably no way it performs well if both A and B are large.

CodePudding user response：

A combination of EXISTS with 2 IN will do the trick.

select * 
from TableB b
where exists (
    select 1
    from TableA a
    where a.A1 in (b.B1, b.B2, b.B3, b.B4, b.B5, b.B6)
      and a.A2 in (b.B1, b.B2, b.B3, b.B4, b.B5, b.B6)
)

Test on db<>fiddle here