CODEWITHSUNDEEP
sqljoinsubqueryinner-join
nonopolarity
nonopolarity

Reputation: 151166

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

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?

Upvotes: 1

Views: 342

Answers (2)

Mark Byers
Mark Byers

Reputation: 838876

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.

Upvotes: 3

Guffa
Guffa

Reputation: 700650

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

Upvotes: 4

Related Questions