This article includes an overview of SOQL join limitations along with examples of inner joins, left joins, right joins, full joins, and self-joins. The SoQL query editor and its join capability are only available on the Enterprise Data Platform.
The Limitations
In SoQL there are some limitations that are a little different from SQL. Here is a list of the current known limitations:
- You can not do nested queries
- You can join point columns however they will not remain geocoded
- You can not join on SoQL created columns ie columns that you created from a SoQL view
NOTE: this is not an exhaustive list, there may be limitations that are not listed here. If you have any questions please reach out at datainsights-support@tylertech.com
Datasets used in this document
This article uses the same datasets as the shown W3Schools examples. Here they are:
Dataset 1:
Dataset 2:
The Joins
Using the SoQL Query Editor you can Join two datasets together to use their data in the same table. These are the same type of joins as SQL Joins but with a slightly different syntax, so if you need an overview of the concepts of joins you can view them here.
In Data & Insights, you can create an inner join, left join, right join, full join, or self join.
Inner Join
SQL Doc This type of join will omit information that does not match your join case.
Table
Query
SELECT
orderid,
customerid,
orderdate,
@cust.id,
@cust.customername,
@cust.contactname,
@cust.address,
@cust.city,
@cust.postalcode,
@cust.country
JOIN @x3in-h3hj as cust ON @cust.id = customerid
Left Join
SQL Doc This type of join will pull in all records from the left side of the join condition but only records from the right that have a match.
Table
Query
SELECT
orderid,
customerid,
orderdate,
@cust.id,
@cust.customername,
@cust.contactname,
@cust.address,
@cust.city,
@cust.postalcode,
@cust.country
LEFT OUTER JOIN @x3in-h3hj as cust ON @cust.id = customerid
Right Join
SQL Doc This type of join will pull in all records from the right side of the join condition but only records from the left that have a match.
Table
Query
SELECT
orderid,
customerid,
orderdate,
@cust.id,
@cust.customername,
@cust.contactname,
@cust.address,
@cust.city,
@cust.postalcode,
@cust.country
RIGHT OUTER JOIN @x3in-h3hj as cust ON @cust.id = customerid
Full Join
SQL Doc This Join will pull all records from both tables. It will put records with matches on the same line.
Table
Query
SELECT
orderid,
customerid,
orderdate,
@cust.id,
@cust.customername,
@cust.contactname,
@cust.address,
@cust.city,
@cust.postalcode,
@cust.country
FULL OUTER JOIN @x3in-h3hj as cust ON @cust.id = customerid
Self Join
SQL Doc This Join will allow you to join a dataset with itself. It will behave like an inner join.
Table
Query
SELECT
id,
customername,
contactname,
address,
city,
postalcode,
country,
@self.id as self_id,
@self.customername as self_customername,
@self.contactname as selfcontactname,
@self.address as selfaddress,
@self.city as self_city,
@self.postalcode as self_postalcode,
@self.country as selfcountry
JOIN @x3in-h3hj as self on @self.city = city and @self.customername != customername
Notes on Datatypes
Joining on the URL datatype
The URL datatype is composed of two parts - a source string and a URI. If you want to join on this value, you must specify which part you want to join on. This is done using a period.
Example: JOIN @1234-abcd AS @dataset ON @dataset.column.uri = column.uri
Comments
Article is closed for comments.