Skip to content
Advertisement

SQL: placement of inner joins and impact of performance and correctness

Is there any difference ( in performance and correctness) between these to SQL (sqls are not important)

SQL no. 1

    SELECT a.test1,
           a.test2,
           a.test3,
           b.test1,
           b.test2,
           b.test3,
           c.test1,
           c.test2,
           c.test3
    FROM table_1 a
    JOIN table_2 b ON a.id = b.id
    AND (a.test1 ="test")
    AND (b.test2 = "test")
    JOIN table_3 c ON c.id2 = b.id2

SQL no. 2

SELECT a.test1,
       a.test2,
       a.test3,
       b.test1,
       b.test2,
       b.test3,
       c.test1,
       c.test2,
       c.test3
FROM table_3 c
JOIN table_2 b ON c.id2 = b.id2
JOIN table_1 a ON a.id = b.id
AND (a.test1 ="test")
AND (b.test2 = "test")

also, table a has 500 000 records, table b has 1000 000 and table c has 1 5000 00 records

Advertisement

Answer

Honestly I don’t think reversing the join statements will make much of a difference. Either way the program will have to combine the data of the table with the data of the two other tables. The calculations that have to be performed don’t seem to be affected by the joins. If the first join changed the length of the data that needed to be analysed to perform the second join, I could see why the query execution time would be affected, as would the results. But aren’t the statements independent of eachother? Measuring time of queries can be done like following in SQL Server Management Studio:

set statistics time on

$query_to_be_measured

set statistics time off
User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement