Background
I search the data on Stack Exchange Data Explorer .
Problem
I want to select columns from two tables and join them to form one
single table. I choose INNER JOIN to avoid seeing null entries in
the result. The syntax is similar to the following.
Sample SQL Syntax 1
2
3
4
SELECT * FROM Table1
INNER JOIN Table2
ON Table1 . ID = Table2 . ID
WHERE Col1 LIKE 'foo'
Unluckily, when the size of Table1 and Table2 is large, it takes a
while to get the result.
Solution
This page has two more efficient ways. Since I search for
recent data, I adopted the third method in my query .
My Query on Stack Exchange Data Explorer 1
2
3
4
5
6
7
8
9
10
11
DECLARE @ TagLike NVARCHAR ( 25 ) = ## taglike : string ##
SELECT TOP 500 * FROM ( SELECT Id AS [ Post Link ], AnswerCount AS [ Ans ],
CommentCount AS [ Com ], CreationDate , Score AS [ Scr ],
ViewCount AS [ Views ], OwnerUserId FROM Posts
WHERE AnswerCount = 0 AND Tags LIKE '%' + @ TagLike + '%' AND
ClosedDate IS NULL ) AS p
INNER JOIN ( SELECT Id , LastAccessDate , Reputation AS [ Rep ] FROM Users
WHERE LastAccessDate >= '2015-12-01' ) AS u
ON p . OwnerUserId = u . Id
ORDER BY p . Com