If you have legacy code that you are upgrading, the Query Designer can convert old style joins to ANSI joins. You can invoke the Query Designer from the Query menu in the SSMS toolbar or by pressing the Ctrl Shift Q three key combination, which is what I recommend. If you select the query you want to convert before pressing Ctrl Shift Q, the query will already be in the Query Designer window when it pops up.
Here's some sample code to experiment with:
create
table table1 (
a int
,b int
);
create
table table2 (
a int
,b int
);
create
table table3 (
a int
,b int
);
insert
into table1 (a,b) values (1,2);
insert into table2 (a,b) values (1,2);
insert into table3 (a,b) values (1,2);
insert
into table1 (a,b) values (11,12);
insert into table2 (a,b) values (21,22);
insert into table3 (a,b) values (31,32);
-- Select the following query and then press Ctrl Shift Q to invoke the Query Designer
select
t1.a as t1a, t2.b as t2b, t3.a as t3a, t3.b as t3b
from table1 t1, table2 t2, table3 t3
where t1.a = t2.a
and t2.b = t3.b;
Figure 1. Query Designer window with non-ANSI joins converted to ANSI joins.
-- Select the following query and then press Ctrl Shift Q to invoke the Query Designer
select
t1.a as t1a, t2.b as t2b, t3.a as t3a, t3.b as t3b
from table1 t1, table2 t2, table3 t3
where t1.a = t2.a
and t2.b = t3.b
and t1.a = 1
and t2.b = 2
and (t3.a = 1 or t3.b = 2);
Figure 2. Notice that the WHERE clause has more predicates than we started with because of the OR.
-- Don't forget to clean up your mess!
drop
table table1;
drop table table2;
drop table table3;
The Query Designer does a good job converting the first select statement. It doesn't do such a great job on the second select. The OR clause causes the Query Designer to introduce unnecessary complexity into the code.
I did real world performance testing of code matching the general pattern of the example select statement shown above having the OR clause. The generated code with the OR clause was less efficient than code I manually converted to ANSI inner joins. Original code without an OR in a compound predicate was fine.
Used with your critical thinking skills, Ctrl Shift Q can help you when refactoring legacy code. It works well most of the time. Hopefully you now have an understanding of when it might be appropriate and when it might not be.