There is no reason to, unless you need to make sure that you will
get all the relevant records from one or more of the tables
regardless of whether they have corresponding records in the
other tables. That's what an outer join is for.
If you put a row into JOBINFO with no corresponding rows in
the ACTIVITYLOG, the 2 queries will return different data.
If that's an impossible situation, then the second query is fine.
Nick
-----Original Message-----
From: Chad Gray [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, September 18, 2001 6:45 PM
To: CF-Talk
Subject: SQL question about JOIN
The first query takes 9seconds to process, the 2second query takes
1seconds to process.
They return the same data. Why should you use an LEFT OUTER JOIN, or any
JOIN for that matter when you can say WHERE JOBINFO.JOBNUM =
ACTIVITYLOG.JOBNUM???
<cfquery name="ACTIVITIES" datasource="servalent" dbtype="ODBC">
SELECT JOBINFO.JOBNUM, JOBINFO.JOBNAME, JOBINFO.COMPANYNAME,
ACTIVITYLOG.LOGIN, ACTIVITYLOG.LOGOUT, ACTIVITYLOG.EMPLOYEENAME,
ACTIVITYLOG.EMPLOYEENUM, ACTIVITYLOG.ACTIVITYDESCRIPTION
FROM JOBINFO LEFT OUTER JOIN ACTIVITYLOG ON JOBINFO.JOBNUM =
ACTIVITYLOG.JOBNUM
WHERE ACTIVITYLOG.EMPLOYEENUM = #EMPLOYEENUM# AND
ACTIVITYLOG.LOGOUT is null AND
ACTIVITYLOG.LOGIN is not null
ORDER BY JOBINFO.JOBNUM, ACTIVITYLOG.LOGIN
</cfquery>
<cfquery name="ACTIVITIES" datasource="servalent" dbtype="ODBC">
SELECT JOBINFO.JOBNUM, JOBINFO.JOBNAME, JOBINFO.COMPANYNAME,
ACTIVITYLOG.LOGIN, ACTIVITYLOG.LOGOUT, ACTIVITYLOG.EMPLOYEENAME,
ACTIVITYLOG.EMPLOYEENUM, ACTIVITYLOG.ACTIVITYDESCRIPTION
FROM JOBINFO, ACTIVITYLOG
WHERE JOBINFO.JOBNUM = ACTIVITYLOG.JOBNUM AND
ACTIVITYLOG.EMPLOYEENUM = #EMPLOYEENUM# AND
ACTIVITYLOG.LOGOUT is null AND
ACTIVITYLOG.LOGIN is not null
ORDER BY JOBINFO.JOBNUM, ACTIVITYLOG.LOGIN
</cfquery>
**********************************************************************
Information in this email is confidential and may be privileged.
It is intended for the addressee only. If you have received it in error,
please notify the sender immediately and delete it from your system.
You should not otherwise copy it, retransmit it or use or disclose its
contents to anyone.
Thank you for your co-operation.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Get the mailserver that powers this list at http://www.coolfusion.com
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
