473,837 Members | 1,993 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

SQL syntax - INNER or OUTER JOIN vs. WHERE

I apologize if this has been asked before- I searched google but could
not find a concrete answer.

I recently inherited a database whose t-sql code is written in a format
that I find difficult to read (versus the format I have used for
years).

I have tested the queries below using the SQL Profiler, and both have
identical costs. Is there any advantage of one format over the other?

Format 1:
---------
SELECT *
FROM Customers c, Orders o
WHERE c.CustomerID = o.CustomerID
Format 2:
---------
SELECT *
FROM Customers c
INNER JOIN Orders AS o ON c.CustomerID = o.CustomerID
Is it just a matter of personal preference? Does the same hold true for
using OUTER JOIN versus the old *= or =* ?

Thanks,

Matt

Jul 20 '05 #1
3 44538
The two statements you posted are logically identical. There isn't really a
practical reason to prefer one over the other, it's largely a matter of
personal style and readability. Some people prefer the INNER JOIN syntax and
some prefer just to use WHERE.
Does the same hold true for
using OUTER JOIN versus the old *= or =* ?


No. You should never use the *= / =* syntax. This notation was deprecated in
the ANSI SQL92 standard and Microsoft has stated that support for the now
obsolete syntax may be dropped from a future version of SQL Server. The
SQL92 OUTER JOIN syntax is much more powerful because it allows joins to be
based on expressions other than equality (something that wasn't possible
under the old method). Also, under the old syntax some outer join
specifications could have more than one possible interpretation - different
RDBMS products interpreted these queries in different ways, which caused
portability problems.

--
David Portas
SQL Server MVP
--
Jul 20 '05 #2
[posted and mailed, please reply in news]

(mh******@yahoo .com) writes:
I have tested the queries below using the SQL Profiler, and both have
identical costs. Is there any advantage of one format over the other?

Format 1:
---------
SELECT *
FROM Customers c, Orders o
WHERE c.CustomerID = o.CustomerID
Format 2:
---------
SELECT *
FROM Customers c
INNER JOIN Orders AS o ON c.CustomerID = o.CustomerID
Is it just a matter of personal preference?
Yes. I too found the second format bulky and verbose when I first
encountered. These days, it is the only format I use. I find it
much clearer, as I can separate the join conditions from the filter
conditions. And I can't do a cartesian join by mishap, I have to
say CROSS JOIN the few times I need it.

But as long as you are only doing inner joins it is just a matter of
style.
Does the same hold true for using OUTER JOIN versus the old *= or =* ?


No, this is where the map changes completely. David mentioned a few
advantages with the newer syntax, but permit me to add a few more:

* You can do a FULL JOIN.
* You can inner-join the outer table to a lookup table.

There a few gotchas with the new syntax. It is a common error to say:

SELECT ...
FROM A LEFT JOIN B ON A.col = B.col
WHERE B.col = 12

and when wonder where all non-matching rows of A went. The answer is
that the whole FROM constructs a virtual table, and then the WHERE
conditions filter that table. Which means that here all rows with
NULL for B.col goes out the window. The remedy is one of:

SELECT ...
FROM A LEFT JOIN B ON A.col = B.col
WHERE (B.col = 12 OR B.col IS NULL)

SELECT ...
FROM A LEFT JOIN B ON A.col = B.col
AND B.col = 12

To do what I mentioned above, inner-join the outer table to a lookup
table you need a syntax which is not documented in Books Online:

SELECT ...
FROM a LEFT JOIN (b JOIN c ON b.lookupcol = c.lookupcol)
ON a.col = b.col

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #3
Thank you both for the clear explanations. I now see it would be in my
best interest to adopt the SQL92 conventions and adjust my coding
styles appropriately. I must admit the OUTER JOIN syntax seems very
foreign, but I look forward to experimenting with the new
functionality.

Thanks again,

Matt

Erland Sommarskog <es****@sommars kog.se> wrote in message news:<Xn******* *************** @127.0.0.1>...
[posted and mailed, please reply in news]

(mh******@yahoo .com) writes:
I have tested the queries below using the SQL Profiler, and both have
identical costs. Is there any advantage of one format over the other?

Format 1:
---------
SELECT *
FROM Customers c, Orders o
WHERE c.CustomerID = o.CustomerID
Format 2:
---------
SELECT *
FROM Customers c
INNER JOIN Orders AS o ON c.CustomerID = o.CustomerID
Is it just a matter of personal preference?


