In SQL, can we always write an inner join statement as a main query and subquery?

Question

In SQL, can we always write an inner join statement as a main query and subquery?

For example,

 select * from gifts g where g.giftID in (select giftID from sentGifts);

can do a join and show the gifts sent in the sentGifts table, but it won't be able to show the sentTime because that is inside the subquery?

Answer 1

No, you can't rewrite all INNER JOINs like that.

Sometimes your inner join condition will involve more than one column and then IN won't necessarily work.

Also, sometimes the inner join condition will be a range or more complex expression where this query/subquery technique won't work at all, for example: finding overlapping intervals.

Answer 2

No, you can only use that if the condition is a single value.

You can't do that to a query like:

select *
from gifts g
inner join sentGifts s on s.giftType = g.giftType and s.giftDate = g.giftDate