It is easy to join tables on SQL server,
SELECT column_name(s) FROM table1 INNER JOIN table2
ON table1.column_name = table2.column_name;
But how would you do the same thing over the HTTP protocol?
Generally speaking, there are two direction you can go.
Option 1. Use SQL injection
Neither Socrata SODA API (SoQL) nor ESRI arcgis REST API allow you to embed the full SQL statement (with JOIN) in the URL as a string due to security reason.
Option 2. Developing a sophisticated mechanism to fullfil the join.
You can JOIN table with Socrata SoQL query editor watch this video Socrata Level 3 Training Video - SoQL Join Statements https://www.youtube.com/watch?v=ik14YcVQfRw
However
On the API level, there is no $join keywords provided in SoQL. Check SoQL keywords. SoQL query editor is manual operation.
We still need Socrata provide $join API in the future to automate JOIN process on the fly.
ESRI Arcgis REST API allow you join feature with GeoAnalytics
https://developers.arcgis.com/rest/services-reference/enterprise/join-features.htm
Also check article Join feature on Portal for ArcGIS
https://gislab.depaul.edu/portal/portalhelp/en/portal/latest/use/geoanalytics-join-features.htm
Most of organization do not have a GeoAnalytics service.
GeoAnalytics service API is very complicated, not fit for you and me.
This is how you can join two layer. First you submit a join task URL via REST API. Then you get a job ID as token. You periodically check job status by send URL with token until you get a response tells you that job is successfully completed. Then you can access result feature layer by job ID as token. You can automated the process but its way complicated.
We need ESRI provide a join API in the same style as current feature service.
One URL to do all jobs on the fly, no waitting time, no token, no job ID needed.