?
Solved

What is the difference between these two SQL statments?

Posted on 2005-05-07
6
Medium Priority
?
170 Views
Last Modified: 2010-03-19
Dear all experts,

  I would like to know the difference between the following SQL statements:

  Select *
  From Name N, Class C
  Where C.NameNo = N.Nameno

  Select *
  From Name N join Class C on C.NameNo = N.NameNo

  Which one is faster or they are the same apart from the Syntax?

Thanks in advance
0
Comment
Question by:LegalIT
6 Comments
 
LVL 34

Expert Comment

by:arbert
ID: 13951901
The are the same.  The first query is using "old syntax" and the second query is newer syntax.  They should generate the same plan.....
0
 
LVL 13

Accepted Solution

by:
softplus earned 500 total points
ID: 13951917
The two give identical results.
Using "Join" will give you the results where matching records are in BOTH tables. You can use other join types to get other results (i.e. get names even if they are not in a class). You can check speed by comparing the queries in the MS SQL Query Analyser. Based on my experience, I'd say the speed should be +/- the same as it probably optimises the first query and executes it like the second (you can check this in the Query Analyser as well) -- if you're using indexed fields :)

You might want to look at something like this for more "Join" Info :)
http://www.w3schools.com/sql/sql_join.asp

John
0
 

Author Comment

by:LegalIT
ID: 13951975
Dear softplus, arbert

  How about the querys become as follow?

  Select *
  From Name N, Class C
  Where C.NameNo = N.Nameno
  And n.nameno = 123

   Select *
   From Name N join Class C on C.NameNo = N.NameNo and n.nameno = 123

  Are they still the same?
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 13

Expert Comment

by:softplus
ID: 13951987
Not the "same" (if you take it by the textbook) but optimised to the same plan, i.e. they run the same way on the server - for the server they are the same.
Note: some servers might not optimise this query the same way the MS SQL server does - so if you want to be safe (maybe you want to change to a different server some time or a user uses a different server), stick to the "Join" syntax.
0
 
LVL 34

Expert Comment

by:arbert
ID: 13952007
I would always use the updated join syntax regardless of server--it's just easier to read and debug....
0
 
LVL 9

Expert Comment

by:dbeneit
ID: 13952503
The second query is faster than first ¿true?

test to run query 2 and query 1  then query 1 is faster...
that is because the optimizer has the results of the earlier query to run second query.

test query 1 .... launch others querys and test query 2  ¿?
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

864 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