SQL JOIN EXPLANATION

Question

Can anyone explain what is happening here? The query uses the "IN" condition during the second left join instead of "ON" condition. What does "IN" condition do in joins?

SELECT
   * 
FROM
   PlacementDetail_Temp PDT 
   LEFT JOIN
      dbo.[Authorization] AUTH 
      on AUTH.PlacementDetailID = PDT.PlacementDetailID 
      and AUTH.PayorID = @PayorID 
   LEFT JOIN
      dbo.Provider SP_as_PR 
      on SP_as_PR.ProviderID in 
      (
         Select
            PR.ProviderID 
         from
            dbo.Provider PR 
            INNER JOIN
               dbo.ProviderSponsor PS 
               ON PR.ProviderID = PS.ProviderID 
               and PS.SponsorID = AUTH.SponsorID 
         where
            IsSponsor = 'True'
      )

The `on` condition can contain *any* boolean condition, including subqueries. — Gordon Linoff, Mar 12 '19 at 14:57
Welcome thiruvengadam! The 'in' is not a join, is a subquery. — Antonio Veneroso Contreras, Mar 12 '19 at 14:58
when the person uses in that means they check if they find any values within the parenthesis(sub query) as written. — Tito, Mar 12 '19 at 15:01
Welcome to StackOverflow. Few comments on your question: 1) Don't use caps locks, it's very aggressive. 2) please properly format your query, one line is not in code block and you have useless spaces 3) highlight relevant clause so that readers don't need to find out which `IN` you are talking about. Finally, for `SP_as_PR.PRroviderID in (...)`, please check https://www.w3schools.com/Sql/sql_in.asp — Al-un, Mar 12 '19 at 15:02
Possible duplicate of [What is the difference between "INNER JOIN" and "OUTER JOIN"?](https://stackoverflow.com/questions/38549/what-is-the-difference-between-inner-join-and-outer-join) — Robson, Mar 12 '19 at 15:14
What did you learn about that use of IN in your research? See [ask] & the voting arrow mouseover texts. What are your table definitions? What is a simpler example you could ask about? [mcve] What is a specific question you are asking about a specific small piece of code? Before considering posting please always google many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names, & read many answers. If you post a question, use one phrasing as title. PS w3schools is poor, read a product manual and/or published academic textbook. — philipxy, Mar 13 '19 at 04:17

score 0 · Answer 1 · answered Mar 12 '19 at 15:36

As people said in the comments, the ON is a sub-query. It looks particularly ugly as the conditions for the second left join don't have any direct relationship to the first table.

So the coder is asking for the provider table to be brought back with all providers in the table that are sponsors. This means that potentially there will be multiple matches for placement_detail, and therefore many duplicate rows.

I'd have expected to see something like

ON PDT.Provider_ID=SP_as_PR.Provider_ID AND SP_as_PR.Priovide_ID IN (...

score 0 · Answer 2 · answered Mar 12 '19 at 16:00

The ON clause contains the condition which Provider rows to join to the Authorization rows. In your case this condition is an IN clause. IN clauses are usually not correlated, but yours is, which is not wrong, but it would be more typical to use EXISTS then. Moreover the Provider in the subquery is superfluous, which makes the clause even less readable.

What the query is trying to do is:

LEFT JOIN dbo.provider pr ON (pr.providerid, auth.sponsorid) IN
(
   SELECT providerid, sponsorid
   FROM dbo.providersponsor
   WHERE issponsor = 'true'
)

but SQL Server doesn't allow tuples in an IN clause as other DBMS do. With the shortened clause you see that we join all providers with their sponsors.

In case there are no duplicate provider/sponsor tuples in ProviderSponsor, you could just as well join the table along. (The IN or EXISTS clause only makes sense in order to avoid duplicates.)

SELECT * 
FROM placementdetail_temp pdt 
LEFT JOIN dbo.authorization auth ON auth.placementdetailid = pdt.placementdetailid 
                                 AND auth.payorid = @payorid 
LEFT JOIN dbo.providersponsor ps ON ps.sponsorid = auth.sponsorid AND ps.issponsor = 'True'
LEFT JOIN dbo.provider pr ON pr.ProviderID = ps.ProviderID;

(It is not clear from your query, whether IsSponsor resides in ProviderSponsor or Provider. In my queries I assume it to be in ProviderSponsor. If it is in Provider instead, move the condition where it belongs.)

SQL JOIN EXPLANATION

2 Answers2