Re: WHERE clause applies to right-hand table of LEFT JOIN

From: Joseph Geretz (jgeretz_at_nospam.com)
Date: 03/24/04 

Date: Wed, 24 Mar 2004 10:16:17 -0500

> But you may get different results with different data as demonstrated by
the
> example below. Like Tibor mentioned, it's always best to specify INNER
JOIN
> unless you have a reason to do otherwise.

Unfortunately, I don't have the best analysis and monitoring tools for
Interbase. But I do have Windows Task Manager. Here's what I've observed:

Single database accessor scenario, monitoring IBServer.exe:

With the LEFT OUTER JOIN, selecting from the VIEW based on a filter from the
LEFT table, yields results with 2% CPU consumption. - GOOD

With the LEFT OUTER JOIN, selecting from the VIEW based on a filter from the
RIGHT table, yields results with 50-60% CPU consumption. - ABYSMAL

With the INNER JOIN, selecting from the VIEW based on a filter from EITHER
table, yields results with 15-20% CPU consumption. - FAIR

Since (In the Interbase environment at least) the LEFT OUTER JOIN performs
so much better, as long as the WHERE clause addresses columns from the left
hand table, I've arranged my VIEWs to accommodate this.

> INSERT INTO T1 VALUES(1,1,1)
> INSERT INTO T1 VALUES(2,2,2)
> INSERT INTO T2 VALUES(1,1,1)

Your example is technically correct, but in my case this doesn't happen.
Every record in T1 must have at least one and possibly more corresponding
records in T2. (If I get a record in T1 without a corresponding match in T2,
then I have bigger problems on the data input side.)

But your points are well taken. Once we get sqarely into SQL Server, I'll
take another look at this.

Thanks to both of you for your advice,

- Joe Geretz -

Relevant Pages

  • Re: WHERE clause applies to right-hand table of LEFT JOIN
    ... it's always best to specify INNER ... With the LEFT OUTER JOIN, selecting from the VIEW based on a filter from the ... yields results with 50-60% CPU consumption. ...
    (microsoft.public.access.queries)
  • Query Assistance
    ... I have the following query that yields the most recent status for a ... I am having trouble with the LEFT OUTER JOIN dbo.SAF2_MtgDaysTimes AS ... How can I modify this query to pull only the most recent MtgDaysTimes ...
    (comp.databases.ms-sqlserver)