Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Location
    Jordan
    Posts
    137

    Exclamation Unanswered: Are there better way

    Hi all

    I have two master-detail tables, when I want to get the number of master that does't has detail I write this code :

    select count(*) from dept where
    0 = (select count(*) from emp where emp.dpt_no = dept.no)

    are there better way to this???

    what is the differance between count(*) and count(field), is it the same cost?

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    There isn't anything to choose between count(*) and count(column) performance-wise, but it is better not to count child rows at all - imagine if each parent could have millions of children! Instead, check whether at least one row exists:
    Code:
    select count(*) from dept
    where not exists (select null from emp where emp.dpt_no = dept.no);

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I should add that count(*) and count(column) can return different results if the column may contain nulls:
    Code:
    SQL> select count(*), count(mgr)
      2  from emp;
    
      COUNT(*) COUNT(MGR)
    ---------- ----------
            15         13

  4. #4
    Join Date
    Feb 2004
    Location
    Jordan
    Posts
    137

    Talking

    thanx alot andrewst

    your second reply is very importanat tip.

    regarding to your first reply, I have almost 10 records in the detail table, so I do't touch any differance between them,
    Maybe i'll touch the deffirance if there is huge records in the detail (as you said)

    thanx again

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •