RIGHT JOIN did not work as expected 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] &gt;= @begin
AND [StringData].[TimeStamp] &lt; @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] &gt;= @begin
    AND [StringData].[TimeStamp] &lt; @end
WHERE [StringData].[ID] = @dcStringID;
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:


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).
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] &gt;= @begin
  AND s.[TimeStamp] &lt;  @end
WHERE d.ID = @dcStringID;


Further reading:


Ambigous date formats
Schema prefix