Is there a way to rewrite this SQL query with '(NOT) IN' instead of 'NOT EXISTS'?

Question

The task was to write an SQL query that returns the first ever inaugurated president. The two attributes used from the table administration should be self-explanatory. Here you can see my solution which I'm confident in being correct.

SELECT A1.pres_name, A1.year_inaugurated
FROM administration A1
WHERE NOT EXISTS
    (SELECT NULL
     FROM administration A2
     WHERE A1.year_inaugurated > A2.year_inaugurated);

As I'm trying to learn SQL, I thought of any other ways to write this query but couldn't find any. Are there any other solution that do not use NOT EXISTS? But instead use IN, NOT IN, EXISTS? Another constraint is to not use the MIN function. And if there are than one solution that do not use NOT EXISTS, I would be very happy to see all of them to learn the most from it.

I'm not sure. I am very new to SQL and these president tables were already there when I installed SQL developer — Tymo, Jul 03 '20 at 19:06

score 1 · Accepted Answer · answered Jul 03 '20 at 18:49

You can't really write this using NOT IN, but you could write it using a scalar subquery:

SELECT A1.pres_name, A1.year_inaugurated
FROM administration A1
WHERE A1.year_inaugurated = (SELECT MIN(A2.year_inaugurated)
                             FROM administration A2
                            );

Or -- assuming that there are not any duplicate years -- using ORDER BY and a way to limit the rows:

SELECT A1.pres_name, A1.year_inaugurated
FROM administration A1
ORDER BY A1.year_inaugurated ASC
FETCH FIRST 1 ROW ONLY;

Thank you very much! I like the alternative solutions you wrote there, they make alot of sense. — Tymo, Jul 03 '20 at 18:56

score 1 · Answer 2 · answered Jul 03 '20 at 19:05

1

Another way to get the result that you want is with the operator ALL:

SELECT pres_name, year_inaugurated
FROM administration
WHERE year_inaugurated <= ALL (SELECT year_inaugurated FROM administration)

answered Jul 03 '20 at 19:05

forpas

160,666
10
38
76

1

I totally did not think of the ALL operator.. that's another good solution, thank you! – Tymo Jul 03 '20 at 19:08

score 1 · Answer 3 · answered Jul 03 '20 at 19:25

1

I think this is pretty much Gordon's 2nd answer, but I'm not familiar with FETCH in SQL server so I would use top.

select top(1) a1.pres_name, a1.year_inaugurated
from administration a1
order by a1.year_inaugurated asc

answered Jul 03 '20 at 19:25

avery_larry

2,069
1
5
17

Yes this seems to be the same as Gordon's answer, however I didn't know the top syntax yet and it's good to know about it, thank you! – Tymo Jul 03 '20 at 21:34

Is there a way to rewrite this SQL query with '(NOT) IN' instead of 'NOT EXISTS'?

3 Answers3