All records from first table and extra records from second table-CodePudding

I have 2 tables where i want to take all records from 1st table and extra records from 2nd table.

Table A

 ----- --------- --------- 
| ID  | NAME    | TASK    |
 ----- --------- --------- 
| 101 | Alan    | Prepare |
 ----- --------- --------- 
| 102 | Fabien  | Approve |
 ----- --------- --------- 
| 103 | Christy | Plan    |
 ----- --------- --------- 
| 104 | David   | Approve |
 ----- --------- --------- 
| 105 | Eric    | Set     |
 ----- --------- ---------

Table B

 ----- --------- --------- 
| ID  | NAME    | TASK    |
 ----- --------- --------- 
| 101 | Richy   | Prepare |
 ----- --------- --------- 
| 103 | Girish  | Plan    |
 ----- --------- --------- 
| 106 | Fleming | Approve |
 ----- --------- --------- 
| 107 | Ian     | Set     |
 ----- --------- ---------

Expected output

 ----- --------- --------- 
| ID  | NAME    | TASK    |
 ----- --------- --------- 
| 101 | Alan    | Prepare |
 ----- --------- --------- 
| 102 | Fabien  | Approve |
 ----- --------- --------- 
| 103 | Christy | Plan    |
 ----- --------- --------- 
| 104 | David   | Approve |
 ----- --------- --------- 
| 105 | Eric    | Set     |
 ----- --------- --------- 
| 106 | Fleming | Approve |
 ----- --------- --------- 
| 107 | Ian     | Set     |
 ----- --------- ---------

I have tried using LEFT JOIN. But i'm getting only all from left table.

select * from A left join B on A.ID=B.ID and B.ID is NULL

I have also tried UNION and UNION ALL but since Name can be different in 2 tables i'm getting both records. One solution could be using NOT IN but it will be big for me as i refer big queries as table A & B here. I dont know what i'm missing. It should be very simple but it is not striking me now. Please help.

CodePudding user response：

I'm thinking a union with the help of ROW_NUMBER and a computed column:

WITH cte AS (
    SELECT ID, NAME, TASK, 1 AS SRC FROM TableA
    UNION ALL
    SELECT ID, NAME, TASK, 2 FROM TableB
),
cte2 AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY SRC) rn
    FROM cte
)

SELECT ID, NAME, TASK
FROM cte2
WHERE rn = 1;

The idea here is to build an intermediate table containing all records from both tables. We introduce a computed column which keeps track of the table source, and give A records a higher priority than B records. Using ROW_NUMBER allows us to select the A records over B records having the same ID.

CodePudding user response：

Full outer join will work, as full out join will get all the matching and non matching records from both the tables

;with tablea as 
(
select  101 as id,  'Alan'    name, 'Prepare ' as task
union select  102 , 'Fabien'  , 'Approve' 
union select  103 , 'Christy' , 'Plan    '
union select  104 , 'David'   , 'Approve '
union select  105 , 'Eric'    , 'Set   ')
,tableb as (
select  101 as ID  ,'Richy  ' as NAME   ,' Prepare ' as TASK     
union select  103 ,'Girish ',' Plan    '
union select  106 ,'Fleming',' Approve '
union select  107 ,'Ian    ',' Set '
)

select isnull(a.id,b.id) as id, isnull(a.name,b.name) as name, isnull(a.task,b.TASK) from tablea a
full outer join tableb b on a.id = b.ID

Result