single statement for mismatches on outer join

[trids]

Hi folks .. two questions:

Q1 = Any ideas on the best way to accomplish the following in only one statement?

SELECT
lhs = L.match_no,
rhs = R.match_no
INTO
#TMP
FROM
#LHS L,
#RHS R
WHERE
L.match_no *= R.match_no

-- DROP matches
DELETE FROM #TMP WHERE rhs != NULL


Q2 = How does a subquery compare, performance-wise?..

SELECT
match_no
INTO
#TMP
FROM
#LHS L
WHERE
match_no NOT IN(SELECT match_no FROM #RHS)

 

[nikhilparchure]

SELECT
lhs = L.match_no,
rhs = R.match_no
INTO
#TMP
FROM
#LHS L,
#RHS R
WHERE
L.match_no *= R.match_no
and R.match_no is null...


Also a (not in ) subquery is the most performance degrading query i have come across after cursors ....

Try avoidin it !!!!!

Regards
Nikhil

 

[trids]

Thanks Nikhil, but ...

The "AND R.match_no IS NULL" clause is the first thing I tried .. but it fails!

Instead of returning only the mismatches, it also returns records from #LHS that do have matches on #RHS .. and sets the #TMP.rhs value to NULL for all records (ie: even those that have values and should not have been returned).

Also have tried putting the "R.match_no IS NULL" criterion in an additional HAVING clause .. but with same results.

Is this a SYBASE issue? using ASE 12.5 here

(( PS: thanks for confirming my fears about the NOT IN subquery ))

 

[saikrish]

Hi trids,

Try this query. As should be evident from the query below it retrieves the extra records alone from one of the tables using a single 'Select' and does not necessitate the usage of temp tables.

SELECT L.match_no
FROM #LHS L Left Outer Join #RHS R
ON L.match_no = R.match_no
WHERE R.match_no is null

Thanks,
Vijay

 

[trids]

HI Vijay

Thanks, that gives exactly the results I'm after.

I've been avoiding the "LEFT OUTER JOIN" syntax in favour of the "*=" syntax, on the recommendation of an article I read (might have been from Microsoft?) that stated that the "LEFT OUTER JOIN" syntax was being phased out and would soon no longer be supported.

The implication was that the "*=" syntax is fully equivalent, so I'm surprised that the results are so different! I'd be very interested to know SYBASE's take on the issue.

Meantime, I'll go back to "LEFT OUTER JOIN" syntax .. at least it works for now.

Thanks again )