January 3, 2014 at 10:28 pm
Please explain following querys, how it works in back end and which one is preferable?
Query 1:
select *
from Mapping_tbl_1 a,Mapping_tbl_2 b
where iccid_trn between b.from and b.to
Query 2:
select *
from Mapping_tbl_1 a
join Mapping_tbl_2 b
on number >= b.from
and number <= b.to
January 4, 2014 at 4:00 am
The first query is an old style ANSI 89 syntax join with the join criteria in the WHERE clause. The second is a modern style join with the join criteria in the join definition. You should use the second method regularly, not the first. It's much more clear, makes the code easier to read, and, when dealing with OUTER type of joins, you have to use the second method anyway. The first won't work for OUTER JOIN.
As to the difference between the BETWEEN statement and using >= <=, usually, but not always, this resolves out to be the same within the query optimizer. You can look at the execution plan to understand what happens in each query. What I see is the BETWEEN operator breaks down to >= <= within the criteria in the execution plan. This means that BETWEEN is just a simpler way to write the same code.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 4, 2014 at 9:26 am
vignesh.ms (1/3/2014)
Please explain following querys, how it works in back end and which one is preferable?Query 1:
select *
from Mapping_tbl_1 a,Mapping_tbl_2 b
where iccid_trn between b.from and b.to
Query 2:
select *
from Mapping_tbl_1 a
join Mapping_tbl_2 b
on number >= b.from
and number <= b.to
My question here would be what table does the column iccid_trn in the first query and number in the second query exist? Deduction would say Mapping_tbl_1 which has a table alias of a. You really should use this table alias on columns that only exist in one of the tables used in a join as well as with those that exist in both tables.
Why you may ask? What happens if a column with the same name in the first table is created in the second table? Your code suddenly starts breaking.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply