CODEWITHSUNDEEP
sql-servert-sql
hidarikani
hidarikani

Reputation: 1131

Store query results or use nested queries

I have a query (simplified), that computes C:

SELECT A + B AS C FROM Foo

The query returns 0.5 million rows.

I need to select rows where C < 0.3. To do this I insert the results into a temporary table #Bar

INSERT INTO #Bar
SELECT A + B AS C FROM Foo

And apply the condition

SELECT *
FROM #Bar
WHERE C < 0.3

According to the execution plan Table Insert costs 86%. Is there a better way to get C < 0.3? I've tried a nested query, and it's the same.

Upvotes: 0

Views: 82

Answers (3)

BI Dude
BI Dude

Reputation: 2016

All solutions work fine above. I just added third option with common table expression that serves a view and it is a bit easier to read.

;WITH CTE_SELECT AS
(
    SELECT A, B, C = A + B
    FROM Foo
    WHERE A + B < 0.3
)

SELECT * FROM CTE_SELECT

Upvotes: 1

Rahul
Rahul

Reputation: 77886

You can do it in a single query like

SELECT C FROM
(
SELECT A + B AS C FROM Foo
) bar
WHERE C < 0.3

(OR) Include the condition in WHERE

SELECT A + B AS C FROM Foo
WHERE A + B < 0.3

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460168

You don't need the temp table at all, just select what you want:

SELECT A, B, C = A + B
FROM Foo
WHERE A + B < 0.3

Upvotes: 4

Related Questions