I have a SQL Server 2014 database table containing a code and two identifiers:
DECLARE @Foo TABLE (code CHAR, id_a INT, id_b INT) INSERT INTO @Foo (code, id_a, id_b) VALUES ('A', 1, 2), ('B', 2, 1), ('C', 3, 4), ('D', 4, 0), ('E', 5, 3)
The first two rows of the table form a two-way match, as the first row links IDs 1 and 2, and the second row links them in reverse.
I want to find all rows that don't take part in a two-way match, and to that end I've written the following query:
SELECT code FROM @Foo WHERE CONCAT(id_a, ':', id_b) NOT IN ( SELECT CONCAT(id_b, ':', id_a) FROM @Foo )
This is working (it returns codes C, D and E for my sample data), but I'm concerned about the performance of those concatenations once the table has millions of rows in it.
I haven't tested the performance with a large dataset (that's tomorrow's job), but in the meanwhile I was wondering if there's a more elegant and performant way I can structure this query?
P.S. The query plan indicates that I'm doing table scans inside a scalar compute inside a nested loop, which doesn't sound like it'll scale too well:
|--Nested Loops(Left Anti Semi Join, WHERE:([Expr1007] IS NULL OR [Expr1008] IS NULL OR [Expr1007]=[Expr1008])) |--Compute Scalar(DEFINE:([Expr1007]=concat(CONVERT_IMPLICIT(varchar(12),[id_a],0),':',CONVERT_IMPLICIT(varchar(12),[id_b],0)))) | |--Table Scan(OBJECT:(@Foo)) |--Compute Scalar(DEFINE:([Expr1008]=concat(CONVERT_IMPLICIT(varchar(12),[id_b],0),':',CONVERT_IMPLICIT(varchar(12),[id_a],0)))) |--Table Scan(OBJECT:(@Foo))