Using the for XML PATH will affect cross Apply return

Yesterday, when writing a statement, encountered a phenomenon, is actually using cross apply to do a concatenation of strings. Like what

CREATE TABLEGoodscatalog (IDINT, NameNVARCHAR( -))    CREATE TABLEGoods (IDINT, GoodscatalogidINT, NameNVARCHAR( -))  INSERT  intoGoodscatalog (ID, Name)VALUES(1,'Fruit'),(2,'Sporting Goods')INSERT  intoGoods (Id,goodscatalogid, Name)VALUES(1,1,'Apple')       ,(2,1,'Banana')       ,(3,2,'Football')       ,(4,2,'Basketball')SELECTA.*,        STUFF(B.goodname,1,1,"') asGoodname fromGoodscatalog a CrossAPPLY (SELECT '-' +B.name fromGoods bWHEREB.goodscatalogid=a.ID forXML PATH ("')) asB (goodname)/*ID Name GoodName1 fruit Apple-Banana 2 sporting goods football-basketball*/

It's normal, isn't it? But what if we add 2 more data to the Goodscatalog table? It's going to turn out like this. The cross apply will not return the row that generated the result set. Why do you still do that?

INSERT  intoGoodscatalog (ID, Name)VALUES(3,'Seafood'),(4,'clothes')SELECTA.*,        STUFF(B.goodname,1,1,"') asGoodname fromGoodscatalog a CrossAPPLY (SELECT '-' +B.name fromGoods bWHEREB.goodscatalogid=a.ID forXML PATH ("')) asB (Goodname)
/*ID Name GoodName1 fruit Apple-Banana 2 sporting Goods soccer-Basketball 3 seafood NULL4 clothes NULL*/

-------------------------------------------This is to describe me as a tease split line---------------------------------------------------------------- ----------------------------------------------

Re-read the use of apply in the Xia Lian machine document

Use the APPLY operator to invoke a table-valued function for each row returned by an external table expression that implements a query operation. The table-valued function acts as the right input and the outer table expression as the left input. The resulting row is combined as the final output by evaluating the right input for each row in the left input. The list of columns generated by the APPLY operator is the column set in the left input, followed by the list of columns returned by the right input.

That is, whether cross apply or Outer apply is followed by a table-valued function that crosses each row of the input table on the left. So whether the return should look () inside the statement itself.

Here I have questions again, Goods table inside no 3,4 result ah, why still can show.

This is the problem with the function. If we compare 2 table-valued functions, it's clear.

CREATE FUNCTIONTestxml (@GoodsCatalogID INT)RETURNS @TABLE TABLE(Goodnamevarchar( $)) as    begin    ; withCTE (Goodname) as    (SELECT '-' +Name fromGoodsWHEREGoodscatalogid= @GoodsCatalogID  forXML PATH ("'))    INSERT  into @TABLE(Goodname)SELECTGoodname fromCTERETURNENDCREATE FUNCTIONTestTable (@GoodsCatalogID INT)RETURNS @TABLE TABLE(Goodnamevarchar( $)) as    begin    INSERT  into @TABLE(Goodname)SELECTName fromGoodsWHEREGoodscatalogid= @GoodsCatalogID    RETURNENDSELECT *     fromDbo. Testxml (3)/*Goodnamenull*/SELECT *      fromDbo. TestTable (3)/*Goodname*/

One has to return, the other does not return OH ~ ~ This will know why you can cross to the value of it.

--------------------------------------------------------------------------------------this is to prove that I'm not serious about the split line of the face--------------------- ---------------------------------------------------------------------------------------------------

The problem was discovered purely because of the unclear use of apply. ╮ (╯_╰) ╭~

Caught dead for everyone.

Using the for XML PATH will affect cross Apply return