Professional Web Applications Themes

Efficient query without using NOT IN clause - Microsoft SQL / MS SQL Server

Hi all, I have got two tables:- a) students ======== std_id Numeric std_name Varchar(50) std_grade Varchar(10) b) attendance ========== atd_id Numeric std_id Numeric atd_date DateTime Every day, records get inserted in attendance table only of those students who are present in the class on that day. So if student table has 10 records, and on 1st July 5 were present, then attendance table will have records for those 5 students only. I want to write a TSQL to fetch the records of the students who were not present on a given date. For eg, Want to list student id and ...

  1. #1

    Default Efficient query without using NOT IN clause

    Hi all,

    I have got two tables:-

    a)
    students
    ========
    std_id Numeric
    std_name Varchar(50)
    std_grade Varchar(10)

    b)
    attendance
    ==========
    atd_id Numeric
    std_id Numeric
    atd_date DateTime

    Every day, records get inserted in attendance table only of those
    students who are present in the class on that day. So if student table
    has 10 records, and on 1st July 5 were present, then attendance table
    will have records for those 5 students only.
    I want to write a TSQL to fetch the records of the students who were
    not present on a given date.

    For eg,
    Want to list student id and name of those students who were not
    present on 01/07/2003.

    The easy option is

    SELECT std_id, std_name FROM students
    WHERE std_id NOT IN (SELECT std_id FROM attendance WHERE
    atd_date='01/07/2003')

    I am sure there must be a efficient way to fetch records, coz we all
    know that the IN clause is a killer one!

    I tried using LEFT OUTER JOIN but the query doesnt work if no student
    was present on that day


    Thanks in advance.

    Regards,

    gogaz
    gogaz Guest

  2. #2

    Default Re: Efficient query without using NOT IN clause

    IN clause should work OK in your case since the dataset is a real small one.
    If needed you can use NOT EXISTS as well like:

    SELECT *
    FROM students s1
    WHERE NOT EXISTS ( SELECT *
    FROM attendance a1
    WHERE a1.std_id = s1.std_id
    AND a1.atd_date = dt ) ;

    If you are using left join you can do something along the lines of :

    SELECT s1.*
    FROM students
    LEFT OUTER JOIN attendance a1
    ON a1.std_id = s1.std_id
    AND a1.atd_date = dt
    WHERE a1.std_id IS NULL ;

    --
    - Anith
    ( Please reply to newsgroups only )


    Anith Sen Guest

  3. #3

    Default Re: Efficient query without using NOT IN clause

    Assuming that (std_id, atd_date) is the PK in Attendance:

    SELECT S.std_id, S.std_name
    FROM students AS S
    LEFT JOIN attendance AS A
    ON S.std_id = A.std_id AND A.atd_date='20030701'
    WHERE A.std_id IS NULL

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --



    David Portas Guest

  4. #4

    Default Re: Efficient query without using NOT IN clause

    you can replace IN clause with EXISTS
    try:
    SELECT std_id, std_name FROM students a
    WHERE NOT exists (SELECT 1 FROM attendance WHERE atd_date='01/07/2003'
    and a.std_id = attendance.std_id)

    Also make sure you have indexes on the field std_id and atd_date columns.

    --
    -Vishal
    "gogaz" <gogazrediffmail.com> wrote in message
    news:25eb55ca.0307010852.24cf16bbposting.google.c om...
    > Hi all,
    >
    > I have got two tables:-
    >
    > a)
    > students
    > ========
    > std_id Numeric
    > std_name Varchar(50)
    > std_grade Varchar(10)
    >
    > b)
    > attendance
    > ==========
    > atd_id Numeric
    > std_id Numeric
    > atd_date DateTime
    >
    > Every day, records get inserted in attendance table only of those
    > students who are present in the class on that day. So if student table
    > has 10 records, and on 1st July 5 were present, then attendance table
    > will have records for those 5 students only.
    > I want to write a TSQL to fetch the records of the students who were
    > not present on a given date.
    >
    > For eg,
    > Want to list student id and name of those students who were not
    > present on 01/07/2003.
    >
    > The easy option is
    >
    > SELECT std_id, std_name FROM students
    > WHERE std_id NOT IN (SELECT std_id FROM attendance WHERE
    > atd_date='01/07/2003')
    >
    > I am sure there must be a efficient way to fetch records, coz we all
    > know that the IN clause is a killer one!
    >
    > I tried using LEFT OUTER JOIN but the query doesnt work if no student
    > was present on that day
    >
    >
    > Thanks in advance.
    >
    > Regards,
    >
    > gogaz

    Vishal Parkar Guest

Similar Threads

  1. Simple WHERE clause in CFQUERY Has error executingdatabase query
    By Johnny Jevidetti in forum Coldfusion Database Access
    Replies: 8
    Last Post: November 7th, 06:00 AM
  2. SQL Query - how to contruct a WHERE clause
    By drmaves in forum Coldfusion Database Access
    Replies: 2
    Last Post: September 1st, 10:22 PM
  3. CFGRID QUERY USING A WHERE CLAUSE
    By bflophil in forum Coldfusion - Advanced Techniques
    Replies: 1
    Last Post: February 17th, 05:46 PM
  4. Looking for the most EFFICIENT way to do the following...
    By Sugapablo in forum PHP Development
    Replies: 0
    Last Post: August 25th, 06:50 PM
  5. Query Optimizer Problem with Views in where Clause
    By james in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 11th, 03:26 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139