How can I filter if a name exists in a certain year in SQL?

Question

I am new to SQL and I am trying to solve the following problem:

I have a marathon table with 2 different years, where a marathon took place and a column that describes what athlete participated in a specific year. I want to return the names of the athletes who participated in 2001 but not in 2000. I've tried different queries (exists, not in, and not etc.) so far but none is working.

year	athlete
2000	White
2000	Adams
2000	Smith
2001	White
2001	Johnson

Answer 1

In postgres, you may use EXCEPT...

    select athlete
    from marathon
    where year = 2001
    except
    select athlete
    from marathon
    where year = 2000

Output:

    Johnson

Answer 2

You could also use an aggregate function and arrays to make the comparison:

SELECT  athlete
FROM    marathon
GROUP BY athlete
HAVING  NOT ARRAY_AGG(year) @> ARRAY[2000];

And when looking for athletes who didn't participate in both years:

SELECT  athlete
FROM    marathon
GROUP BY athlete
HAVING  NOT ARRAY_AGG(year) @> ARRAY[2000,2001];

Or did participate in 2000 or 2001:

SELECT  athlete
FROM    marathon
GROUP BY athlete
HAVING ARRAY_AGG(year) && ARRAY[2000,2001];

Answer 3

Are you looking for something as simple as

SELECT * FROM `ATHLETES_TABLE` WHERE `year` = '2001';

Edit: For only the names, you would pass the name columnn to the SELECT

SELECT athlete FROM `ATHLETES_TABLE` WHERE `year` = '2001';