CODEWITHSUNDEEP
sqlexistsnot-existsnotin
Tymo
Tymo

Reputation: 77

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

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.

Upvotes: 0

Views: 628

Answers (3)

avery_larry
avery_larry

Reputation: 2135

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

Upvotes: 1

forpas
forpas

Reputation: 164154

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)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270573

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;

Upvotes: 1

Related Questions