Join vs. *= - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Join vs. *=

Author

Topic

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-04-21 : 09:49:05

What is the difference between using an Outer Join clause instead of using *=?

Reason I asked is that when I use outer join in place of my *=, I'm getting less records. I can't seem to figure what's causing it.

Below are my queries:

This one gets the right records:

SELECT R.PART_ID, P.DESCRIPTION, CAST(CAST(BITS AS BINARY(8000)) AS VARCHAR(40)) AS SPECS,
R.WORKORDER_SUB_ID, R.WORKORDER_BASE_ID, R.OPERATION_SEQ_NO, R.PIECE_NO, QTY_PER,CALC_QTY,ISSUED_QTY,
P.STOCK_UM, EST_MATERIAL_COST+EST_LABOR_COST+EST_BURDEN_COST AS ESTIMATED_COST,
R.ACT_LABOR_COST+R.ACT_BURDEN_COST+R.ACT_MATERIAL_COST AS ACTUAL_COST
FROM REQUIREMENT R,PART P, REQUIREMENT_BINARY RB
WHERE R.WORKORDER_BASE_ID=@WORKORDER
AND R.PART_ID*=P.ID
AND R.WORKORDER_BASE_ID*=RB.WORKORDER_BASE_ID
AND R.WORKORDER_SUB_ID*=RB.WORKORDER_SUB_ID
AND R.OPERATION_SEQ_NO*=RB.OPERATION_SEQ_NO
AND R.PIECE_NO*=RB.PIECE_NO
AND R.SUBORD_WO_SUB_ID IS NULL
AND RB.WORKORDER_TYPE = 'W'
ORDER BY R.PART_ID,P.DESCRIPTION,R.WORKORDER_SUB_ID,R.OPERATION_SEQ_NO

This one gets less records, but I want to use Joins:

SELECT R.PART_ID, P.DESCRIPTION, CAST(CAST(RB.BITS AS BINARY(8000)) AS VARCHAR(40)) AS SPECS, R.WORKORDER_SUB_ID, R.OPERATION_SEQ_NO,
R.PIECE_NO, R.QTY_PER, R.CALC_QTY, R.ISSUED_QTY, P.STOCK_UM,
R.EST_MATERIAL_COST + R.EST_LABOR_COST + R.EST_BURDEN_COST AS ESTIMATED_COST,
R.ACT_LABOR_COST + R.ACT_BURDEN_COST + R.ACT_MATERIAL_COST AS ACTUAL_COST
FROM REQUIREMENT_BINARY RB RIGHT OUTER JOIN
REQUIREMENT R LEFT OUTER JOIN
=PART P ON R.PART_ID = P.ID ON RB.WORKORDER_BASE_ID = R.WORKORDER_BASE_ID
AND RB.WORKORDER_SUB_ID = R.WORKORDER_SUB_ID
AND RB.OPERATION_SEQ_NO = R.OPERATION_SEQ_NO
AND RB.PIECE_NO = R.PIECE_NO
WHERE (R.WORKORDER_BASE_ID = @WORKORDER)
AND (R.SUBORD_WO_SUB_ID IS NULL)
AND (RB.WORKORDER_TYPE = 'W')
ORDER BY R.PART_ID, P.DESCRIPTION, R.WORKORDER_SUB_ID, R.OPERATION_SEQ_NO

Anyone can explain to me why one can get more records than the other? Maybe I'm missing something?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-04-21 : 09:54:37

Don't use RIGHT OUTER JOINS .... always select FROM your base table and then LEFT OUTER JOIN to outer tables as needed. Your "base table" is the primary driver for the data you are returning. Try to avoid the query designer since it doesn't really produce logical JOIN clauses depending on what order you add tables in or draw your joins.

In might help to explain to us (and think to yourself) in logical terms what relationship you are trying to express between these tables.

- Jeff

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-04-21 : 10:05:40

ooops. It was meant to be Left Outer Join and not right.

What I wanted to do is pull records from the Requirement (R) table but there are some small little information that I need from the other 2 tables. HOwever by using the join clause, I'm not getting all the records like the first query example I provided. I was wondering why the Join Clause is dropping records and not pulling it like the firs query using *= operator.

Any ideas?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-04-21 : 10:48:53

This should be the equivelant FROM clause using the sql-92 syntax:
(good for you for switching!!)


FROM	REQUIREMENT R
Left JOIN PART P
		ON R.PART_ID=P.ID 
