|
|
Posted by Hugo Kornelis on 10/13/05 00:02
On 4 Oct 2005 01:33:08 -0700, jsfromynr wrote:
>Hi Hugo,
> As always you are being a gentleman and willing to provide
>help. The above query can be converted into an inner join.
>Something like this :--
>Select * From EmpMst Where deptid in
>( Select deptid from deptmst where deptname='acc')
>-----------------
>into This ----
>--------------
>Select * From EmpMst EM
>Inner Join DeptMst DM On Dm.deptid = EM.deptid and DM.deptname='acc'
(snip)
Hi Jatinder,
My apologies for the delayed reply. Real life and other obligations have
been interfering.
The two queries above are not exactly equivalent.
First, the SELECT * (which should never be used in production code,
unless as part of an EXISTS subquery) will produce more columns in the
second query.
Second, the second query might also produce more rows. This will NOT
happen if you have a PRIMARY KEY or UNIQUE constraint on DeptMst.DeptID,
but it will happen if you have no such constraint. If there are three
rows in DeptMst with the same DeptID value, then each row from EmpMst
with that value in EmpMst.DeptID will be tripled in the second query; it
will still be output only once in the first.
>I have one problem while optimizing the sql query .For a few rows the
>query works perfectly ,but as the number of rows increases it works but
>gives wrong result.
If the explanation above does not apply, then I'd have to see a repro
script in order to comment. See further below.
>I am using (...)
(snip)
The description of your problem is too vague for me to comment on.
Please post actual table structures (as CREATE TABLE statements,
including all constraints and properties but excluding irrelevant extra
columns), some rows of sample data (as INSERT statements - and not all 2
billioin of'em, please - just enough to demonstrate the situation), the
actual query you've been using, the output you needed and the output you
actually got.
Also check out www.aspfaq.com/5006.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Navigation:
[Reply to this message]
|