Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How it works? "Between" and "Non equi join" Expand / Collapse
Author
Message
Posted Friday, January 03, 2014 10:28 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, April 12, 2014 1:21 AM
Points: 115, Visits: 304
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
Post #1527781
Posted Saturday, January 04, 2014 4:00 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:51 AM
Points: 14,788, Visits: 27,264
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1527802
Posted Saturday, January 04, 2014 9:26 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 9:36 AM
Points: 22,474, Visits: 30,145
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1527820
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse