1
\$\begingroup\$

I'm learning The essence of SQL by Rozenshtein indirectly from the SQL Cookbook which has a section on it with modern SQL operators introduced after the book was released.

The exercise:

Given:

create table student
( sno integer,
  sname varchar(10),
  age integer
);
--
/* table of courses */
create table courses
( cno varchar(5),
  title varchar(10),
  credits integer
);
/* table of students and the courses they take */
create table take
( sno integer,
  cno varchar(5)
);

The question is: Find students who do not take CS112

I came up with:

with s1 as (
    select sno from student except select s.sno from student s inner join take t on s.sno = t.sno where t.cno = 'CS112'
)
select s.* from student s inner join s1 on s.sno = s1.sno 

Rozenshtein:

select *
from student
where sno not in (select sno
                  from take
                  where cno = 'CS112')

SQL Cookbook style 1: Group by

select s.sno, s.sname, s.age 
from student s left join take t on (s.sno = t.sno) 
group by s.sno, s.sname, s.age
having max(case when t.cno = 'CS112' then 1 else 0 end) = 0;

SQL Cookbook style 2: Window function

select distinct sno, sname, age
from (
    select s.sno,
           s.sname,
           s.age,
           max(case when t.cno = 'CS112' then 1 else 0 end) over (partition by s.sno, s.sname, s.age) as takes_CS112
    from student s left join take t on (s.sno = t.sno)
) as x
where takes_CS112 = 0;

I just wanted your opinion on how my query would perform compared to the other queries and if they are in the same ballpark, especially the SQL Cookbook queries. I'm targeting PostgreSQL.

\$\endgroup\$
2
\$\begingroup\$

I would personally use the JOIN, because the query here is fairly simple. You are just matching two tables.
A subselect is handy for more complex lookups though.

But each DBMS has its own way of parsing and optimizing queries.
You should run the execution plan on each query and compare results.

Like with Mysql, use the EXPLAIN command to determine which one would perform better. To get representative results you should fill up the tables with sample data. Add a least a few thousand records. Also add indexes where appropriate. It could be interesting to run the tests with and without indexes.

The presence (or lack) of indexes will affect the query optimizer. Sometimes you have to provide hints, if the query optimizer does not choose the 'best' index.

It should be noted that performance is related to good DB design. These table examples have no primary key, no index etc.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.