SQL SELECT-IN/JOIN/LEFT JOIN instructions performance evaluation

For example, let's look at few similar tasks:

• Show readers list who took books in the library at least once. (Solve with and without JOIN)
• Show readers list who never took books in the library. (Solve with and without JOIN)

MS SQL examination code

-- Option 1: use JOIN
SELECT DISTINCT [s_id],
                                   [s_name]
FROM [subscribers]
            JOIN [subscriptions]
                ON [s_id] = [sb_subscriber]

-- Option 2: use IN (... DISTINCT ...)
SELECT [s_id],
                [s_name]
FROM [subscribers]
WHERE [s_id] IN (SELECT DISTINCT [sb_subscriber]
                                FROM [subscriptions])

-- Option 3: use IN
SELECT [s_id],
                [s_name]
FROM [subscribers]
WHERE [s_id] IN (SELECT [sb_subscriber]
                                FROM [subscriptions])

-- Option 4: use LEFT JOIN
SELECT [s_id],
                [s_name]
FROM [subscribers]
            LEFT JOIN [subscriptions]
                ON [s_id] = [sb_subscriber]
WHERE [sb_subscriber] IS NULL

-- Option 5: use NOT IN (... DISTINCT ...)
SELECT [s_id],
                [s_name]
FROM [subscribers]
WHERE [s_id] NOT IN (SELECT DISTINCT [sb_subscriber]
                                         FROM [subscriptions])

-- Option 6: use NOT IN
SELECT [s_id],
                [s_name]
FROM [subscribers]
WHERE [s_id] NOT IN (SELECT [sb_subscriber]
                                         FROM [subscriptions])

Median time for one hundred query's execution:

Since most of the results are extremely close in value, there is only one unequivocal conclusion: IN works faster than JOIN, in other cases it is worth conducting additional research.