473,695 Members | 3,037 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Diff between "LEFT OUTER JOIN" and " *= " when condition

Dam
Using SqlServer :
Query 1 :

SELECT def.lID as IdDefinition,
TDC_AUneValeur. VALEURDERETOUR as ValeurDeRetour
FROM serveur.Data_tb lDEFINITIONTABL EDECODES def,
serveur.Data_tb lTABLEDECODEAUN EVALEUR TDC_AUneValeur
where def.TYPEDETABLE DECODES = 4
and TDC_AUneValeur. PERIODE_ANNEEFI SCALE_ID = 2
and def.lID *= TDC_AUneValeur. DEFINITIONTABLE DECODES_DEFINIT ION_ID
Query 2 :

SELECT def.lID as IdDefinition,
TDC_AUneValeur. VALEURDERETOUR as ValeurDeRetour
FROM serveur.Data_tb lDEFINITIONTABL EDECODES def LEFT OUTER JOIN
serveur.Data_tb lTABLEDECODEAUN EVALEUR TDC_AUneValeur
ON def.lID = TDC_AUneValeur. DEFINITIONTABLE DECODES_DEFINIT ION_ID
where def.TYPEDETABLE DECODES = 4
and TDC_AUneValeur. PERIODE_ANNEEFI SCALE_ID = 2

The query 1 returns :
IdDefinition ValeurDeRetour
23 null
24 null
25 null
29 36

The query 2 returns :
IdDefinition ValeurDeRetour
29 36
The first result is the good one.
How is it that the second query doesn't return the same resultSet ?
I've been told about problems comparing NULL ???
What is the solution ???
Thanks a lot.

Damien

Jul 23 '05 #1
3 10050
There's a big difference between referencing the unpreserved table
(TDC_AUneValeur ) in the ON clause versus the WHERE clause. Try:

SELECT def.lID as IdDefinition,
TDC_AUneValeur. VALEURDERETOUR as ValeurDeRetour
FROM serveur.Data_tb lDEFINITIONTABL EDECODES def LEFT OUTER JOIN
serveur.Data_tb lTABLEDECODEAUN EVALEUR TDC_AUneValeur
ON def.lID = TDC_AUneValeur. DEFINITIONTABLE DECODES_DEFINIT ION_ID
and TDC_AUneValeur. PERIODE_ANNEEFI *SCALE_ID = 2
where def.TYPEDETABLE DECODES = 4 ;

--
David Portas
SQL Server MVP
--

Jul 23 '05 #2
Dam

Thank you very much.
It's exactly the solution.

Bye

Jul 23 '05 #3
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 "unpreserve d 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;

Jul 23 '05 #4

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

Similar topics

1
4213
by: Steve | last post by:
I have a SQL query I'm invoking via VB6 & ADO 2.8, that requires three "Left Outer Joins" in order to return every transaction for a specific set of criteria. Using three "Left Outer Joins" slows the system down considerably. I've tried creating a temp db, but I can't figure out how to execute two select commands. (It throws the exception "The column prefix 'tempdb' does not match with a table name or alias name used in the query.")
7
31554
by: Steve | last post by:
I have a SQL query I'm invoking via VB6 & ADO 2.8, that requires three "Left Outer Joins" in order to return every transaction for a specific set of criteria. Using three "Left Outer Joins" slows the system down considerably. I've tried creating a temp db, but I can't figure out how to execute two select commands. (It throws the exception "The column prefix 'tempdb' does not match with a table name or alias name used in the query.")
3
19475
by: Martin | last post by:
Hello everybody, I have the following question. As a join clause on Oracle we use " table1.field1 = table2.field1 (+) " On SQL Server we use " table1.field1 *= table2.field1 " Does DB2 have the same type of operator, without using the OUTER JOIN syntax ?
14
5708
by: mike | last post by:
I'm using postgresl 7.3.2 and have a query that executes very slowly. There are 2 tables: Item and LogEvent. ItemID (an int4) is the primary key of Item, and is also a field in LogEvent. Some ItemIDs in LogEvent do not correspond to ItemIDs in Item, and periodically we need to purge the non-matching ItemIDs from LogEvent. The query is: delete from LogEvent where EventType != 'i' and ItemID in
0
1558
by: SKB | last post by:
Hi, I want to implement the "outer join" functionality in Java. Can somebody explain the pseudo code for the same. OR what needs to be done to extend the hash-join Java code of equijoin(I have the hash join code for equijoin with me). Regds
1
1658
by: new2php | last post by:
Hi,I'm new to PHP...I currently doing a PHP project....I don't understand this code..can someone please kindly explain to me..Thanks select * from category c left join category2 c2 on c2.cat1_id=c.cat_id where c.cat_id=" . $_GET . " and c2.cat2_id=" . $_GET What does the c & c2 do? & what is the output result? Here the database table: CREATE TABLE `category` (
1
3275
by: nico3334 | last post by:
I have a query that currently pulls data from a main table and a second table using LEFT OUTER JOIN. I know how to do make another LEFT OUTER JOIN with the main table, but I want to add another LEFT OUTER JOIN to the second table. So I want the third table to be joined through the second table, not the main table. Here is my original code that joins the main table and the second table SELECT t1.supply, t2.inventory, FROM MAIN_TABLE...
2
1501
BeemerBiker
by: BeemerBiker | last post by:
I thought the following would work, but I thought wrong: SELECT DISTINCT AvailableCourses.* FROM AvailableCourses INNER JOIN PlannedCourses ON PlannedCourses.CourseID<>AvailableCourses.CourseID WHERE PlannedCourses.EmplID=?; I want to display courses that are available for an employee to take, but I do not want to display courses that are already planned. If I change <> to "=" then i get the courses that are planned. I want the ones...
0
1978
by: CatchSandeepVaid | last post by:
Product and ProductBasic has one-to-one relationship Product ---> ProductID <<PK>> ProductBasic ----> ProductId, useCode, StartTime as composite key..... this startTime's value will be known as runtime. Now i want to define mapping between Product & ProductBasic, how will i do it ? Moreover suppose i define their mapping as product.productID = productBasic.productID and useCode='a' as :
0
8619
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9112
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
6484
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5831
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4336
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4571
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2994
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2258
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
1970
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.