A SQL Developer's Guide to Working with SOQL and the relationship queries

This blog post aims to help those familiar with SQL to query data in Salesforce databases using SOQL (Salesforce Object Query Language), which has a different syntax and functionality but similar concepts of querying data. It explores key differences between SQL and SOQL and provides examples of how to write effective SOQL queries. Additionally, it compares SQL joins and SOQL relationships when combining data from multiple tables, as they serve the same purpose but have important differences to consider.

If you're used to working with SQL, you might be wondering how to query data in Salesforce databases using SOQL (Salesforce Object Query Language). While the syntax and functionality of SOQL are different from SQL, the basic concepts of querying data remain the same. In this blog post, we'll explore some key differences between SQL and SOQL and provide examples of how to write effective SOQL queries.

Additionally, when working with databases, you often need to combine data from multiple tables. In SQL, this is typically done using joins, while in SOQL, it is done using relationships. While both concepts serve the same purpose of combining data, there are some important differences to consider. In this blog post, we will also compare SQL joins and SOQL relationships when relating multiple tables.

Understanding the Salesforce Data Model

Before we dive into SOQL, it's important to understand the Salesforce data model. Salesforce databases are composed of objects, which can be thought of as tables in SQL. Each object has fields, which can be thought of as columns in SQL. Objects are related to each other through relationships, which can be either lookup relationships or master-detail relationships.

The basic syntax for a SOQL query is as follows:

SELECT field1, field2, ... FROM object WHERE condition        

This is similar to SQL, where you select fields from a table based on a condition. Let's look at an example:

SELECT Id, Name FROM Contact WHERE MailingState = 'California'        

In this example, we're selecting the Id, and Name fields from the Contact object where the MailingState field is 'California'.

By the way, in SOQL you are not allowed to write a query like this:

SELECT * FROM object WHERE condition        

You must explicitly indicate which fields you are actually interested in retrieving. As we are going to see, this and few other features have to do with the performance of the queries.

Aggregates

In SQL, you can use aggregate functions like SUM, COUNT, and AVG to perform calculations on data. SOQL also supports aggregate functions. Here's an example:

SELECT COUNT() FROM Contact WHERE MailingState = 'California'        

In this example, we're using the COUNT() function to count the number of Contact records where the MailingState field is 'California'.

Relationships in Salesforce databases vs. SQL databases

When working with databases, it is often necessary to combine data from multiple tables. In SQL, this is typically done using joins, while in SOQL, it is done using relationships. In this section, we will compare SQL joins and SOQL relationships when relating multiple tables.

Using relationships in Salesforce databases with SOQL

In databases, relationships are an essential concept, as they allow you to link objects together to form a hierarchy. In SOQL, you can query related objects using dot notation. For example, to query the name of a contact and the name of the related account where the account name is "Acme Corporation", you could use the following SOQL query:

SELECT Name, Account.Name FROM Contact WHERE Account.Name = 'Acme Corporation'        

In this example, the dot notation is used to access the Account object's name field. Note that the relationship between Contact and Account objects is defined in the schema, and therefore, you do not need to indicate how you want to relate the two tables.

Using joins in SQL databases

In SQL databases, joins are used to combine data from two or more tables based on a related column between them, but this relationship does not have to be explicit in the schema. SQL allows developers to create ad-hoc relationships between tables, which means that you can join any two tables on any field that have values in common. For example, if you have a leads table and a contacts table, you could join them on fields such as last name, postal code, or any other field that they both share.

Here is an example of how to use an inner join to combine data from two tables in SQL:

SELECT *
FROM leads
INNER JOIN contacts
ON leads.email = contacts.email;        

In contrast to SQL, SOQL only allows you to relate Salesforce objects that are already related in the schema via lookups or master-detail relationships. This means that you cannot create ad-hoc relationships between tables in SOQL. For example, when a Lead is converted to a Contact, the relationship between the two is maintained through the "Converted Lead" field on the Contact object, which is a standard read-only lookup field that stores the ID of the Lead record that was converted to create the Contact record. You can only relate the Lead and Contact tables using the "Converted Lead" field.

Another important difference in SQL, is that it allows you to join tables with operators that are not necessarily the equals operator.

Let's say you have two tables, "employees" and "departments". The "employees" table has a column called "salary", and the "departments" table has a column called "budget". You want to join the two tables to find all employees whose salary is greater than their department's budget.

You can do this using a join with the greater than operator:

SELECT employees.name, departments.name
FROM employees
JOIN departments
ON employees.department_id = departments.id
WHERE employees.salary > departments.budget;        

In this example, the join is done on the "department_id" column in the "employees" table and the "id" column in the "departments" table, and the where clause specifies that the salary of each employee must be greater than the budget of their department.

The main reason why Salesforce does not allow this type of join, is that using operators other than equals in joins can be less efficient than using equals, as it can require more complex query planning and execution. It's important to carefully consider the performance implications of using non-equal joins in your queries.

