I would like to write an SQL query for intersection between A and B unless B is empty set. For example:

Table A:

| col |
-------
|  1  |
|  2  |
-------

Table B:

| col |
-------
|  2  |
|  3  |
-------

Table B':

| col |
-------
-------

• For A and B, I expect 2 (take B which are also contained in A)
  • e.g. SELECT * FROM A INTERSECT SELECT * FROM B; or SELECT A.* from A JOIN B ON a.col = b.col;
• For A and B', I expect 1 and 2 (take A as default if B is empty)

Do you have any idea?

CodePudding user response：

Edit : Modified for Postgres.

You can run the standard INETRSECT command using both sets to get the standard intersect results. In order to return the non-empty set when on set is empty, UNION the INTERSECT results, which will be null if a set is missing, against the non-empty set. There needs to be an empty check on the second step or all distinct values will be returned when both sets have values with no intersects {a,b,c}{x,y,z}

Schema (PostgreSQL v14)

CREATE TABLE SetA (SetValue INT);
CREATE TABLE SetB(SetValue INT);

INSERT INTO SetA VALUES(7),(3);
INSERT INTO SetB VALUES(2),(3),(1);

Query #1

SELECT        
    SetValue
FROM
(
    SELECT SetValue FROM
    (
        SELECT SetValue FROM SetA
        UNION 
        SELECT SetValue FROM SetB
    )x
    WHERE 
        NOT EXISTS(SELECT * FROM SetA) OR NOT EXISTS(SELECT * FROM SetB)
        
    UNION
  
    SELECT SetValue FROM SetA
    INTERSECT
    SELECT SetValue FROM SetB 
  
 )x;

View on DB Fiddle

CodePudding user response：

I hope this will solve the issue,

DECLARE @A VARCHAR(20)='{1, 2, 3}'
DECLARE @B VARCHAR(20)='{}'


    SELECT CASE WHEN @B='{}' THEN @A ELSE '{' STRING_AGG(A.Data,',') '}' END AS Data 
    FROM (
    
    SELECT LTRIM(RTRIM(REPLACE(REPLACE(Value,'{',''),'}',''))) Data FROM string_split(@A,',')
    INTERSECT
    SELECT LTRIM(RTRIM(REPLACE(REPLACE(Value,'{',''),'}',''))) Data FROM string_split(@B,',')) A