473,320 Members | 1,865 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Difference on condition in JOIN & WHERE

I appreciate how difficult it is to resolve a problem without all the
information but maybe someone has come across a similar problem.

I have an 'extract' table which has 1853 rows when I ask for all rows where
period_ = 3. The allocation table for info has 210 rows.

I have two scripts below. The first script where I specify a period on a
join, brings back 1853 lines and works. The second script where I specify
the period in the where clause only brings back 1844 rows. I have located
the missing 9 rows and they don't look any different to the other 1844 rows.

Can someone educate me as to the difference between specifying a condition
on a join and a condition in a where clause.

SELECT
a.costcentre_,
b.nett_,
a.*,
b.*

FROM extract a

LEFT OUTER JOIN
allocation b

ON a.e_reg_ = b.reg_no_
AND b.period_ = 3
WHERE
a.period_ = 3
--------------
SELECT
a.costcentre_,
b.nett_,
a.*,
b.*

FROM extract a

LEFT OUTER JOIN
allocation b

ON a.e_reg_ = b.reg_no_
WHERE
a.period_ = 3
AND b.period_ = 3
May 3 '07 #1
4 14213
Jane T (ja***@nospam.net) writes:
Can someone educate me as to the difference between specifying a condition
on a join and a condition in a where clause.
As long as you are doing only inner joins it does not matter. When you
do outer joins it matters a lot.

Let's look at this:
FROM extract a
LEFT OUTER JOIN allocation b ON a.e_reg_ = b.reg_no_
AND b.period_ = 3
WHERE a.period_ = 3
You take all rows from extract. Then we add the columns from the
allocation table and fill data into these where there is a match
on the condition

a.e_reg_ = b.reg_no_ AND b.period_ = 3

For non-matching rows, you leave those columns NULL. Finally, you filter
this with the WHERE clause.
FROM extract a
LEFT OUTER JOIN allocation b ON a.e_reg_ = b.reg_no_
WHERE a.period_ = 3
AND b.period_ = 3

Again, you take all rows from extract, and add the columns from allocation.
Now you fill in the data for the rows that match the condition:

a.e_reg_ = b.reg_no_

and leave NULL in the columns from allocation where there is no match. Now
you filter with the WHERE clause. But the WHERE clause has the condition:

b.period_ = 3

And the rows with NULL in b.period_ does not fulfil this condition,
and thus rows are filtered out.

More generally, the FROM JOIN clauses builds a table, and then the
WHERE clause filters that table.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
May 3 '07 #2
Jane T wrote:
I have an 'extract' table which has 1853 rows when I ask for all rows where
period_ = 3. The allocation table for info has 210 rows.

I have two scripts below. The first script where I specify a period on a
join, brings back 1853 lines and works. The second script where I specify
the period in the where clause only brings back 1844 rows. I have located
the missing 9 rows and they don't look any different to the other 1844 rows.

Can someone educate me as to the difference between specifying a condition
on a join and a condition in a where clause.
For equal joins, there's no difference. For left outer joins,
specifying a condition on the join simply causes it to return
rows with data from the left side and NULLs from the right side,
whereas specifying a condition on the WHERE causes it to filter
the output of the JOIN.

Basically, the query is processed in this order:

* FROM/JOIN
* SELECT
* WHERE
* GROUP BY
* HAVING
* ORDER BY
May 3 '07 #3
"Ed Murphy" <em*******@socal.rr.comwrote in message
news:46**********************@roadrunner.com...
Jane T wrote:
Basically, the query is processed in this order:

* FROM/JOIN
* SELECT
* WHERE
* GROUP BY
* HAVING
* ORDER BY
Ed,

Wouldn't SELECT be much further down in the list, between HAVING and ORDER
BY? Otherwise one might be tempted to write:

SELECT col1 AS mycol
FROM Table
WHERE mycol = 'X';

:)

Plamen Ratchev
http://www.SQLStudio.com
May 4 '07 #4
Here is how OUTER JOINs work in SQL-92. Assume you are given:

Table1 Table2
a b a c
====== ======
1 w 1 r
2 x 2 s
3 y 3 t
4 z

and the outer join expression:

Table1
LEFT OUTER JOIN
Table2
ON Table1.a = Table2.a <== join condition
AND Table2.c = 't'; <== single table condition

We call Table1 the "preserved table" and Table2 the "unpreserved
table" in the query. What I am going to give you is a little
different, but equivalent to the ANSI/ISO standards.

1) We build the CROSS JOIN of the two tables. Scan each row in the
result set.

2) If the predicate tests TRUE for that row, then you keep it. You
also remove all rows derived from it from the CROSS JOIN

3) If the predicate tests FALSE or UNKNOWN for that row, then keep the
columns from the preserved table, convert all the columns from the
unpreserved table to NULLs and remove the duplicates.

So let us execute this by hand:

Let @ = passed the first predicate
Let * = passed the second predicate

