single sql query to select distinct values from two tables with data.

Description:

In this example we explain that how to get unique rows from two separates table and also add two more column at run time that display the rows values is exists in which table. Or single sql query to select distinct values from two tables with data. Query? Or SQL query to find distinct values in two tables.

there are two query that you can use for this result.

Query1:

SELECT

 ISNULL(c1.Code, c2.Code) AS Code,

 CASE WHEN C1.ID IS NOT NULL THEN 'Yes' ELSE 'No' END AS [Available in Table1],

 CASE WHEN C2.ID IS NOT NULL THEN 'Yes' ELSE 'No' END AS [Available in Table2]

FROM dbo.Table1 as c1

FULL OUTER JOIN dbo.Table2 AS c2 ON c1.Code = c2.Code

ORDER BY ISNULL(c1.Code, c2.Code )

 Query2:

select 

ROW_NUMBER() over (order by a.code ) as #,

a.code,case when a.code = c1.code then 'Yes' else 'No' end AS [Available in Table1]  ,

case when a.code = c2.code then 'Yes' else 'No' end AS [Available in Table2] 

 from (select

       code

       from dbo.Table1 union

       select

       code

          from dbo.Table2)a

 left join dbo.Table1 c1 on a.code = c1.code

  left join dbo.Table2 c2 on a.code = c2.code where a.code is not null