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.
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
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.