It's more a time / aesthetics way of doing things. The SQL 1 standard
didn't support join, inner join, or outer join in the way we use them
now, and each vendor started to cook up their own way of doing it. When
the ANSI 92 syntax was brought about, they needed to come up with a way
to standardize joining tables that didn't break the proprietary methods
vendors had invented, so they moved it up into the FROM clause via the
JOIN keywords we use now.
In theory, using the ANSI 92 syntax (not using the WHERE clause) will
create SQL that could port better to other RDBMS.
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Wednesday, June 16, 2004 6:53 AM
To: CF-Talk
Subject: RE: SQL query style (WAS: SQL search query)
James,
Not sure about the join fashion trend (I use the WHERE clause),
but I'm pretty sure using cfqueryparam increases query speeds and
improves security (from unautorised users:
cfqueryparam
Verifies the data type of a query parameter and, for DBMSs that
support bind variables, enables ColdFusion to use bind variables in the
SQL statement. Bind variable usage enhances performance when executing a
cfquery statement multiple times.
This tag is nested within a cfquery tag, embedded in a query SQL
statement. If you specify optional parameters, this tag performs data
validation.
Macromedia recommends that you use the cfqueryparam tag within
every cfquery tag, to help secure your databases from unauthorized
users.
From
http://livedocs.macromedia.com/coldfusion/6.1/htmldocs/tags-b20.htm
-----Original Message-----
From: James Smith [mailto:[EMAIL PROTECTED]
Sent: 16 June 2004 11:47 am
To: CF-Talk
Subject: SQL query style (WAS: SQL search query)
While we are on the subject I have noticed recently that more
and ore people
are joining tables using the...
FROM table1 x JOIN table2 y ON x.ID = y.ID
And I am wondering if there is a reason for this. Sure I use
this syntax
for my outer joins but inner joins I still do the old fashioned
way in the
where clause, for example
FROM table1 x, table2 y
WHERE x.ID = y.ID
This has the added benefit of taking the place of the 'WHERE
0=0' line we
recently discussed. What are the benefits of one form of inner
join over
the other or is it just today's SQL fashion trend?
Secondly I notice more and more use of the cfqueryparam tag,
what is wrong
with simply using
WHERE x.ID = #form.id#
AND y.var = '#url.string#'
type formatting? I have been doing this for about 5 years now
and have
NEVER found a need for the cfqueryparam tag, I just find it
makes the code
harder to read.
Comments anyone?
--
Jay
_____
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
