Optimize/Shorten SQL Queries

Question

Is there a way to Optimize/Shorten my query below:

DROP TABLE IF EXISTS #TempTable1

SELECT 'Candidate1' AS 'Candidate', A.DisplayName, A.Location, A.AccountId, F.Title, F.Body, F.Tags
INTO #TempTable1
FROM [Users] A
LEFT JOIN [Badges] B ON A.Id = B.Id
LEFT JOIN [Comments] C ON A.Id = C.Id AND C.UserId = B.UserId
LEFT JOIN [LinkTypes] D ON A.Id = D.Id
LEFT JOIN [PostLinks] E ON A.Id = E.Id AND E.PostId = C.PostId
LEFT JOIN [Posts] F ON A.Id = F.Id
LEFT JOIN [PostTypes] G ON A.Id = G.Id
LEFT JOIN [Votes] H ON A.Id = H.Id AND H.UserId = B.UserId AND H.PostId = E.PostId
LEFT JOIN [VoteTypes] I ON A.Id = I.Id
WHERE A.DisplayName LIKE 'Jeff%'AND F.Title = 'Director'

UNION ALL

SELECT 'Candidate2' AS 'Candidate', A.DisplayName, A.Location, A.AccountId, F.Title, F.Body, F.Tags
FROM [Users] A
LEFT JOIN [Badges] B ON A.Id = B.Id
LEFT JOIN [Comments] C ON A.Id = C.Id AND C.UserId = B.UserId
LEFT JOIN [LinkTypes] D ON A.Id = D.Id
LEFT JOIN [PostLinks] E ON A.Id = E.Id AND E.PostId = C.PostId
LEFT JOIN [Posts] F ON A.Id = F.Id
LEFT JOIN [PostTypes] G ON A.Id = G.Id
LEFT JOIN [Votes] H ON A.Id = H.Id AND H.UserId = B.UserId AND H.PostId = E.PostId
LEFT JOIN [VoteTypes] I ON A.Id = I.Id
WHERE A.DisplayName LIKE 'ANA%' AND A.AccountId = '3125156'

UNION ALL

SELECT 'Candidate3' AS 'Candidate', A.DisplayName, A.Location, A.AccountId, F.Title, F.Body, F.Tags
FROM [Users] A
LEFT JOIN [Badges] B ON A.Id = B.Id
LEFT JOIN [Comments] C ON A.Id = C.Id AND C.UserId = B.UserId
LEFT JOIN [LinkTypes] D ON A.Id = D.Id
LEFT JOIN [PostLinks] E ON A.Id = E.Id AND E.PostId = C.PostId
LEFT JOIN [Posts] F ON A.Id = F.Id
LEFT JOIN [PostTypes] G ON A.Id = G.Id
LEFT JOIN [Votes] H ON A.Id = H.Id AND H.UserId = B.UserId AND H.PostId = E.PostId
LEFT JOIN [VoteTypes] I ON A.Id = I.Id
WHERE A.DisplayName LIKE 'Peter%' AND A.Location = 'USA'

SELECT * FROM #TempTable1

Ideally, I will have different criteria for each candidate and will insert it into a temporary table for consolidation, but all of them have the same columns.

If there is a way I could shorten my query into this:

DROP TABLE IF EXISTS #TempTable1

SELECT 'Candidate1' AS 'Candidate', A.DisplayName, A.Location, A.AccountId, F.Title, F.Body, F.Tags
INTO #TempTable1
FROM [Users] A
LEFT JOIN [Badges] B ON A.Id = B.Id
LEFT JOIN [Comments] C ON A.Id = C.Id AND C.UserId = B.UserId
LEFT JOIN [LinkTypes] D ON A.Id = D.Id
LEFT JOIN [PostLinks] E ON A.Id = E.Id AND E.PostId = C.PostId
LEFT JOIN [Posts] F ON A.Id = F.Id
LEFT JOIN [PostTypes] G ON A.Id = G.Id
LEFT JOIN [Votes] H ON A.Id = H.Id AND H.UserId = B.UserId AND H.PostId = E.PostId
LEFT JOIN [VoteTypes] I ON A.Id = I.Id
WHERE A.DisplayName LIKE 'Jeff%' AND F.Title = 'Director'

UNION ALL

SELECT 'Candidate2' AS 'Candidate', *
FROM [Users] A
WHERE A.DisplayName LIKE 'ANA%' AND A.AccountId = '46532'

UNION ALL

SELECT 'Candidate3' AS 'Candidate', *
FROM [Users] A
WHERE A.DisplayName LIKE 'Peter%' AND A.Location LIKE '%USA%'

So it will not be confusing when maintaining this query by other users.

Answer 1

The obvious solution is to use AND/OR logic with a row number as follows:

SELECT
  'Candidate' + CONVERT(VARCHAR(3), ROW_NUMBER() OVER ()) AS [Candidate]
  , A.DisplayName, A.Location, A.AccountId, F.Title, F.Body, F.Tags
INTO #TempTable1
FROM [Users] A
LEFT JOIN [Badges] B ON A.Id = B.Id
LEFT JOIN [Comments] C ON A.Id = C.Id AND C.UserId = B.UserId
LEFT JOIN [LinkTypes] D ON A.Id = D.Id
LEFT JOIN [PostLinks] E ON A.Id = E.Id AND E.PostId = C.PostId
LEFT JOIN [Posts] F ON A.Id = F.Id
LEFT JOIN [PostTypes] G ON A.Id = G.Id
LEFT JOIN [Votes] H ON A.Id = H.Id AND H.UserId = B.UserId AND H.PostId = E.PostId
LEFT JOIN [VoteTypes] I ON A.Id = I.Id
WHERE (A.DisplayName LIKE 'Jeff%' AND F.Title = 'Director')
OR (A.DisplayName LIKE 'ANA%' AND A.AccountId = '46532')
OR (A.DisplayName LIKE 'Peter%' AND A.Location LIKE '%USA%');

Clearly you never could have a different column name for the candidate e.g. 'Candidate1', 'Candidate1' etc and nor would you single quote the column name in SQL Server.

If you prefer a UNION ALL solution then you can insert the bare minimum into another temp table (or table variable) and then use that to join onto all the rest of your tables e.g.

SELECT 'Candidate1' AS [Candidate], A.AccountId
INTO #MyNewTempTable
FROM [Users] A

UNION ALL

SELECT 'Candidate2', A.AccountId
FROM [Users] A
WHERE A.DisplayName LIKE 'ANA%' AND A.AccountId = '46532'

UNION ALL

SELECT 'Candidate3', A.AccountId
FROM [Users] A
WHERE A.DisplayName LIKE 'Peter%' AND A.Location LIKE '%USA%';