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
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;
[StringData] RIGHT OUTER JOIN [StringData]
i.e. a self-join. Is this intended? It seems to contradict the preceding sentence. – Michael Green Feb 20 '16 at 9:47