SQL Performance Inner Join

Question

Let me ask you something I've been thinking about for a while. Imagine that you have two tables with data:

MAIN TABLE (A)

| ID | Date | |:-----------|------------:| | 1 | 01-01-1990| | 2 | 01-01-1991| | 3 | 01-01-1992| | 4 | 01-01-2000| | 5 | 01-01-2001| | 6 | 01-01-2003|

SECONDARY TABLE (B)

| ID | Date | TOTAL | |:-----------|------------:|--------:| | 1 | 01-01-1990| 1 | | 2 | 01-01-1991| 2 | | 3 | 01-01-1992| 1 | | 4 | 01-01-2000| 5 | | 5 | 01-01-2001| 8 | | 6 | 01-01-2003| 7 |

and you want to select only ID with date greater than 31-12-1999 and get the following columns: ID, Date and Total. For that we have many options but my question would be which of the following would be better in terms of performance:

OPTION 1

With main as( select id, date from A where date > '31-12-1999' ) select main.id, main.date, B.total from main inner join B on main.id = b.id

OPTION 1

With main as( select id, date from A where date > '31-12-1999' ), secondary as ( select id, total from B where date > '31-12-1999' ) select main.id, main.date, secondary.total from main inner join secondary on main.id = b.id

Which of both queries would be better in terms of performance? Thanks in advance!

DATE FOR BOTH TABLES MEANS THE SAME

Fahmi · Accepted Answer · 2020-09-27 18:15:50Z

1

You don't need to use CTE you can directly join two tables -

select A.id, A.date, B.total from A inner join B on A.id = b.id where A.date > '31-12-1999'

edited Sep 27, 2020 at 18:15

answered Sep 27, 2020 at 18:07

Fahmi

37.5k5 gold badges26 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Daniel Over a year ago

Thanks Fahmi. In that case it will throw an error because we you need to specify the table for the date filter. Hope it helps!

Daniel Over a year ago

Would it be better to filter on the second table as well? Or is the inner join executed firstly?

Fahmi Over a year ago

join would execute first @Daniel

Gordon Linoff · Accepted Answer · 2020-09-27 18:08:10Z

0

You would need to test on your data. But there is really no need for CTEs:

select a.id a.date, b.total from a inner join b on a.id = b.id where a.date > '1999-12-31' and b.date > '1999-12-31';

As for your specific question, the two queries are not the same, because the first is filtering on only one date and the second is filtering on two dates. You should run the query that implements the logic that you intend.

answered Sep 27, 2020 at 18:08

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

3 Comments

Daniel Over a year ago

Thanks for the quick reply. Probably there is no need for CTEs as you said but it was just an example. My question would be: Would it be better to filter on the second table with the date even thought the inner join will get the same result? Date for boths tables mean the same

Gordon Linoff Over a year ago

@Daniel . . . The two queries are different. Use the one that implements the logic you need. You either need to filter on one table or both tables . . . or you need to fix your data model so you don't have redundant data. If the id/date values are the same in both tables, no join at all is needed, and that will be faster.

Daniel Over a year ago

Thanks Gordon. I didn't want to give an actual example because it would be longer to explain but imagine that you have that situation. Even the inner join will give the desired result, would it be better to use a where clause to reduce the amount of data we read or the inner join is executed firstly so it doesnt matter?

Collectives™ on Stack Overflow

SQL Performance Inner Join

2 Answers 2

3 Comments

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

3 Comments

Related