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