CODEWITHSUNDEEP
sqlsql-server
Developer
Developer

Reputation: 35

How to Select Two tables without union and union all?

Table1:

Id  | Text      | Parent Id | Number
**************************************
101 |robber     | 201       | 1
102 |devel      | 202       |  1
103 |programmer | 203       | 3


Table 2     
Id  | TO id     | Parent Id | Number
**************************************
102 |355        | 201       | 1
104 |366        | 202       |  2
105 |377        | 203       | Null

I need to join two tables without using Union and union All

Out Put Like: (Both table columns are same expect one To Id that columns add to last )

Id  |   Text    | Parent Id |  Number | To Id
101 |robber     | 201       |  1      |  Null
102 |devel      |  202      | 2       | null
103 |programmer |  203      |  3      |Null
102 |Null       | 201       |  1      |355
104 | Null      |   202     |  2      | 366
105 |Null       |    203     |  null  | 377

Upvotes: 1

Views: 13360

Answers (2)

nazark
nazark

Reputation: 1240

Try full join

select isnull(a.id,b.id) as id, 
    a.Text1,isnull(a.ParentId,b.ParentId) parentid,
    isnull(a.Number,b.Number) numm,TOid 
 from @t a 
 
 full join @t1 b on a.Id=b.Id and a.ParentId=b.ParentId

data

declare @t table (Id int,Text1 varchar(50),ParentId int, Number int) insert into @t
(Id,Text1,ParentId, Number) values
(101 ,'robber'     , 201       , 1),
(102 ,'devel'      , 202       ,  1),
(103 ,'programmer' , 203       , 3)

declare @t1 table (Id int,TOid int,ParentId int, Number int) insert into @t1
(Id,TOid,ParentId, Number) values
(102 ,355        , 201       , 1),
(104 ,366        , 202       ,  2),
(105 ,377        , 203       , Null)

Upvotes: 2

Matt
Matt

Reputation: 14341

and for the non-union way you can use a temp table as follows

IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL
    BEGIN
        DROP TABLE #TempTable
    END

CREATE TABLE #TempTable (
    Id INT
    ,[Text] VARCHAR(20)
    ,ParentId INT
    ,Number INT
    ,ToId INT
)

INSERT INTO #TempTable (Id, [Text], ParentId, Number)
SELECT
    Id
    ,[Text]
    ,ParentId
    ,Number
FROM
    TableNameA

INSERT INTO #TempTable (Id, ToId, ParentId, Number)
SELECT
    Id
    ,ToId
    ,ParentId
    ,Number
FROM
    TableNameB


SELECT *
FROM
    #TempTable

I would only use this way in circumstances that I definitely want a temp table of the results or if my logic has to be broken up for some reason, very rare for the later. There are still other ways yet but if you are not using a temp table union all should perform better than the other ways.

Upvotes: 1

Related Questions