sql query syntax - joins without 'join' syntax

[djc]

Sample:

select u.username, u.[password], j.job
from userLogon u, Jobs j
where (u.username = j.username) and (u.username = 'certianUserName')

I have seen the above style syntax used in a book (on ado.net actually, but
this is a query specific question, and this group has been very helpful to
me in the past). In most other places I have seen the various 'join'
keywords used.

1) So I guess you don't have to use 'join', 'outer join', 'inner join',
etc... ?
2) would my expample be performing an inner then? (i think so)...
3) which way is better? any advantages/disadvantages? reccomended?
performance?
4) could it be because the author of this book i'm reading comes from a more
programing code rather than sql coding background?
5) how do you do outer joins, for example, using only the where clause and
no 'join' keyword? and what about joining more than 2 tables? can that be
done this way as well? And how about nested joins?

yikes, the more I think about it the more questions I have. I am thinking
that using only the where clause may be easier in my mind...but I need some
examples to look at.

any info would be greatly appreciated. My numbers 2, 3, and 5 above are the
most important to me to find out.

Thanks!

 

[Michel Walsh]

Hi,

In Jet

1- no CROSS JOIN required, LEFT JOIN and RIGHT JOIN are enough (not
LEFT OUTER JOIN), explicit INNER JOIN requires the INNER.

2- indirectly, yes. It is called an implicit inner join through the
WHERE clause. Without the WHERE clause implying two tables, it would have
been a CROSS join.

3- The implicit syntax is recommended:

FROM userLogon AS u INNER JOIN Jobs As j
ON u.username = j.username
WHERE u.username = 'certianUserName'


Note that it does not require much more typing, just re-arrangement.


4- There are other possibilities. Oracle was not supporting the
explicit syntax until very very recently, as example, so the book may
consider that the book should support "old" Oracle code. Joe Celko, not a
two of spades in SQL, often use an implicit join through a where clause...
at least, in the edition I have from his books.


5- You cannot in Jet. In MS SQL Sever, but not recommended at all, it
is to use *= or =* , I think, but then, you are limited to outer EQUI join,
ie, join with a = test. The explicit syntax allows outer non-equi join,
such as u.f1 >= v.f1 (useful to break symmetry, or to compute rank, as
example), or any other "test". In Jet you can use VBA function returning a
Boolean result, in the ON clause, if you wish, or

ON a.word LIKE b.pattern


as example. The explicit syntax is much more versatile, and ... explicit
(simple to understand that is what you want to do). Also remember that a
JOIN is logically performed BEFORE the WHERE clause, so may be faster (that
is important mainly if you use outer joins)






Hoping it may help,
Vanderghast, Access MVP

Sample:

select u.username, u.[password], j.job
from userLogon u, Jobs j
where (u.username = j.username) and (u.username = 'certianUserName')

I have seen the above style syntax used in a book (on ado.net actually,
but
this is a query specific question, and this group has been very helpful to
me in the past). In most other places I have seen the various 'join'
keywords used.

1) So I guess you don't have to use 'join', 'outer join', 'inner join',
etc... ?
2) would my expample be performing an inner then? (i think so)...
3) which way is better? any advantages/disadvantages? reccomended?
performance?
4) could it be because the author of this book i'm reading comes from a
more
programing code rather than sql coding background?
5) how do you do outer joins, for example, using only the where clause and
no 'join' keyword? and what about joining more than 2 tables? can that be
done this way as well? And how about nested joins?

yikes, the more I think about it the more questions I have. I am thinking
that using only the where clause may be easier in my mind...but I need
some
examples to look at.

any info would be greatly appreciated. My numbers 2, 3, and 5 above are
the
most important to me to find out.

Thanks!

 

[Michel Walsh]

Hi,


Horrible typo. In 3, I should have type

the EXPLICIT syntax it recommended


Vanderghast, Access MVP

Hi,

In Jet

1- no CROSS JOIN required, LEFT JOIN and RIGHT JOIN are enough (not
LEFT OUTER JOIN), explicit INNER JOIN requires the INNER.

2- indirectly, yes. It is called an implicit inner join through the
WHERE clause. Without the WHERE clause implying two tables, it would have
been a CROSS join.

3- The implicit syntax is recommended:

FROM userLogon AS u INNER JOIN Jobs As j
ON u.username = j.username
WHERE u.username = 'certianUserName'


Note that it does not require much more typing, just re-arrangement.


4- There are other possibilities. Oracle was not supporting the
explicit syntax until very very recently, as example, so the book may
consider that the book should support "old" Oracle code. Joe Celko, not a
two of spades in SQL, often use an implicit join through a where clause...
at least, in the edition I have from his books.