In conclusion, both SQL and SOQL provide ways to combine data from multiple tables. However, while SQL allows for ad-hoc relationships between tables, SOQL only allows you to relate Salesforce objects that are already related in the schema.

What about subqueries?

In both SQL and SOQL, a subquery is a query nested inside another query. The results of the subquery are used in the outer query as a condition for filtering, and they can be used to retrieve data from multiple tables and perform calculations on the results.

SQL subqueries

In SQL, subqueries are enclosed in parentheses and can be used in various parts of a query, such as the SELECT, FROM, WHERE, and HAVING clauses. A subquery in the WHERE clause is called a correlated subquery because it depends on values from the outer query. A subquery in the SELECT or FROM clause is called a non-correlated subquery.

Here's an example of a non-correlated subquery in SQL:

SELECT name, (SELECT AVG(salary) FROM employees WHERE department = departments.id) FROM departments;        

This query retrieves the name of each department and the average salary of its employees. The subquery calculates the average salary for each department and returns the result as a column in the outer query.

Here's an example of a correlated subquery in SQL:

SELECT name, salary
FROM employees e
WHERE salary > (SELECT AVG(salary) FROM employees WHERE department = e.department);        

This query retrieves the name and salary of employees whose salary is greater than the average salary of their department. The subquery is correlated with the outer query by the department column.

Salesforce SOQL subqueries

In SOQL, subqueries are used to retrieve data from related objects. A subquery in SOQL is enclosed in curly braces and is used in the WHERE clause of the outer query. Unlike SQL, SOQL does not support correlated subqueries.

Here's an example of a SOQL subquery:

SELECT name, (SELECT name FROM Opportunities)
FROM Account
WHERE name = 'Acme Corporation';        

This query retrieves the name of the account and the names of all related opportunities for the account named 'Acme Corporation'. The subquery is enclosed in curly braces and is used in the WHERE clause of the outer query.

Contrast and compare the different types of joins

For this section, we are going to compare two Salesforce objects that are related: Accounts and Contacts.

For each of the following types of relationships, I am providing the syntax for both standard and custom relationships. Please note that custom relationships indicate the lookup or master-detail relationship was defined by the developer, not by Salesforce. This is actually very important to understand because the syntax is slightly different.

Notice that you could define your own relationship between Account and Contact, for example, to indicate a previous account that the contact used to work for. In this case, you are using a custom relationship between two standard objects.

Similarly, the CreatedBy, OwnerId, RecordTypeId are standard relationships that exist in your custom objects.

Do not assume a custom relationship exists only between custom objects!

Left Outer Join

Standard relationship:

SELECT Name, (SELECT Name FROM Contacts)
FROM Account        

Custom relationship:

SELECT Name, (SELECT Name FROM Children__r)
FROM Parent__c        

Left Anti-Join

Standard relationship:

SELECT Name
FROM Account
WHERE Id NOT IN (SELECT AcccountId FROM Contact)        

Custom relationship:

SELECT Name
FROM Parent__c
WHERE Id NOT IN (SELECT Parent__c FROM Child__c)?        

Right Outer Join

Standard relationship:

SELECT Name, Account.Name
FROM Contact        

Custom relationship:

SELECT Name, Parent__r.Name
FROM Child__c        

Right Anti-Join

Standard relationship:

SELECT Name 
FROM Contact
WHERE AccountId = null        

Custom relationship:

SELECT Name
FROM Child__c
WHERE Parent__c = null        

Inner Join

This particular join can be written in either left inner join or right inner join format, let's explore both of them

Standard relationship (Left Inner Join):

SELECT Name
FROM Account
WHERE Id IN (SELECT AccountId FROM Contact)?        

Custom relationship (Left Inner Join):

SELECT Name
FROM Parent
WHERE Id IN (SELECT Parent__c FROM Child__c)?        

Standard relationship (Right Inner Join):

SELECT Name, Account.Name
FROM Contact
WHERE AccountId != null        

Custom relationship (Right Inner Join):

SELECT Name, Parent__c.Name
FROM Child__c
WHERE Parent__c != null        

Performance Considerations

It is worth noting that SQL joins can be expensive in terms of performance, especially when joining large tables with fields that are not indexed. SOQL relationships, on the other hand, are optimized for querying data in Salesforce databases and take advantage of the platform's indexing and caching features to deliver fast performance.

Conclusion

Subqueries are a powerful tool for querying data in both SQL and SOQL. Although the syntax and functionality differ, the concept is the same. SQL supports both correlated and non-correlated subqueries, while SOQL only supports non-correlated ones.

However, SQL and SOQL differ in their approach to relating multiple tables. SQL allows developers to create ad-hoc relationships on any field, while SOQL only allows relating tables already related in the schema via lookups or master-detail relationships. This difference affects query syntax and performance, as SQL's approach can result in complex queries requiring more processing power.

Overall, understanding subqueries' similarities and differences is crucial for effective query writing. Additionally, the approach to relating multiple tables differs between SQL and SOQL, impacting query syntax and performance.

===

P.S. Thanks ChatGPT :-)