Table1 CROSS JOIN Table2
a b a c
=========================
1 w 1 r @
1 w 2 s
1 w 3 t *
2 x 1 r
2 x 2 s @
2 x 3 t *
3 y 1 r
3 y 2 s
3 y 3 t @* <== the TRUE set
4 z 1 r
4 z 2 s
4 z 3 t *

Table1 LEFT OUTER JOIN Table2
a b a c
=========================
3 y 3 t <= only TRUE row
-----------------------
1 w NULL NULL Sets of duplicates
1 w NULL NULL
1 w NULL NULL
-----------------------
2 x NULL NULL
2 x NULL NULL
2 x NULL NULL
3 y NULL NULL <== derived from the TRUE set - Remove
3 y NULL NULL
-----------------------
4 z NULL NULL
4 z NULL NULL
4 z NULL NULL

the final results:

Table1 LEFT OUTER JOIN Table2
a b a c
=========================
1 w NULL NULL
2 x NULL NULL
3 y 3 t
4 z NULL NULL

The basic rule is that every row in the preserved table is represented
in the results in at least one result row.

There are limitations and very serious problems with the extended
equality version of an outer join used in some diseased mutant
products. Consider the two Chris Date tables

Suppliers SupParts
supno supno partno qty
========= ==============
S1 S1 P1 100
S2 S1 P2 250
S3 S2 P1 100
S2 P2 250

and let's do an extended equality outer join like this:

SELECT *
FROM Supplier, SupParts
WHERE Supplier.supno *= SupParts.supno
AND qty < 200;

If I do the outer first, I get:

Suppliers LOJ SupParts
supno supno partno qty
=======================
S1 S1 P1 100
S1 S1 P2 250
S2 S2 P1 100
S2 S2 P2 250
S3 NULL NULL NULL

Then I apply the (qty < 200) predicate and get

Suppliers LOJ SupParts
supno supno partno qty
===================
S1 S1 P1 100
S2 S2 P1 100

Doing it in the opposite order

Suppliers LOJ SupParts
supno supno partno qty
===================
S1 S1 P1 100
S2 S2 P1 100
S3 NULL NULL NULL

Sybase does it one way, Oracle does it the other and Centura (nee
Gupta) lets you pick which one -- the worst of both non-standard
worlds! In SQL-92, you have a choice and can force the order of
execution. Either do the predicates after the join ...

SELECT *
FROM Supplier
LEFT OUTER JOIN
SupParts
ON Supplier.supno = SupParts.supno
WHERE qty < 200;

... or do it in the joining:

SELECT *
FROM Supplier
LEFT OUTER JOIN
SupParts
ON Supplier.supno = SupParts.supno
AND qty < 200;

Another problem is that you cannot show the same table as preserved
and unpreserved in the extended equality version, but it is easy in
SQL-92. For example to find the students who have taken Math 101 and
might have taken Math 102:

SELECT C1.student, C1.math, C2.math
FROM (SELECT * FROM Courses WHERE math = 101) AS C1
LEFT OUTER JOIN
(SELECT * FROM Courses WHERE math = 102) AS C2
ON C1.student = C2.student;

May 4 '07 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Dam | last post by:
Using SqlServer : Query 1 : SELECT def.lID as IdDefinition, TDC_AUneValeur.VALEURDERETOUR as ValeurDeRetour FROM serveur.Data_tblDEFINITIONTABLEDECODES def,...
1
by: Peter Alberer | last post by:
Hi there, i have a problem with a query that uses the result of a plsql function In the where clause: SELECT assignments.assignment_id, assignments.package_id AS package_id,...
8
by: Mike Mascari | last post by:
Hello. I have a query like: SELECT big_table.* FROM little_table, big_table WHERE little_table.x = 10 AND little_table.y IN (big_table.y1, big_table.y2); I have indexes on both big_table.y1...
5
by: das | last post by:
hello all, this might be simple: I populate a temp table based on a condition from another table: select @condition = condition from table1 where id=1 in my stored procedure I want to do...
4
by: joh12005 | last post by:
Hello, i posted for suggestions a little idea even if it still needs further thoughts but as i'm sure you could help :) if would like to implement some kind of Condition class which i coud...
9
by: HC | last post by:
Hello, all, I started out thinking my problems were elsewhere but as I have worked through this I have isolated my problem, currently, as a difference between MSDE and SQL Express 2005 (I'll just...
3
by: Smita Kashyap | last post by:
What is the main difference between Full Join and Inner Join in SQl Server 2005 ? As inner join retrive all records that match certain condition, similiary in full join will rreturn all records...
7
by: pbd22 | last post by:
Hi. I really need some advice on fine-tuning a stored procedure that is the meat of the search logic on my site. Customers are allowed to save searches, which dumps the search logic in a table...
5
by: DaveLock | last post by:
Hi, I have 3 tables of data created from different sources, each with the same 2 fields & I wanted to fill the 2nd field of another table with the data on condition. The condition is because...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.