在SQL Server中获取两个表之间的公共行和不同行的最佳方法是什么？

-1

我正在做一个比较两个设备的项目。我想既得到差异，又得到共同点，比如说在保持顺序的情况下，上述设备的可用3g技术，共同点将放在一个跨度中，而其余部分将是纯文本。我尝试了以下方法：

SELECT n.name AS modif, n.name FROM device_networks
JOIN networks n ON n.id = network_id
JOIN network_technologies nt ON nt.id = n.tech_id
WHERE G=3 AND device_id = 77 
AND n.name NOT IN(
SELECT n.name FROM device_networks
JOIN networks n ON n.id = network_id
JOIN network_technologies nt ON nt.id = n.tech_id
WHERE G=3 AND device_id = 10
INTERSECT
SELECT n.name FROM device_networks
JOIN networks n ON n.id = network_id
JOIN network_technologies nt ON nt.id = n.tech_id
WHERE G=3 AND device_id = 77
)
UNION
SELECT CONCAT('<span class=''dif''>', n.name,'</span>') AS modif, n.name FROM device_networks
JOIN networks n ON n.id = network_id
JOIN network_technologies nt ON nt.id = n.tech_id
WHERE G=3 AND device_id = 77 
AND n.name IN(
SELECT n.name FROM device_networks
JOIN networks n ON n.id = network_id
JOIN network_technologies nt ON nt.id = n.tech_id
WHERE G=3 AND device_id = 10
INTERSECT
SELECT n.name FROM device_networks
JOIN networks n ON n.id = network_id
JOIN network_technologies nt ON nt.id = n.tech_id
WHERE G=3 AND device_id = 77
)
ORDER BY name DESC

但是它似乎太大了，这使我认为还有另一种处理方法。我将为子查询使用一个函数，但是如果有更好的方法，请告诉我！谢谢。