Yes. I too found the second format bulky and verbose when I first
encountered. These days, it is the only format I use. I find it
much clearer, as I can separate the join conditions from the filter
conditions. And I can't do a cartesian join by mishap, I have to
say CROSS JOIN the few times I need it.

But as long as you are only doing inner joins it is just a matter of
style.
Does the same hold true for using OUTER JOIN versus the old *= or =* ?


No, this is where the map changes completely. David mentioned a few
advantages with the newer syntax, but permit me to add a few more:

* You can do a FULL JOIN.
* You can inner-join the outer table to a lookup table.

There a few gotchas with the new syntax. It is a common error to say:

SELECT ...
FROM A LEFT JOIN B ON A.col = B.col
WHERE B.col = 12

and when wonder where all non-matching rows of A went. The answer is
that the whole FROM constructs a virtual table, and then the WHERE
conditions filter that table. Which means that here all rows with
NULL for B.col goes out the window. The remedy is one of:

SELECT ...
FROM A LEFT JOIN B ON A.col = B.col
WHERE (B.col = 12 OR B.col IS NULL)

SELECT ...
FROM A LEFT JOIN B ON A.col = B.col
AND B.col = 12

To do what I mentioned above, inner-join the outer table to a lookup
table you need a syntax which is not documented in Books Online:

SELECT ...
FROM a LEFT JOIN (b JOIN c ON b.lookupcol = c.lookupcol)
ON a.col = b.col

Jul 20 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
4978
by: Matt | last post by:
Hello I have to tables ar and arb, ar holds articles and a swedish description, arb holds descriptions in other languages. I want to retreive all articles that match a criteria from ar and also display their corresponding entries in arb, but if there is NO entry in arb I still want it to show up as NULL or something, so that I can get the attention that there IS no language associated with that article.
6
9037
by: Thomas Beutin | last post by:
Hi, i've a speed problem withe the following statement: SELECT DISTINCT pz.l1_id, pz.l2_id, pz.l3_id, pz.l4_id FROM ot_adresse AS a, ot_produkt AS p LEFT OUTER JOIN ot_kat_prod AS pz ON ( p.p_id = pz.p_id ) WHERE p.a_id = a.id AND a.id = '105391105424941' AND a.m_id = '37'; This is terrible slow compared to the inner join: SELECT DISTINCT pz.l1_id, pz.l2_id, pz.l3_id, pz.l4_id
2
19265
by: Sri | last post by:
Hi, I am new to DB2 and I am using DB2 UDB 8.1 for Windows. I have a query which works fine in ORACLE and I am trying to find out equivalent query in DB2. SELECT sessi.ses sessions, sp1.speaker speaker1, sp2.speaker speaker2,
3
17844
by: Doug | last post by:
Hi, I'm more familiar with MSSQL than Access syntax and have run into a problem with correctly putting ( )'s around the joins in a 3 table query. I want to INNER JOIN lenders and accounts and LEFT OUTER JOIN that result with prospects. (I want to receive all the results of the inner join and any pertinent info from table 3 that is available.) The way it was written in MSSQL was basically..
3
6645
by: media.opslag | last post by:
Hi, How can i get this to work in access / jet sql ??? Someone?? SELECT tbl1., tbl2. FROM tbl1 left outer join tbl2 on
1
4573
by: Eitan M | last post by:
Hello, I want to do select like this : select t1.col_2 from table_1 t1, table_2 t2 where t1.col_1 = t2.col_1 (+) The above is correct syntax for Oracle. What is the correct syntax for access. (Not the syntax : left outer join table_1 on table_2 etc... , but something
2
8398
by: Rhishabh07 | last post by:
difference between (left, right, inner, outer) join? anybody can help me? Thanx
0
1330
by: gr8white | last post by:
I'm running a query involving an outer join where one of the conditions is that the numeric value of a varchar field is between the numeric values of 2 varchar fields in another table (this has to do with an address range where in some cases the addresses include an alpha character). The query runs fine in the original database but for some reason it returns an "invalid number" error in another db against the exact same data, even though I am...
1
4613
by: teneesh | last post by:
Here I have a code for a view that has been created by a developer on my team. I am trying to use the very same code to create a view for a different formid/quesid. But I cannot figure out how this one starts and ends. can someone please help. here's the code from the developer. SELECT a.EvalRecNo, w1.q1, w2.q2, w3.q3, w4.q4, w5.q5, w6.comment FROM (SELECT DISTINCT u.EvalRecNo FROM dbo.UData AS u...
0
10890
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10581
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10634
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10279
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9416
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7819
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7007
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
1
4479
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3127
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.