Left JOIN REQUIREMENT_BINARY RB
		ON	R.WORKORDER_BASE_ID=RB.WORKORDER_BASE_ID 
		AND	R.WORKORDER_SUB_ID=RB.WORKORDER_SUB_ID 
		AND	R.OPERATION_SEQ_NO=RB.OPERATION_SEQ_NO 
		AND	R.PIECE_NO=RB.PIECE_NO 
WHERE	R.WORKORDER_BASE_ID=@WORKORDER 
AND	R.SUBORD_WO_SUB_ID IS NULL 
AND	RB.WORKORDER_TYPE = 'W'

Be One with the Optimizer
TG

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-04-21 : 11:18:53

Thanks for the help TG. A lot of our SQL stuff are old and needs to be changed or modify.

The example you provided produce the same result on my second query. I think the *= operator is equivalent to the left join. However I think the second only pulls records where my "BITS" field is not null.

The first query would pull records where the "BITS" is null, but now the second query doesn't have any records that has NULL value for the field "BITS".

I'm just not clear on why it would do that by using Join Clauses. Do I need to state so that it will pull the records even if the "BITS" field is Null from my WHere clause?

mfemenel
Professor Frink

1421 Posts

Posted - 2005-04-21 : 11:55:36

Microsoft has also stated that in future versions *= may not be supported. I've also seen some discussion that has said *= may be slower than using the text join syntax but opinions on that seem to conflict. My personal opinion is just because MS says they may not support *= in the future, stay away from it. Would you want to change it later and change it in all your code and upgrade it if they don't support in future versions? Doesn't seem worth it for the quick shortcut.

Mike
"oh, that monkey is going to pay"

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-04-21 : 12:45:49

I'm confused as to why you have the condition "R.SUBORD_WO_SUB_ID IS NULL" if you want to include non-nulls.

I guess your second query (old syntax) treated that condition as part of the JOIN criteria. To make the new syntax equivelant, try moving that condition to the JOIN criteria instead of the WHERE criteria. (or removing it)


FROM	REQUIREMENT R
Left JOIN PART P
		ON R.PART_ID=P.ID 
Left JOIN REQUIREMENT_BINARY RB
		ON	R.WORKORDER_BASE_ID=RB.WORKORDER_BASE_ID 
		AND	R.WORKORDER_SUB_ID=RB.WORKORDER_SUB_ID 
		AND	R.OPERATION_SEQ_NO=RB.OPERATION_SEQ_NO 
		AND	R.PIECE_NO=RB.PIECE_NO 
                AND	R.SUBORD_WO_SUB_ID IS NULL 
WHERE	R.WORKORDER_BASE_ID=@WORKORDER 
AND	RB.WORKORDER_TYPE = 'W'

Be One with the Optimizer
TG

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2005-04-21 : 14:15:43

Yes, when you put criteria for the right-hand table of a Left Join into the WHERE clause, it essentially gets converted to an INNER JOIN. I had this come up as an interview question recently. Thankfully, I wrote an article about it a couple of years ago, which you might want to check out to get more familiar with this trick.

---------------------------
EmeraldCityDomains.com

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-04-21 : 14:33:05

Well Duh! I didn't even catch that, what with my tunnel vision and all.


FROM	REQUIREMENT R
Left JOIN PART P
		ON R.PART_ID=P.ID 
Left JOIN REQUIREMENT_BINARY RB
		ON	R.WORKORDER_BASE_ID=RB.WORKORDER_BASE_ID 
		AND	R.WORKORDER_SUB_ID=RB.WORKORDER_SUB_ID 
		AND	R.OPERATION_SEQ_NO=RB.OPERATION_SEQ_NO 
		AND	R.PIECE_NO=RB.PIECE_NO 
                AND	R.SUBORD_WO_SUB_ID IS NULL 
		AND	RB.WORKORDER_TYPE = 'W' 
WHERE	R.WORKORDER_BASE_ID=@WORKORDER

Chriskahn2000, you get the idea? Where criteria limits the entire result set, OUTER Join criteria limits the right table's records (as well as defines the join columns).

Be One with the Optimizer
TG

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2005-04-21 : 20:27:56

The old style (*=, =*) outer join syntax is not equivalent to the new style (FROM clause)outer join syntax when more than one condition is involved.

HTH

=================================================================
Every act of conscious learning requires the willingness to suffer an injury to one's self-esteem. That is why young children, before they are aware of their own self-importance, learn so easily; and why older persons, especially if vain or important, cannot learn at all. -Thomas Szasz, author, professor of psychiatry (1920- )

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-04-21 : 20:49:43

...and the *= syntax is gone for SQL Server 2005. Better start fixing code now...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources