Data query to keep the common data-CodePudding

I have a table

Table 1 : It the final table contains all data . ID and IDS are composite key

ID      IDS    name
1       PL35    Bumper
151111  PL35    Bumper
151111  PL36    Bumper
1516    PL35    TUMI
151511  PL36    Limo
151521  PL35    Superb
151521  PL36    Superb

table 2 : Its a pre final table which will upcoming data with incomplete information

ID      IDS    name
15100   PL35    NULL
1516    PL35    NULL
151521  PL36    NULL
151511  PL36    NULL

EXPECTED RESULT : Some IDs (ID IDS) are in Table 1 and some are in Table 2 . I need to compare the data of table 1 and table 2 . The rule is Keep the common data with Table 1 information Keep the new row with (id IDS) which is in table 2 but not in table 1

for eg;

(15100 PL35) is in table 2 but not in table 1 then it will remain
(1516 PL35) is common in both then the row from table 1 will remain.
( 151511 PL36) is also common hence will remain .
The data (ID IDs) which is not in table 2 but in table 1 is not needed.

    ID      IDS    name
    15100   PL35    NULL
    1516    PL35    TUMI 
    151511  PL36    Superb

SO far I am only think about this

select * from table1 t1 
inner join table2 t2 on t1.id=t2.id

CodePudding user response：

Use INTERSECT, named after the equivalent set operation.

SELECT 
   ID,
   IDS
FROM
  table1
INTERSECT
SELECT 
   ID,
   IDS
FROM
   table2

EDIT In answer to your question, you can wrap this in a CTE and then join back onto table1.

WITH common AS
(
  SELECT 
       ID,
       IDS
    FROM
      table1
    INTERSECT
    SELECT 
       ID,
       IDS
    FROM
       table2
)
SELECT  
   c.*, 
   t.Name 
FROM common c
INNER JOIN
    table1 t
 ON c.ID = t.ID
 AND c.IDS = t.IDS

CodePudding user response：

I think you can use FULL OUTER JOIN, and put a WHERE condition WHERE t1.id NOT NULL and t2.id NOT NULL.