Filtered joins

[newtophp2000]

I am having some difficulty in constructing outer joins. I have
simplified what I need to do and have included sample SQL statements:

create table tab_a (id int, descr varchar(10), qty int)
insert into tab_a values (1, 'item one', 10)
insert into tab_a values (2, 'item two', 20)
insert into tab_a values (3, 'item three', 30)
insert into tab_a values (4, 'item four', 40)

create table tab_b (id2 int, descr2 varchar(10), qty2 int)
insert into tab_b values (1, 'item one', 10)
insert into tab_b values (2, 'item two', 20)
insert into tab_b values (3, 'item three', 30)
insert into tab_b values (4, 'item four', 40)

Here is the statement that I have:

SELECT tab_a.id,
tab_a.descr,
tab_a.qty,
tab_b.id2,
tab_b.descr2,
tab_b.qty2
FROM tab_a LEFT OUTER JOIN tab_b
ON (tab_a.id = tab_b.id2 )
WHERE tab_a.qty <= 30 AND
tab_b.qty2 > 20

What I am trying to do is left outer join between tab_a and tab_b after
they have been filtered based on the qty column. (for tab_a: qty <=
30; and for tab_b: qty > 20).

How would I go about that? I would like to do this efficiently since
the two tables have about a million records and several other columns
each.

[Hugo Kornelis]

On 20 Jan 2005 06:40:53 -0800, ne**********@yahoo.com wrote:

(snip)

How would I go about that? I would like to do this efficiently since
the two tables have about a million records and several other columns
each.

Hi newtophp,

You forgot to include the desired output from your query. Based on your
narrative, I *think* that this query might do what you want:

SELECT tab_a.id, tab_a.descr, tab_a.qty,
tab_b.id2, tab_b.descr2, tab_b.qty2
FROM tab_a
LEFT OUTER JOIN tab_b
ON tab_a.id = tab_b.id2 AND tab_b.qty2 > 20
WHERE tab_a.qty <= 30
Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[newtophp2000]

Hugo Kornelis wrote:

On 20 Jan 2005 06:40:53 -0800, ne**********@yahoo.com wrote: Hi newtophp,

You forgot to include the desired output from your query. Based on your narrative, I *think* that this query might do what you want:

SELECT tab_a.id, tab_a.descr, tab_a.qty,
tab_b.id2, tab_b.descr2, tab_b.qty2
FROM tab_a
LEFT OUTER JOIN tab_b
ON tab_a.id = tab_b.id2 AND tab_b.qty2 > 20
WHERE tab_a.qty <= 30
Thanks Hugo! It works as expected now. Essentially, what I was
looking for was standard outer join output:

id descr qt id2 descr2 qty2
==================================
1 item one 10 NULL NULL NULL
2 item two 20 NULL NULL NULL
3 item three 30 3 item three 30

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[newtophp2000]

> Hugo Kornelis wrote:

Based on your
narrative, I *think* that this query might do what you want:

SELECT tab_a.id, tab_a.descr, tab_a.qty,
tab_b.id2, tab_b.descr2, tab_b.qty2
FROM tab_a
LEFT OUTER JOIN tab_b
ON tab_a.id = tab_b.id2 AND tab_b.qty2 > 20
WHERE tab_a.qty <= 30
Thanks Hugo! It works as expected now.

Hi Hugo,

After playing with the above statement, I noticed that this form also
works: (I put extra blank lines to highlight the difference):

SELECT tab_a.id, tab_a.descr, tab_a.qty,
tab_b.id2, tab_b.descr2, tab_b.qty2
FROM tab_a
LEFT OUTER JOIN tab_b
ON tab_a.id = tab_b.id2 AND tab_b.qty2 > 20

AND tab_a.qty <= 30

WHERE tab_a.qty <= 30

The difference is that the filter "tab_a.qty <= 30" is repeated in both
the ON clause and the WHERE clause. Would this form make the query
execute faster or slower? It appears that it prunes out rows even
before the outer join so it would be more efficient but I am not
certain.

What is your take on this?

Thanks!

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Hugo Kornelis]

On 20 Jan 2005 18:12:41 -0800, ne**********@yahoo.com wrote:

After playing with the above statement, I noticed that this form also
works (snip) Would this form make the query
execute faster or slower? It appears that it prunes out rows even
before the outer join so it would be more efficient but I am not
certain.

What is your take on this?

Hi newtophp2000,

The only way to find out is to test is and compare execution plans.

I ran a test with the tables and sample data you posted. With these
tables, this sample data and my hardware configuration, the plans were
very similar. Both execution plans start with a table scan on table a,
already filtering for the requirement on tab_a.qty. Both queries then join
table b (using a table scan on table b for each row returned from the scan
on table a, filtering for the value of tab_b.qty2). The only difference I
found is that your query would compare the value of tab_a.qty to 30 a
second time during the join operation.

On your system, with your real tables, real data and (hopefully) some
useable indexes, you're very likely to get different results. So you
really should test it yourself.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Erland Sommarskog]

(ne**********@yahoo.com) writes:

After playing with the above statement, I noticed that this form also
works: (I put extra blank lines to highlight the difference):

SELECT tab_a.id, tab_a.descr, tab_a.qty,
tab_b.id2, tab_b.descr2, tab_b.qty2
FROM tab_a
LEFT OUTER JOIN tab_b
ON tab_a.id = tab_b.id2 AND tab_b.qty2 > 20

AND tab_a.qty <= 30

WHERE tab_a.qty <= 30

The difference is that the filter "tab_a.qty <= 30" is repeated in both
the ON clause and the WHERE clause. Would this form make the query
execute faster or slower? It appears that it prunes out rows even
before the outer join so it would be more efficient but I am not
certain.

The optimizer may be smart enough to only check the condition once.

Generally, maybe we should explain why you had to rewrite the query.

If you say:

SELECT ...
FROM a LEFT JOIN b ON a.col = b.col

You get a table which has many rows as there are rows in a, and
which includes all columns in a and b. For the rows in a where there are
matching rows in b, there are values in the b columns, for other
rows all columns in b are NULL.

Then we apply a filter:

SELECT ...
FROM a LEFT JOIN b ON a.col = b.col
WHERE b.othercol = 2

The WHERE clause filters the first table, so that only rows with
b.othercol = 2 remains. This means that all rows with NULL in b.othercol
are lost. Specifically that means that all rows in a without matching
rows in b are lost.

If we move the condition to the ON clause, the result is different:

SELECT ...
FROM a LEFT JOIN b ON a.col = b.col
AND b.othercol = 2

Now there will be NULL in all b-columns for all rows in a that does
not match a row in b where othercol = 2.

More generally, the FROM JOIN clauses are evalutated in order to build
table by table, and then the final table is filtered by WHERE.

CONCEPTUALLY THAT IS! The actual computation order performed by
SQL Server may be totally different. The optimizer is free to recast
as long as the result does not change.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp