Finding rows that exists in one column, but not another

1

I need to find the rows in MS SQL that exist in one column but do not exist in another column. This is my table that has mission rows:

Table A

ASP                         Con
e123456                  e155555
e543216                  e987654
e155555                  e123456
e987654                  e432156

I want to get e123456 and e432156. How can I achieve this?

2

Your criteria are not clear, and one of the example values you gave appears in both columns. In any case, this should give you the results you're looking for:

;WITH CTE(all_values) AS (
SELECT ASP FROM TableA
UNION ALL
SELECT Con FROM TableA)
SELECT all_values FROM CTE
GROUP BY all_values
HAVING COUNT(*)=1
2 Likes
3

Another method is to use CROSS APPLY, esp. if you have many columns rather than just 2 to consolidate:


;WITH cte_test_data AS (
    SELECT CAST('e123456' AS varchar(10)) AS ASP, CAST('e155555' AS varchar(10)) AS Con UNION ALL
	SELECT 'e543216', 'e987654' UNION ALL
	SELECT 'e155555', 'e123456' UNION ALL
	SELECT 'e987654', 'e432156'
)
SELECT ASP_or_Con
FROM (
    SELECT ASP_or_Con
	FROM cte_test_data
	CROSS APPLY (VALUES(ASP), (Con)) AS ASP_or_Con(ASP_or_Con)
) AS derived
GROUP BY ASP_or_Con
HAVING COUNT(*) = 1
ORDER BY 1
4

Thank you @robert_volk !. The query worked right away.

Thanks again for all your help! appreciate your help!

5

Thank you @ScottPletcher

6

Hi

Hope this helps

Another way to do this ? using Full Outer Join

create sample data

drop table #Temp

create table #Temp (ASP varchar(100), CON varchar(100))

insert into #Temp select 'e123456','e155555'
insert into #Temp select 'e543216','e987654'
insert into #Temp select 'e155555','e123456'
insert into #Temp select 'e987654','e432156'

SELECT 
    max(a.asp)
   ,max(b.con) 
FROM 
   #Temp a 
     full outer join 
   #Temp b 
        on a.ASP = b.con 
WHERE 
    b.asp is null OR a.CON is null

image

8

Hi

Another way to do this

select asp from #Temp where asp not in ( select con from #Temp)
union all 
select con from #Temp where con not in ( select asp from #Temp)