5- You cannot in Jet. In MS SQL Sever, but not recommended at all,
it is to use *= or =* , I think, but then, you are limited to outer EQUI
join, ie, join with a = test. The explicit syntax allows outer non-equi
join, such as u.f1 >= v.f1 (useful to break symmetry, or to compute rank,
as example), or any other "test". In Jet you can use VBA function
returning a Boolean result, in the ON clause, if you wish, or

ON a.word LIKE b.pattern


as example. The explicit syntax is much more versatile, and ... explicit
(simple to understand that is what you want to do). Also remember that a
JOIN is logically performed BEFORE the WHERE clause, so may be faster
(that is important mainly if you use outer joins)






Hoping it may help,
Vanderghast, Access MVP

Sample:

select u.username, u.[password], j.job
from userLogon u, Jobs j
where (u.username = j.username) and (u.username = 'certianUserName')

I have seen the above style syntax used in a book (on ado.net actually,
but
this is a query specific question, and this group has been very helpful
to
me in the past). In most other places I have seen the various 'join'
keywords used.

1) So I guess you don't have to use 'join', 'outer join', 'inner join',
etc... ?
2) would my expample be performing an inner then? (i think so)...
3) which way is better? any advantages/disadvantages? reccomended?
performance?
4) could it be because the author of this book i'm reading comes from a
more
programing code rather than sql coding background?
5) how do you do outer joins, for example, using only the where clause
and
no 'join' keyword? and what about joining more than 2 tables? can that be
done this way as well? And how about nested joins?

yikes, the more I think about it the more questions I have. I am thinking
that using only the where clause may be easier in my mind...but I need
some
examples to look at.

any info would be greatly appreciated. My numbers 2, 3, and 5 above are
the
most important to me to find out.

Thanks!

 

[djc]

Thank you very much. Very helpful.

Hi,

In Jet

1- no CROSS JOIN required, LEFT JOIN and RIGHT JOIN are enough (not
LEFT OUTER JOIN), explicit INNER JOIN requires the INNER.

2- indirectly, yes. It is called an implicit inner join through the
WHERE clause. Without the WHERE clause implying two tables, it would have
been a CROSS join.

3- The implicit syntax is recommended:

FROM userLogon AS u INNER JOIN Jobs As j
ON u.username = j.username
WHERE u.username = 'certianUserName'


Note that it does not require much more typing, just re-arrangement.


4- There are other possibilities. Oracle was not supporting the
explicit syntax until very very recently, as example, so the book may
consider that the book should support "old" Oracle code. Joe Celko, not a
two of spades in SQL, often use an implicit join through a where clause...
at least, in the edition I have from his books.


5- You cannot in Jet. In MS SQL Sever, but not recommended at all, it
is to use *= or =* , I think, but then, you are limited to outer EQUI join,
ie, join with a = test. The explicit syntax allows outer non-equi join,
such as u.f1 >= v.f1 (useful to break symmetry, or to compute rank, as
example), or any other "test". In Jet you can use VBA function returning a
Boolean result, in the ON clause, if you wish, or

ON a.word LIKE b.pattern


as example. The explicit syntax is much more versatile, and ... explicit
(simple to understand that is what you want to do). Also remember that a
JOIN is logically performed BEFORE the WHERE clause, so may be faster (that
is important mainly if you use outer joins)






Hoping it may help,
Vanderghast, Access MVP

Sample:

select u.username, u.[password], j.job
from userLogon u, Jobs j
where (u.username = j.username) and (u.username = 'certianUserName')

I have seen the above style syntax used in a book (on ado.net actually,
but
this is a query specific question, and this group has been very helpful to
me in the past). In most other places I have seen the various 'join'
keywords used.

1) So I guess you don't have to use 'join', 'outer join', 'inner join',
etc... ?
2) would my expample be performing an inner then? (i think so)...
3) which way is better? any advantages/disadvantages? reccomended?
performance?
4) could it be because the author of this book i'm reading comes from a
more
programing code rather than sql coding background?
5) how do you do outer joins, for example, using only the where clause and
no 'join' keyword? and what about joining more than 2 tables? can that be
done this way as well? And how about nested joins?

yikes, the more I think about it the more questions I have. I am thinking
that using only the where clause may be easier in my mind...but I need
some
examples to look at.

any info would be greatly appreciated. My numbers 2, 3, and 5 above are
the
most important to me to find out.

Thanks!

 

[Michel Walsh]

Hi,

Just re-reading my post... I have problem with implicit and
explicit. In 1- , should be "implicit" inner join do not require the
word INNER (since it is implicitly meant).


V.