Hello Folks, I came to know one interesting question about sql query.

Question:- How to select all records which are not in relation with each table using JOIN ?

Answer:-

First create both tables and insert values in it as follows:

CREATE table #tmp
(id int,
name varchar(10))

CREATE table #tmp1
(map_id int,
id int,
name varchar(10))

insert into #tmp
SELECT 1,'a'
UNION
SELECT 2,'b'
UNION
SELECT 3,'c'
UNION
SELECT 4,'d'
UNION
SELECT 5,'e'


insert into #tmp1
SELECT 1,1,'a'
UNION
SELECT 2,2,'b'
UNION
SELECT 2,3,'c'
UNION
SELECT 4,4,'d'
UNION
SELECT 5,5,'e'
UNION
SELECT 6,6,'e'
UNION
SELECT 8,7,'e'

select * from #tmp
select * from #tmp1

Now I want output like

For that I have written query like :

SELECT ISNULL(t.id,t1.map_id) FROM
#tmp t
FULL JOIN #tmp1 t1
on t1.map_id=t.id
WHERE 
isnull(t1.map_id,0)<>ISNULL(t.id,0) 

It will give output as i want. Hope you like it. It always fun to play with sql queries, if you have any queries or questions I would love to hear it from you. Don’t forget to share your views.

Keep Smiling 🙂