I have the following query
select studentid from students where studentid not in (select studentid from
coursestudent)
is there some way I could write this with using the 'not in'?
student is a table of students. coursestudent is a join table of courses to
students. I'm trying
to find those students who are not registered for a course. While the
above query works,
I'd like to find a more efficient query.
create table student
(studentid int identity primary key,
name varchar(50),
address1varchar(50),
address2 varchar(50),
city varchar(50),
state varchar(4),
zip varchar(11))
create table coursestudent
(courseid int
studentid int)
notes:
- non clustered index on courseid and non clustered index on studentid exist
- there is a foreign key relationship from coursestudent's studentid to
student studentidtry:
select a.studentid from students as a
left join coursestudent as b on a.studentid = b.studentid
where b.courseid is null
Mikhail Berlyant
Eng.Manager
Yahoo! Music
"Dodo Lurker" <none@.noemailplease> wrote in message
news:nc-dndGE1OlQ6IXYnZ2dnUVZ_uudnZ2d@.comcast.com...
> Hi
> I have the following query
> select studentid from students where studentid not in (select studentid
from
> coursestudent)
> is there some way I could write this with using the 'not in'?
> student is a table of students. coursestudent is a join table of courses
to
> students. I'm trying
> to find those students who are not registered for a course. While the
> above query works,
> I'd like to find a more efficient query.
> create table student
> (studentid int identity primary key,
> name varchar(50),
> address1varchar(50),
> address2 varchar(50),
> city varchar(50),
> state varchar(4),
> zip varchar(11))
> create table coursestudent
> (courseid int
> studentid int)
> notes:
> - non clustered index on courseid and non clustered index on studentid
exist
> - there is a foreign key relationship from coursestudent's studentid to
> student studentid
>
>|||This is a multi-part message in MIME format.
--=_NextPart_000_0247_01C6E0CB.6186E090
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
To determine which is most efficient, you need to look at the execution =plans, examine the I/O, and execution times.
Here is an alternative:
SELECT StudentID FROM Students s
JOIN CourseStudent c
ON s.StudentID =3D c.StudentID
WHERE c.StudentID IS NULL
-- Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous
"Dodo Lurker" <none@.noemailplease> wrote in message =news:nc-dndGE1OlQ6IXYnZ2dnUVZ_uudnZ2d@.comcast.com...
> Hi
> > I have the following query
> > select studentid from students where studentid not in (select =studentid from
> coursestudent)
> > is there some way I could write this with using the 'not in'?
> > student is a table of students. coursestudent is a join table of =courses to
> students. I'm trying
> to find those students who are not registered for a course. While =the
> above query works,
> I'd like to find a more efficient query.
> > create table student
> (studentid int identity primary key,
> name varchar(50),
> address1varchar(50),
> address2 varchar(50),
> city varchar(50),
> state varchar(4),
> zip varchar(11))
> > create table coursestudent
> (courseid int
> studentid int)
> > notes:
> - non clustered index on courseid and non clustered index on studentid =exist
> - there is a foreign key relationship from coursestudent's studentid =to
> student studentid
> > > >
--=_NextPart_000_0247_01C6E0CB.6186E090
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
To determine which is most efficient, =you need to look at the execution plans, examine the I/O, and execution =times.
Here is an alternative:
SELECT StudentID =FROM Students s JOIN CourseStudent c ON s.StudentID =3D =c.StudentIDWHERE c.StudentID IS NULL
-- Arnie Rowland, =Ph.D.Westwood Consulting, Inc
Most good judgment comes from =experience. Most experience comes from bad judgment. - Anonymous
"Dodo Lurker"
--=_NextPart_000_0247_01C6E0CB.6186E090--|||Arnie
Please double-check - this shouldn't work
There should be left join
Mikhail Berlyant
Eng.Manager
Yahoo! Music
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:ul7tQYQ4GHA.2144@.TK2MSFTNGP04.phx.gbl...
To determine which is most efficient, you need to look at the execution
plans, examine the I/O, and execution times.
Here is an alternative:
SELECT StudentID
FROM Students s
JOIN CourseStudent c
ON s.StudentID = c.StudentID
WHERE c.StudentID IS NULL
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Dodo Lurker" <none@.noemailplease> wrote in message
news:nc-dndGE1OlQ6IXYnZ2dnUVZ_uudnZ2d@.comcast.com...
> Hi
> I have the following query
> select studentid from students where studentid not in (select studentid
from
> coursestudent)
> is there some way I could write this with using the 'not in'?
> student is a table of students. coursestudent is a join table of courses
to
> students. I'm trying
> to find those students who are not registered for a course. While the
> above query works,
> I'd like to find a more efficient query.
> create table student
> (studentid int identity primary key,
> name varchar(50),
> address1varchar(50),
> address2 varchar(50),
> city varchar(50),
> state varchar(4),
> zip varchar(11))
> create table coursestudent
> (courseid int
> studentid int)
> notes:
> - non clustered index on courseid and non clustered index on studentid
exist
> - there is a foreign key relationship from coursestudent's studentid to
> student studentid
>
>|||Why do you think the solution you have is not efficient?
In the pubs database, these two queries have an almost identical plan, but
the wone with the NOT IN is slightly cheaper:
use pubs
select pub_name from publishers
where pub_id not in (select pub_id from titles)
select pub_name
from publishers p left join titles t
on p.pub_id = t.pub_id
where t.pub_id is null
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Dodo Lurker" <none@.noemailplease> wrote in message
news:nc-dndGE1OlQ6IXYnZ2dnUVZ_uudnZ2d@.comcast.com...
> Hi
> I have the following query
> select studentid from students where studentid not in (select studentid
> from
> coursestudent)
> is there some way I could write this with using the 'not in'?
> student is a table of students. coursestudent is a join table of courses
> to
> students. I'm trying
> to find those students who are not registered for a course. While the
> above query works,
> I'd like to find a more efficient query.
> create table student
> (studentid int identity primary key,
> name varchar(50),
> address1varchar(50),
> address2 varchar(50),
> city varchar(50),
> state varchar(4),
> zip varchar(11))
> create table coursestudent
> (courseid int
> studentid int)
> notes:
> - non clustered index on courseid and non clustered index on studentid
> exist
> - there is a foreign key relationship from coursestudent's studentid to
> student studentid
>
>|||> is there some way I could write this with using the 'not in'?
Another method is with NOT EXISTS. NOT IN and NOT EXISTS will both yield
identical execution plans (and performance) as long as the queries are
semantically the same. The SQL language is descriptive rather than
procedural so the optimizer will try to generate the most efficient plan
possible based on your query statement. As long as the expressions are
sargable, most performance tuning is in making sure you have appropriate
indexes.
However, note that the coursestudent.studentid column allows null so the
queries are not the same semantically. With not NOT IN query, no rows will
be returned if *any* null studentid exists in the coursestudent table. The
NOT EXIST query will return the stundents you expect.
If the coursestudent studentid column were changed to allow nulls, then both
NOT IN and NOT EXISTS queries will be semantically identical.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Dodo Lurker" <none@.noemailplease> wrote in message
news:nc-dndGE1OlQ6IXYnZ2dnUVZ_uudnZ2d@.comcast.com...
> Hi
> I have the following query
> select studentid from students where studentid not in (select studentid
> from
> coursestudent)
> is there some way I could write this with using the 'not in'?
> student is a table of students. coursestudent is a join table of courses
> to
> students. I'm trying
> to find those students who are not registered for a course. While the
> above query works,
> I'd like to find a more efficient query.
> create table student
> (studentid int identity primary key,
> name varchar(50),
> address1varchar(50),
> address2 varchar(50),
> city varchar(50),
> state varchar(4),
> zip varchar(11))
> create table coursestudent
> (courseid int
> studentid int)
> notes:
> - non clustered index on courseid and non clustered index on studentid
> exist
> - there is a foreign key relationship from coursestudent's studentid to
> student studentid
>
>|||You are absolutely correct. I meant to include the LEFT on the join.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Mikhail Berlyant" <remove-this-berlyant@.yahoo-inc.com> wrote in message
news:OzbZqbQ4GHA.3592@.TK2MSFTNGP05.phx.gbl...
> Arnie
> Please double-check - this shouldn't work
> There should be left join
> Mikhail Berlyant
> Eng.Manager
> Yahoo! Music
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:ul7tQYQ4GHA.2144@.TK2MSFTNGP04.phx.gbl...
> To determine which is most efficient, you need to look at the execution
> plans, examine the I/O, and execution times.
> Here is an alternative:
> SELECT StudentID
> FROM Students s
> JOIN CourseStudent c
> ON s.StudentID = c.StudentID
> WHERE c.StudentID IS NULL
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Dodo Lurker" <none@.noemailplease> wrote in message
> news:nc-dndGE1OlQ6IXYnZ2dnUVZ_uudnZ2d@.comcast.com...
>> Hi
>> I have the following query
>> select studentid from students where studentid not in (select studentid
> from
>> coursestudent)
>> is there some way I could write this with using the 'not in'?
>> student is a table of students. coursestudent is a join table of courses
> to
>> students. I'm trying
>> to find those students who are not registered for a course. While the
>> above query works,
>> I'd like to find a more efficient query.
>> create table student
>> (studentid int identity primary key,
>> name varchar(50),
>> address1varchar(50),
>> address2 varchar(50),
>> city varchar(50),
>> state varchar(4),
>> zip varchar(11))
>> create table coursestudent
>> (courseid int
>> studentid int)
>> notes:
>> - non clustered index on courseid and non clustered index on studentid
> exist
>> - there is a foreign key relationship from coursestudent's studentid to
>> student studentid
>>
>>
>|||thank you everyone
"Dodo Lurker" <none@.noemailplease> wrote in message
news:nc-dndGE1OlQ6IXYnZ2dnUVZ_uudnZ2d@.comcast.com...
> Hi
> I have the following query
> select studentid from students where studentid not in (select studentid
from
> coursestudent)
> is there some way I could write this with using the 'not in'?
> student is a table of students. coursestudent is a join table of courses
to
> students. I'm trying
> to find those students who are not registered for a course. While the
> above query works,
> I'd like to find a more efficient query.
> create table student
> (studentid int identity primary key,
> name varchar(50),
> address1varchar(50),
> address2 varchar(50),
> city varchar(50),
> state varchar(4),
> zip varchar(11))
> create table coursestudent
> (courseid int
> studentid int)
> notes:
> - non clustered index on courseid and non clustered index on studentid
exist
> - there is a foreign key relationship from coursestudent's studentid to
> student studentid
>
>
No comments:
Post a Comment