473,372 Members | 900 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,372 software developers and data experts.

difference between explicit inner join and implicit

Is there any difference between explicit inner join and implicit
inner join

Example of an explicit inner join:

SELECT *
FROM employee
INNER JOIN department
ON employee.DepartmentID = department.DepartmentID

Example of an implicit inner join:

SELECT *
FROM employee, department
WHERE employee.DepartmentID = department.DepartmentID
Mar 31 '08 #1
11 19915
On Mar 31, 1:02 pm, YZXIA <yz...@hotmail.comwrote:
Is there any difference between explicit inner join and implicit
inner join
The execution plan for both queries is the same, so that suggests to
me that they are functionally identical.
Mar 31 '08 #2
Is there any difference between *explicit inner join and implicit
inner join
Technically no, but it's a good idea to make it a habit to use the
explicit inner join. It is considered the standard nowadays, is
easier to read and debug, and is consistent with the OUTER JOIN
syntax.

Mar 31 '08 #3
--CELKO-- wrote:
The real difference is in the mindset of programmers. Those that
write with infixed notation thing in terms of a linear sequence of
joins, as if they were limited to simple binary Theta operators. The
programmers that use the older notation will use BETWEEN, IN () and
other predicates that work with multiple terms.
Like this, you mean? And are you recommending or deprecating it?

select a.x, b.y
from table1 a
join table2 b on a.x between b.y and 500
Apr 1 '08 #4
>Technically no, but it's a good idea to make it a habit to use the explicit inner join. It is considered the standard nowadays, <<

Not when I was on the Standards Committee; did you join later than
me?
>is easier to read and debug, <<
NO, it isn't; have you seen any of the human factors research? The ON
clauses can be spread so far from the matching tables debugging time
increases. Multiple parameter predicates like BETWEEN and IN are
split and their higher level meaning is lost.

But it looks like ACCESS and has a nice binary operator feel that
procedural programmers like.
>and is consistent with the OUTER JOIN syntax. <<
Yes, that is the reason it exists.

Apr 1 '08 #5
On Tue, 1 Apr 2008 07:01:14 -0700 (PDT), --CELKO-- wrote:
>>Like this, you mean? <<

No, try this:

SELECT ..
FROM A, B, C
WHERE A.x BETWEEN B.y AND C.z;

Versus:

SELECT ..
FROM A
INNER JOIN
B
ON A.x >= B.y
INNER JOIN
C
ON C.z <= A.x;

The "between" is lost in what typographers call the law of proximity
because things are split into binary operators.
Hi Joe,

And both versions hide the actual relationship between B and C.

SELECT ..
FROM B
INNER JOIN C
ON C.z >= B.y
INNER JOIN A
ON A.x BETWEEN B.y AND C.z;

Now we have the "between", *and* the relationship between B and C is
shown explicitly. Looks like a winner to me!

(Though I have to admit that I'd be hardpressed to come up with an
example that makes it even vaguely believeable that a query such as this
would ever show up in the real world).

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Apr 1 '08 #6
>Now we have the "between", *and* the relationship between B and C is shown explicitly. Looks like a winner to me! <<

No, no! What you have done is hide redundancy which would have been
clearly shown in a simple WHERE clause. I don't think I am the only
guy who puts all the WHERE clause predicates into a tool to sort them
to see exactly what predicates go to which tables.

