In SQL, can we always write an inner join statement as a main query and subquery if we only want to find the intersection?

Question

In SQL, can we always write an inner join statement as a main query and subquery or vice versa if we only want to find the intersection?

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. But if all we care is to find the intersection, without caring what is being displayed, then we can always convert one to the other?

Answer 1

No, you can only do that if you are joining on a single column. It won't work when tables are connected via multi-part keys.

For example:

select g.* from gifts g
join sentGifts s on s.Number=g.Number and s.Name=g.Name

If the only unique identifier on both tables is the combination of (Number, Name), then there's no way to convert the above into a subquery-type statement.

Answer 2

In set terms, both queries will find the intersection of the two sets, so that does not matter which form you use.

You will always be able to convert one form to the other.

As a matter of practical use - most RDBMSs will perform better if you use a set based approach with JOINs (they are better optimized for joins). As you mentioned, there is also the matter of the returned result set - you have to use a join if you need data from both sets.