Outer join vs Not in

Using outer join to find records in table1 which are not in table2

In this case, I have records in table, playdb..pbt_cusip_pool ( with cusips not in mast..sec_mst) and I need to find them. This outer join works better than not in and not exists clauses.

When you do the outer join, it will show everything from the first table, although not matched with the second table, value from the second tbl will be null, and that is what we are tapping into by using where s.cusip = null :) try it out.. this is fun.

select p.cusip, s.cusip from playdb..pbt_cusip_pool p
left outer join mast..sec_mst s
on (p.cusip = s.cusip)
where s.cusip is null

Check user sessions in Sybase

1) right click on the database (ex: SYCORP_DB1)
2) DBTools
3) Session Manager
4) There is an option on the top to copy all rows
5) Paste them to excel

External Link :

Syntax of inner join

select * from foo

inner join (

select 5 as bar

union all select 6

union all select 7

) as x on foo.bar = x.bar

Forcing index- SQL

So if cust_num is the 6 th index created on the table. Here is how you force the index while querying the database table :

select * from tranhist (6) where cust_num='AA25150'

SQL Not in vs Join example

Obviously SQL join is more efficient than the not in clause. Here is an example of both.


Most inefficient Not in query :

select count(*)
from
wire_instr w
where
w.short_code
not in (select short_code
from nostro_accts)


Slightly better Not Exists query :

select count(*) from
wire_instr w
where not exists(
select 1
from nostro_accts n where
n.short_code=w.short_code)


Finally the Join query :

Join infact creates a new table which

select w.short_code as wshort,n.short_code as nshort from wire_instr w
left outer join
nostro_accts n
on n.short_code=w.short_code
where n.short_code=null

Enjoy
-Sunny

sybase convert float to char

Sybase function to convert float to char
convert(char(10),isnull(convert(char(18), pi.factor),'N/A')) as factor