SQL Designer Join Order Query

Category: sql server tools

Question

KelvinClark on Thu, 27 Sep 2018 15:49:59


Hi All,

I have a question I wonder if anyone can answer? Some colleagues of mine tend to use SQL Query Designer in SQL 2016 which works well for the most part. 

Where we have issues is with the query designer adding right outer instead of left outer joins. For some reason, designer changes the type of join when we add multiple joins. Ideally everything would be left outer joins to help with performance, but as you can see, as soon as we add the second table join "Class_Value", this is the first thing in the join to be referenced.

This demo shows what I mean, everything is a left outer from the person table. 

Now when I add in the Class_Value table, the first join is a right outer from the Class_Value table instead of multiple left outer joins to that table.

The SQL for the second query should be this below, but it instead uses a right outer?


SELECT        dbo.Country.Country, dbo.[Group].Group_Name, dbo.Class.Class_Name, dbo.Class_Value.Value
FROM            dbo.Person

LEFT OUTER JOIN
                         dbo.Country ON dbo.Person.Country_Code = dbo.Country.Country_Code LEFT OUTER JOIN
                         dbo.[Group] ON dbo.Person.Group_ID = dbo.[Group].Group_ID

LEFT OUTER JOIN
                         dbo.Class ON dbo.Person.Class_ID = dbo.Class.Class_ID

LEFT OUTER JOIN
                         dbo.Class_Value ON dbo.Class.Class_Name = dbo.Class_Value.Class_Name

So my question is, is there a way to tell designer which joins the tables should use without having to re-write T-SQL?


Kind regards, Kelvin.


Replies

Olaf Helper on Thu, 27 Sep 2018 16:21:13


Where we have issues is with the query designer join order.

Hello Kelvin,

Why is the order of tables/joins important for you? For SQL Server query execution the order doesn't matter in any way.

If you want to have an order, then write the SQL for the view instead of using the designer.

Visakh16 on Thu, 27 Sep 2018 16:31:55


Hi All,

I have a question I wonder if anyone can answer? Some colleagues of mine tend to use SQL Query Designer in SQL 2016 which works well for the most part. 

Where we have issues is with the query designer join order. For some reason, designer changes the order of the join when we add multiple joins. Ideally everything would be left outer joins to help with performance, but as you can see, as soon as we add the second table join "Class_Value", this is the first thing in the join to be referenced.

This demo shows what I mean, everything is a left outer from the person table. 

Now when I add in the Class_Value table, the first join is a right outer from the Class_Value table instead of multiple left outer joins to that table.

So my question is, is there a way to tell designer the order of the tables without having to re-write T-SQL?

Many thanks for reading.

Kind regards, Kelvin.

I think you missed attaching the screenshot or it didnt get uploaded successfully.

To answer your question, order doesnt matter as long as the JOIN types remain the same. The execution plan would remain the same and code will execute the same way as well


KelvinClark on Thu, 27 Sep 2018 19:29:00


Thanks for your reply

I've explain it wrong. Please see my revised question. 

KelvinClark on Thu, 27 Sep 2018 19:30:06


Thanks for your reply

I've explain it wrong. Please see my revised question. 

The screenshots are available for me to view, can you still not see them?

Erland Sommarskog on Thu, 27 Sep 2018 21:36:47


From a performance perspective, it should not matter. From a maintenance perspective on the other hand... I get a headache from right joins, and I never use them.

Why the Query Designer messes things up I have no idea. I'm afraid that I have no kind words for this tool. It's formatting and the fact that it refuses to use aliases makes the query hard to read. And there are a lot query constructs it can't handle. Best is to avoid it. This was just another nail in the coffin.