I have a SQL Server 2012 database and I'm trying to improve the following sentence:
Select c.CodeId from Code c where c.AggregationLevelId = 2 and CodeId in (select AggregationId from Aggregation where AggregationId not in (select AggregationChildrenId from AggregationChildren))
I want to get the rows in
Code table where its CodeId is in
Aggregation table but it isn't in table
It works but it is very slow.
The relationships between the tables are:
AggregationChildren.AggregationChildrenId are FK to
CREATE TABLE [dbo].[Code] ( [CodeId] INT IDENTITY (1, 1) NOT NULL, [Serial] NVARCHAR (20) NOT NULL, [ ... ] CONSTRAINT [PK_CODE] PRIMARY KEY CLUSTERED ([CodeId] ASC), [ ... ] ) CREATE TABLE [dbo].[Aggregation] ( [AggregationId] INT NOT NULL, [ ... ], CONSTRAINT [PK_AGGREGATIONS] PRIMARY KEY CLUSTERED ([AggregationId] ASC), CONSTRAINT [FK_Aggregation_Code] FOREIGN KEY ([AggregationId]) REFERENCES [dbo].[Code] ([CodeId]) ) CREATE TABLE [dbo].[AggregationChildren] ( [AggregationChildrenId] INT NOT NULL, [ ... ], CONSTRAINT [PK_AGGREGATION_CHILDREN] PRIMARY KEY CLUSTERED ([AggregationChildrenId] ASC), CONSTRAINT [FK_AggregationChildren_Code] FOREIGN KEY ([AggregationChildrenId]) REFERENCES [dbo].[Code] ([CodeId]), [ ... ] )
Is there a better way to improve that select?