[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

The difference in the way to construct a select SQL

Posted on 2007-07-20
11
Medium Priority
?
213 Views
Last Modified: 2010-03-20
1) select s.transno from salesinvoice s join customer c on c.pkey = s.customer
    where s.status = 'NEW' and s.stock = 'PEN'

2) select s.transno from salesinvoice s join customer c on c.pkey = s.customer
    and s.status = 'NEW' and s.stock = 'PEN'

I would like to know what differs between these two sql either in the aspect of performance or result returned.


0
Comment
Question by:ivylnm
10 Comments
 
LVL 33

Expert Comment

by:hongjun
ID: 19536576
(1) is the right way to go. (2) is the old way of "doing" things and there's no guarantee this syntax will be supported in the future.

hongjun
0
 
LVL 33

Expert Comment

by:hongjun
ID: 19536580
The ANSI-92 style INNER JOIN syntax is is recommended in Microsoft SQL
Server. The older join syntax is still accepted and both should provide the
same level of performance and reliability.

In the case of OUTER JOINs, older style joins (*= and =*) are sometimes
ambiguous (unreliable) so the ANSI-92 style OUTER JOIN syntax is strongly
recommended. The older style outer joins are only allowed in databases
with compatibility level lower than 90 and may not be supported in future
SQL Server versions.
0
 
LVL 2

Expert Comment

by:TaeSoft
ID: 19536615
I think the difference between both is that the first quey have to seach for all and the second is faster because you give more parameters and smaller down the search
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 2

Accepted Solution

by:
TaeSoft earned 252 total points
ID: 19536623
The 2nd makes use of the internal index file
0
 

Author Comment

by:ivylnm
ID: 19536632
3) select s.transno from salesinvoice s, customer c
    where c.pkey = s.customer and s.status = 'NEW' and s.stock = 'PEN'

How about this one  ?
Could anyone provide me some sites for these study ? Eventually I am finding the most optimised SQL select statement in order to improve the performance/speed of my current system which involves lots of user transactions.
0
 
LVL 33

Expert Comment

by:hongjun
ID: 19536649
You should use this

select s.transno
from salesinvoice s inner join customer c on c.pkey = s.customer
where s.status = 'NEW' and s.stock = 'PEN'
0
 
LVL 33

Expert Comment

by:hongjun
ID: 19536668
SQL Server Database Coding Conventions, Best Practices, and Programming Guidelines
Use the more readable ANSI-Standard Join clauses instead of the old style joins
0
 
LVL 33

Assisted Solution

by:hongjun
hongjun earned 248 total points
ID: 19536669
SQL Server Database Coding Conventions, Best Practices, and Programming Guidelines
Use the more readable ANSI-Standard Join clauses instead of the old style joins
http://www.sql-server-performance.com/vk_sql_best_practices.asp
0
 
LVL 1

Expert Comment

by:Computer101
ID: 20075437
Forced accept.

Computer101
EE Admin
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question