Here are some alternative methods.

You could create a View from your #veg_tab and #non_veg_tab tables, that includes an additional column (stype) indicating the original source table of the row ('N' or 'V'), and join to this view on the sid and stype columns.

If you are not allowed/willing to create a View, you could join to a derived table or a CTE created from your #veg_tab and #non_veg_tab tables.

SELECT MGR.sno, MGR.sname, FOOD.item_name, FOOD.stype

FROM #mgr_tab MGR

JOIN (

SELECT 'V', sid, veg_name FROM #veg_tab

UNION ALL

SELECT 'N', sid, non_veg_name FROM #non_veg_tab

) FOOD(stype, sid, item_name)

ON (MGR.sid = FOOD.sid AND MGR.stype = FOOD.stype)

;WITH FOOD(stype, sid, item_name) AS (

SELECT 'V', sid, veg_name FROM #veg_tab

UNION ALL

SELECT 'N', sid, non_veg_name FROM #non_veg_tab

)

SELECT MGR.sno, MGR.sname, FOOD.item_name, FOOD.stype

FROM #mgr_tab MGR

JOIN FOOD ON (MGR.sid = FOOD.sid AND MGR.stype = FOOD.stype)