Geek asks and answers

SQL Server: full external join order?

I have 4 full screen connections in my request and its very slow. So, FULL OUTER JOINdoes order affect performance / result?

FULLY INTERNAL CONNECTION = ⋈

Then

I have a situation: A ⋈ B ⋈ C ⋈ D

All connections are met with a key common to all kcontained in all A, B, C, D

Then:

  • Will ins connections be reordered based on performance?
  • Change the order ⋈ change the result?

I feel that this should not affect the result, but whether it will affect the performance or not, I'm not sure!

Update:

Will SQL Server automatically rebuild connections for better performance if the result set is not order-dependent?

+5
3

, JOIN . MSSQL ( ) , . , - .

, . , . JOIN, MSSQL, ( ). (, FULL JOIN, ) .

FULL JOINS, 4 . . , , -.

+8
  • ⋈ ?

, FULL JOIN , . , , - ( ):

SELECT 
    COALESCE(a.id, b.id, c.id, d.id) AS id,  --- Key columns used in FULL JOIN
    a.*, b.*, c.*, d.*                       --- other columns                 
FROM a 
  FULL JOIN b
      ON b.id = a.id
  FULL JOIN c
      ON c.id = a.id
  FULL JOIN d
      ON d.id = a.id ;

- ( ):

SELECT 
    COALESCE(a.id, b.id, c.id, d.id) AS id,   
    a.*, b.*, c.*, d.*                                   
FROM a 
  FULL JOIN b
      ON b.id = a.id
  FULL JOIN c
      ON c.id = COALESCE(a.id, b.id) 
  FULL JOIN d
      ON d.id = COALESCE(a.id, b.id, c.id) ;
  • ins ?

, COALESCE() , , , , .

+3

Reordering a full outer join should not affect performance or results. The only thing that will depend on the order of the full Outer Join is the default order for columns created using SELECT *. You may have performance issues if you try to make multiple joins with large tables. If there is no where clause to limit tables, you can go through hundreds of thousands of results.

0
source

More articles:


All Articles