SoQL Join Examples

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 socrata-support@tylertech.com 

Datasets used in this document

This article uses the same datasets as the shown W3Schools examples. Here they are:

Dataset 1:

mceclip0.png

Dataset 2:

mceclip1.png

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

mceclip2.png

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

mceclip3.png

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

mceclip4.png

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 

mceclip5.png

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

mceclip6.png

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
Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

Comments

0 comments

Article is closed for comments.