Madhivanan's TSQL Blog

Getting Started with ASP.NET MVC - Part 6: ASP.NET MVC and Entity Framework

First Time? You can support us by signing up. It takes only 5 seconds. Click here to sign up. If you already have an account, click here to login.


	Blogs

Upload Image

Close it

Select File


	Like

Invite Friends

Browse by Tags · View All

sql_server	217
t-sql	211
tsql	113
sqlserver	94
BRH	78
#SQLServer	65
#TSQL	55
SQL Server	32
function	11
SSMS	9

Archive · View All

August 2007	17
August 2010	8
June 2011	7
November 2007	7
November 2011	6
August 2011	6
October 2011	6
July 2011	6
September 2011	6
December 2011	6

Home · Posts · Most Liked · Most Discussed · Ask a Question · Images · Fans (212) · Preferences

Always use ANSI joins for accuracy

Dec 12 2011 1:59AM by Madhivanan

Suppose there are two tables which have 5 and 4 rows respectively and related by the column id. If you want to join these two tables and get data from table1 you can write old style join like below

create table table1 (id int, names varchar(100))
insert into table1 
select 1,'test1' union all
select 2,'test2' union all
select 3,'test3' union all
select 4,'test4' union all
select 5,'test5' 


create table table2 (id int, amount decimal(12,2))
insert into table2 
select 1,87234.54 union all
select 2,491.90 union all
select 2,20000 union all
select 3,7000 


select 
	t1.* 
from 
	table1 as t1, table2 as t2
where t1.id=t2.id

The above returns 4 rows

But what happens when you forget to match the columns of the two tables?

select 
	t1.* 
from 
	table1 as t1, table2 as t2

It produces 20 rows which is a cartesian product. But when you use ANSI stype join you will not get any unexpected result but an error. Consider these ANSI style syntaxes.

select 
	t1.* 
from 
	table1 as t1 inner join table2 as t2
on t1.id=t2.id

While the above works the following will lead to error

select 
	t1.* 
from 
	table1 as t1 inner join table2 as t2

So this is another reason why you should use ANSI joins

Tags: t-sql, sql_server, sqlserver, tsql, SQL Server, #SQL SERVER, ansi,

Previous: Fun in TSQL - TSQL from TSQL
Next: Question of the month December 2011 - How will you capture multiple error messages?

Madhivanan

4 · 39% · 8850

Liked

Lifesaver

Refreshed

Learned

Incorrect


Like this
Post this to my facebook wall
Tweet this
This was a true life saver
Thanks, this helped me to refresh my memory
Very informative, I just learned something new
I disagree, this information is incorrect


	Submit

2 Comments

Good one Madhi...

Never see the advantage of ANSI joins from this point of view..

commented on Dec 12 2011 11:36PM .

Ramireddy

3 · 39% · 8882

One thing to remember when using outer joins is that the ON predicate is logically processed before the OUTER JOIN puts the rows back in and before the WHERE clause.

SELECT
 t1.Name,
 t2.Amount
FROM
 t1 LEFT OUTER JOIN t2 ON t1.ID = t2.ID AND t2.ID = 3

Which returns 5 rows and an amount (7000) for row with the name 'test3'. is logically different than

SELECT
     t1.Name,
     t2.Amount
FROM
     t1 LEFT OUTER JOIN t2 ON t1.ID = t2.ID 
WHERE
    t2.ID = 3

Which only returns one row with a name ('test3') and an amount (7000). Moving the filtering criteria to the ON clause preserves the records from t1. Here is a better explanation

Interesting Observation of ON Clause on LEFT JOIN – How ON Clause affects Resultset in LEFT JOIN

commented on Jan 6 2012 8:39AM .

Madhivanan's TSQL Blog

Always use ANSI joins for accuracy

2 Comments

Your Comment