Newbie Question re: Where Clause

[xweyer]

What is the difference between the following two statements

"where t1.site_id = t2.site_id"

and

"where t1.site_id* = t2.site_id"

In other words what does the asterisk signify?

 

[SQLBill]

The asterik is actually next to the equal sign with no space between them. That is the 'old' form of joins and I believe indicates a LEFT JOIN (asterick to the left of the equal). I also believe that Oracle still uses that form, but SQL Server is going away from it and eventually may not support it.

-SQLBill

Posting advice: FAQ481-4875

 

[ptheriault]

Actually TSQL 2005 does not support the '*' operator anymore.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[gmmastros]

But... to do this the right way, you need to put that condition in to the join statement, like this...

Select Field1, Field2
From   T1
       Left Join T2
         On T1.Site_id = T2.Site_Id

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

In Katmai (the next version of SQl Server) you can also use MAYBE EXISTS

Select Field1, Field2
From   T1
       WHERE MAYBE EXISTS (select * from  T2
         where T1.Site_id = T2.Site_Id);

That is basically a LEFT JOIN by using EXISTS

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[jbenson001]

You can set the compatiblity leve in 2005 to accept the old syntax.. but this is NOT reccomended...

Just learn the new syntax.. it's no big deal to figure out.

 

[SQLSister]

That left join syntax does not work properly even in 2000. Sometimes it interprets it as a left join and sometimes as a cross join. Do not under any circumstances use this syntax or you will get bad results.

Questions about posting. See faq183-874

 

[xweyer]

Thanks to all for your detailed information. The code I'm trying to interpret was written some years ago which likely explains the outdated syntax.

 

[SQLSister]

Yes, but you need to go through your system and find all those outdated syntax examples and update them as they truly do not give the correct result set all the time.

Questions about posting. See faq183-874