>(Though I have to admit that I'd be hard pressed to come up with an example that makes it even vaguely believable that a query such as this would ever show up in the real world). <<
LOL! We have seen much worse in this newsgroup!
Apr 3 '08 #7
If anyone is willing to help a novice,

is there any difference between the JOIN operator and the INNER JOIN
operator?

For example, in a sample database, I get identical results when I run

SELECT title, name
FROM books JOIN publisher
USING (pubid)

and

SELECT title, name
FROM books INNER JOIN publisher
USING (pubid)

Thanks,

Neil

*** Sent via Developersdex http://www.developersdex.com ***
Jun 27 '08 #8
On Apr 12, 7:22 pm, Neil H <trumbol...@yahoo.comwrote:
If anyone is willing to help a novice,

is there any difference between the JOIN operator and the INNER JOIN
operator?

For example, in a sample database, I get identical results when I run

SELECT title, name
FROM books JOIN publisher
USING (pubid)

and

SELECT title, name
FROM books INNER JOIN publisher
USING (pubid)
No, there is no difference. They are the same. 'INNER' is optional:
the default JOIN is INNER JOIN.

Whether you write INNER or not is a matter of style.
Jun 27 '08 #9
Neil H (tr********@yahoo.com) writes:
If anyone is willing to help a novice,

is there any difference between the JOIN operator and the INNER JOIN
operator?

For example, in a sample database, I get identical results when I run

SELECT title, name
FROM books JOIN publisher
USING (pubid)

and

SELECT title, name
FROM books INNER JOIN publisher
USING (pubid)
No, "JOIN" is just short for "INNER JOIN".

However, the USING syntax does not appear in MS SQL Server (which is the
topic for this newsgroup), so you seem be using a different product.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jun 27 '08 #10
INNER is a shorthand for INNER JOIN, and USING is a shorthand for an
equi-join. Nobody really likes USING, since adding more tables and re-
compiling can give you surprises.
Jun 27 '08 #11
Technically no, but it's a good idea to make it a habit to use the explicit inner join. *It is considered the standard nowadays, <<

Not when I was on the Standards Committee; did you join later than
me?
The INNER JOIN syntax is the current standard. It is considered
"standard" by those who actually work in the field. How long ago was
it that you were on the Standards Committee?

You seriously need to stop pulling this "I was on the committee" crap
in place of having a valid argument. The fact of the matter is, the
vast majority of us are using the INNER JOIN syntax. If you feel like
doing things the old way, whatever.
is easier to read and debug, <<

NO, it isn't; have you seen any of the human factors research? *
YES, it is. That is one of the main reasons why people who actually
work in the field have so widely adopted it. But just for fun, why
don't you post a link to the research that shows that the syntax
practically *everyone* has adopted with open arms is so much worse
than the syntax we all (just as willingly) dropped.
The ON clauses can be spread so far from the matching tables debugging time
increases. *Multiple parameter predicates like BETWEEN and IN are
split and their higher level meaning is lost.
Debugging time is actually diminished because the join criteria is not
mixed in with the filtering criteria. If you find yourself "losing"
predicates then maybe the problem is with you and not the syntax.
But it looks like ACCESS and has a nice binary operator feel that
procedural programmers like.
I could care less what looks like ACCESS. I don't personally use
ACCESS; have never been a fan.
and is consistent with the OUTER JOIN syntax. <<

Yes, that is the reason it exists.
Whatever the reason, the fact is it is currently the accepted industry
standard. If you do not realize/understand this, that is your issue.
Jun 27 '08 #12

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

Similar topics

5
by: Neil Rutherford | last post by:
During testing of an application, i noticed a difference between SQL 2000 and SQL 7, both with identical config. In a nutshell: A table has a trigger for UPDATE and DELETE. When a column in the...
3
by: Beringer | last post by:
I have the following query in Access: SELECT a.interest_parent_id, a.interest_element_id, b.alr_category_id AS interest_category_id, a.allergy_parent_id, a.allergy_element_id, c.alr_category_id...
7
by: yufufi | last post by:
lets say we have a 'shape' class which doesn't implement IComparable interface.. compiler doesn't give you error for the lines below.. shape b= new shape(); IComparable h; h=(IComparable)b;...
24
by: Peter Maas | last post by:
The Python FAQ 1.4.5 gives 3 reasons for explicit self (condensed version): 1. Instance variables can be easily distinguished from local variables. 2. A method from a particular class can be...
9
by: HC | last post by:
Hello, all, I started out thinking my problems were elsewhere but as I have worked through this I have isolated my problem, currently, as a difference between MSDE and SQL Express 2005 (I'll just...
6
by: Nishu | last post by:
Hi all, What is difference between typecasting and casting? I used to think that both are same; but few days back someone pointed out here that these are different. Vague guess, Is it that...
3
by: Smita Kashyap | last post by:
What is the main difference between Full Join and Inner Join in SQl Server 2005 ? As inner join retrive all records that match certain condition, similiary in full join will rreturn all records...
3
nikpreek
by: nikpreek | last post by:
Hi All, I know its very basic question and event I know the answer for this. But I haven't found any proof of this. Is there any difference between thse queries (given below at end of post)? (In...
9
by: shapper | last post by:
Hello, I am used to SQL but I am starting to use LINQ. How can I create Left, Right and Inner joins in LINQ? How to distinguish the different joins? Here is a great SQL example:...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 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 former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

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.