SQL join table with 12 column composite PK with itself painlessly

I have a table with a composite natural primary key that involves 12 columns (c1, c2, …, c12).

I want to write a self join query:

SELECT *
FROM T1 AS a
JOIN T1 AS b ON a.c1 = b.c1 AND a.c2 = b.c2 AND ... AND a.c12 = b.c12;

This join is a pain to write. Is there a better way?

Answer

Personally, if you must have a 12 column “key”, I would DROP that key, and create a computed column that concatenates all the values, and then create the key on that. This is overly simplified, but this should give you the gist:

CREATE TABLE dbo.YourTable (C1 int NOT NULL,
                            C2 int NOT NULL,
                            C3 int NOT NULL,
                            C4 int NOT NULL,
                            C5 int NOT NULL,
                            C6 int NOT NULL,
                            C7 int NOT NULL,
                            C8 int NOT NULL,
                            C9 int NOT NULL,
                            C10 int NOT NULL,
                            C11 int NOT NULL,
                            C12 int NOT NULL);
GO
--Create a unique index, instead of a PK
CREATE UNIQUE INDEX UQ_YourTable ON dbo.YourTable (C1,C2,C3,C4,C5,C6,C7,C8,C9,C10,C11,C12);
GO
--Add the new column
ALTER TABLE dbo.YourTable ADD K AS CONCAT(C1,C2,C3,C4,C5,C6,C7,C8,C9,C10,C11,C12);
--Set it as the PK 
ALTER TABLE dbo.YourTable ADD CONSTRAINT PK_YourTable PRIMARY KEY (K);

Then your JOIN becomes as simple as:

SELECT *
FROM dbo.YourTable YT1
     JOIN dbo.YourTable YT2 ON YT1.K = YT2.K;

Note: this assumes all your columns are NOT NULL. This will likely not work correctly if they can have NULL values.