SQL Server performance : Join or Where

Question

I have a question which has come up and want to make sure I am doing this the best way. If I have three columns:

Table: Invoices

InvoiceNumber
InvoiceAmount
InvoiceDate

I then have a temp table that has the same three fields where I will have invoice records all for the the same InvoiceDate.

I need to update the amount of the Invoices table based on InvoiceNumber and InvoiceDate.

Is it best to just join on the two columns, or what I am doing is using the InvoiceDate in the Where clause as the main Invoices table is really large and I thought that where filter is better. There will only be one record per InvoiceNumber and InvoiceDate but I was thinking the filter down of just that date then join on the invoice number and the Update statement would be more performant.

What is the better approach?

When you create your temp table you might benefit from adding a index after you insert data. — Ross Bush, Aug 09 '20 at 03:29
Please show both queries and the execution plan for each since performance advice is asked for. — Dale K, Aug 09 '20 at 03:39

score 0 · Accepted Answer · answered Aug 09 '20 at 03:23

0

Check here Is a JOIN faster than a WHERE?

So there shouldn’t be a difference. What will make a difference is how you write your queries as well as how you index your tables.

answered Aug 09 '20 at 03:23

Slayton

49
3

score -1 · Answer 2 · edited Aug 09 '20 at 05:47

-1

In general, there is not any huge difference between Join and Where clause.

It seems you want to use the best practices then I will suggest use common table expression (and not temp tables)

Why?

Because temp tables are physically created in the tempdb database. CTE can only be used in the current query scope.

CTE improves readability and ease in maintenance of complex queries and sub-queries. you can use joins or where clause in CTE

edited Aug 09 '20 at 05:47

Dale K

25,246
15
42
71

answered Aug 09 '20 at 05:33

Rajeev Bera

2,021
1
16
30

3

The problem is "in general" doesn't work with SQL Server - performance is highly specific to the scenario. And as for generically suggesting CTEs over temp tables - they have quite different use cases, yes there is some overlap, but often there is not. Both CTEs and temp tables are tools that every SQL developer needs in their toolbox. Whether one is better than the other is again highly situation dependent. Actually all we can say about this question is that OP hasn't provided enough detail to answer. – Dale K Aug 09 '20 at 05:49
Hey @DaleK, I appreciate your thought process. well explained +1 – Rajeev Bera Aug 09 '20 at 20:34

SQL Server performance : Join or Where

2 Answers2