RIGHT JOIN did not work as expected

Question

I have a question about joining two tables.

Schema

CREATE TABLE [dbo].[DCString] (
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [DCDistributionBoxID] [bigint] NOT NULL,
    [CurrentMPP] [decimal](18, 2) NULL,
    CONSTRAINT [PrimaryKey3] PRIMARY KEY CLUSTERED ( [ID] ASC )
)

ALTER TABLE [dbo].[DCString]
    ADD CONSTRAINT [FK_DCString_DCDistributionBox] FOREIGN KEY([DCDistributionBoxID])
    REFERENCES [dbo].[DCDistributionBox] ([ID])

CREATE TABLE [dbo].[StringData](
    [DCStringID] [bigint] NOT NULL,
    [TimeStamp] [datetime] NOT NULL,
    [DCCurrent] [decimal](18, 2) NULL,
    CONSTRAINT [PrimaryKey4] PRIMARY KEY CLUSTERED ( [TimeStamp] DESC, [DCStringID] ASC)
)

The [StringData] table has the following storage stats:

• Data space: 26,901.86 MB
• Row count: 131,827,749
• Partitioned: true
• Partition count: 62

Usage

I now want to JOIN the data in the [StringData] table with the data from the [DCString] table.

Something like:

declare @begin datetime = '22.02.2016';
declare @end datetime = '23.02.2016';
declare @dcStringID bigint = 6658;

SELECT [DCString].[ID], [StringData].[TimeStamp]
FROM [StringData]
RIGHT OUTER JOIN [StringData] ON [StringData].[DCStringID] = [DCString].[ID]
WHERE [StringData].[ID] = @dcStringID
AND [StringData].[TimeStamp] >= @begin
AND [StringData].[TimeStamp] < @end;

What I expect on searched date range where matching data in the [StringData] table exist is this:

ID   | TimeStamp
6658 | 22.02.2016 10:00:00
6658 | 22.02.2016 11:00:00
6658 | 22.02.2016 12:00:00

...and on a searched date range where no matching data in the [StringData] table exist is this:

ID   | TimeStamp
6658 | NULL

Question

What I get on a searched date range where no matching data in the [StringData] table exist, is a 0 rows result. Why?

I simply want always to get all [DCString].[ID]s. Whats wrong with my JOIN or did I mess up completely?

Update 1 (related to the answer of @Aaron Bertrand):

I tried your way already, but I have to cancel testing the query because after 10min it is still running. It looked like this:

declare @begin datetime = '22.02.2016';
declare @end datetime = '23.02.2016';
declare @dcStringID bigint = 6658;

SELECT [DCString].[ID], [StringData].[TimeStamp]    
FROM [StringData]    
LEFT JOIN [DCString] 
    ON [StringData].[DCStringID] = [DCString].[ID] 
    AND [StringData].[TimeStamp] >= @begin
    AND [StringData].[TimeStamp] < @end
WHERE [StringData].[ID] = @dcStringID;

Answer 1

If I understand your requirements correctly, you want each row in DCString, even when there is no matching row in StringData. The two queries you have now are incorrect:

1. The first one performs DCString RIGHT JOIN StringData - this is backward to what you seem to want (and is one of the reasons most sane people avoid RIGHT JOIN whenever possible).
2. The second query performs StringData LEFT JOIN DCString - this is also backward to what you seem to want. I suggested changing the RIGHT JOIN to a LEFT JOIN, without changing the table order, but you swapped the tables too, for no net change. Why that one is currently not returning could be due to a lot of things, no way for us to speculate. Try and generate an estimated plan and see if you are falling prey to any of these reasons.

Again, based on what I think you want (you'll get better answers if you provide some sample data and desired results):

-- don't use regional, ambiguous formats like dd.mm.yyyy:
-- also you can declare multiple variables in one statement

DECLARE @begin datetime    = '20160222',
        @end   datetime    = '20160223',
        @dcStringID bigint = 6658;

-- don't litter your code with square brackets except where necessary:

SELECT d.ID, s.[TimeStamp] -- bad choice for a column name

-- always use schema prefix!
FROM dbo.DCString AS d
LEFT OUTER JOIN dbo.StringData AS s
  ON d.ID = s.DCStringID
  AND s.[TimeStamp] >= @begin
  AND s.[TimeStamp] <  @end
WHERE d.ID = @dcStringID;

Further reading:

• Ambigous date formats
• Schema prefix

Answer 2

Aaron Bertrand's answer is correct, and his advice is good. Your "update 1" interpretation is not the same as his query. I bet if you copy his code verbatim it will work.

It may help to think of "outer" and "inner" as sets in a Venn diagram with one circle nested inside the other: the outer table is the set that contains the inner table. So when we say R left outer join S, we mean the table on the left, R, is the outer one.

Some people call the outer table the preserved table to reinforce the idea that all its rows are preserved in the JOIN. I've also heard the columns of the outer table referred to as the spine off which "hang" the inner table's data.

The reason to prefer LEFT over RIGHT is psychological. Most people seem to arrange the SELECT to put the outer table's columns first (on the left) and mention the outer table first in the FROM clause. So all the "big stuff" is on the left, and the "might haves" are on the right. I know for myself that whenever someone brings me a RIGHT join that isn't working as expected, the first thing I do is recast it as a LEFT join, and that's often enough to see what's wrong.