I want all 200 rows between 400 and 600 regardless of if the value in the second table column exists
So given two joined tables, "left" and "right", you want to include every row from the "left" table and matching rows in the "right" table if they happen to be there?
That's called a Left [Outer] Join.
You'll have to ...
For the "isexpired" column, don't store it as a literal column: use a view to determine that status dynamically. That way status is accurate to the second, at runtime.
create table passwordResetLink(
link_id number(5) primary key,
link_date timestamp(0) default sysdate NOT NULL,
email varchar2(110) UNIQUE NOT NULL,
Rather than query a table, why not query a view that has your rules built in?
CREATE VIEW vwPasswordResetLink
CASE WHEN sysdate > link_date + INTERVAL '1' DAY THEN 1
ELSE 0 END AS isExpired,
WHERE link_date >= sysdate - INTERVAL '7' ...
Please see this link in Stack Overflow answers all question - agg distinct - is now built in some versions and this post also shows work around (polyfill) for older versions.