Sql-server – Improve select rows from table1 in table2 but not in table3

optimizationsql server

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 AggregationChildren.

It works but it is very slow.

The relationships between the tables are: Aggregation.AggregationId and AggregationChildren.AggregationChildrenId are FK to Code.CodeId.

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?

This is the execution plan for the select:
enter image description here

Best Answer

Unless I'm misunderstanding your question, I would think a combination of EXISTS and NOT EXISTS would give you what you want.

--Setup
set nocount on
DECLARE @Code TABLE (CodeId INT)
DECLARE @Aggregation TABLE (AggregationId INT)
DECLARE @AggregationChildren TABLE (AggregationChildrenId INT)

INSERT INTO @Code (CodeId)
VALUES (1),(2)

INSERT INTO @Aggregation (AggregationId)
VALUES (1),(2)

INSERT INTO @AggregationChildren (AggregationChildrenId)
VALUES (1)

--The query
SELECT c.CodeId
FROM @Code c
WHERE EXISTS (
        SELECT *
        FROM @Aggregation
        WHERE AggregationId = c.codeid
        )
    AND NOT EXISTS (
        SELECT *
        FROM @AggregationChildren
        WHERE AggregationChildrenId = c.codeid
        )

| CodeId |
|--------|
| 2      |
Related Question