SOQL vs SQL: Best Practices to Query Salesforce Database

When it comes to working with Salesforce, we need SOQL. It’s similar to SQL but with its own features. Both are about fetching data, but SOQL is tailored specifically for Salesforce’s database structure, while SQL is more universal. By sticking to best practices in SOQL, like using selective filters and avoiding over-fetching, businesses can avoid performance bottlenecks and stay within Salesforce’s governor limits. 

On the other hand, neglecting these best practices can lead to slower queries, hitting data limits, and frustrated users. 

Whether you’re trying to streamline reporting, enhance app performance, or just keep the system running smoothly, mastering SOQL techniques can save time and headaches. Let’s dive deeper into some best practices for making requests to the Salesforce database and how they tackle common business pain points.

Table of contents

1. What is SQL?
2. What is Salesforce SQL (SOQL)?
3. Types of SOQL Queries
4. SOQL vs. SQL: Key Differences
5. Syntax Comparison: SOQL vs. SQL
6. Best Practices to Query Salesforce Database
7. Conclusion: When to Use SOQL vs. SQL?

What is SQL?

Structured Query Language, or SQL, is like the universal ability to chat with databases. It’s what we use to ask database questions, update info, delete unnecessary stuff, or even redesign the structure of the database itself. With SQL, businesses can:

• Grab specific data (using SELECT).
• Add new info (INSERT).
• Make updates (UPDATE).
• Clean house (DELETE). 

It’s the backbone of popular database systems like MySQL, PostgreSQL, Oracle, and Microsoft SQL Server.

Now, here’s the catch: Salesforce has its twist on SQL. Instead of standard SQL, Salesforce uses Salesforce Object Query Language (SOQL), which is tailored to work with Salesforce’s unique database structure. 

Let’s jump into what makes SOQL special.

What is Salesforce SQL (SOQL)?

As we have noticed above, Salesforce Object Query Language (SOQL) is another version of SQL. Still, while the first one is querying traditional relational databases, SOQL works with Salesforce’s unique database structure, which is centered around objects instead of tables. Think of it as SQL’s cousin who knows all the ins and outs of Salesforce.

With it, users can pull data from Salesforce objects, like Accounts, Contacts, or opportunities, efficiently and accurately.

This approach makes it super focused and efficient for retrieving exactly what businesses need without breaking anything.

Salesforce leverages it to make data retrieval easy and scalable, whether running reports, building dashboards, or integrating with other systems. Companies can use SOQL in tools like Apex, Salesforce’s developer console, or even through APIs when connecting Salesforce to external apps.

Types of SOQL Queries

When working with Salesforce data using SOQL, there’s more than one way to get what you need. SOQL offers a few different types of queries, each designed for specific use cases. 

Let’s dive into the main ones and how they can make your life easier. 

Basic Queries

These are the bread-and-butter SOQL queries, simple and straightforward. You use them to fetch data from a single object. 

Examples:

SELECT.  Retrieving Data from an Object.

SELECT Name, Industry FROM Account

This pulls the names and industries of all accounts. 

INSERT. Adding a New Record (Handled in Apex, Not SOQL).

Note: Unlike SQL, SOQL is read-only, so inserting data requires Apex DML statements.

Account newAccount = new Account(Name = 'TechCorp', Industry = 'Technology');
insert newAccount;

This creates a new account in Salesforce.

UPDATE. Modifying Existing Records.

Note: Updating data also requires Apex DML.

Account acc = [SELECT Id, Name FROM Account WHERE Name = 'TechCorp' LIMIT 1];
acc.Industry = 'Software';
update acc;

Used to update the industry field for the account named TechCorp.

DELETE. Removing a Record.

Account acc = [SELECT Id FROM Account WHERE Name = 'TechCorp' LIMIT 1];
delete acc;

This deletes the TechCorp account from Salesforce.

COUNT. Finding the Number of Records.

SELECT COUNT(Id) FROM Contact

This one counts the total number of contacts in Salesforce.

LIMIT. Restricting Query Results.

SELECT Name FROM Account LIMIT 10

It retrieves only the first 10 accounts.

Filtered Queries

Sometimes, businesses don’t need all the data, just the stuff that matches specific conditions to narrow things down using the WHERE clause. Here, you’re getting all opportunities where the deal has already been won.

Example:

SELECT Name, CloseDate FROM Opportunity WHERE StageName = 'Closed Won'

Perfect for focusing on success stories.

Parent-to-Child Queries (a.k.a. Relationship ones)

They let companies fetch data from a parent object and its related child objects in one go. We’ll use subqueries for this magic. This grabs account names along with the last names of all their related contacts. It’s a two-for-one deal.

Example:

SELECT Name, (SELECT LastName FROM Contacts) FROM Account

Child-to-Parent Queries

These ones pull data from a child object and reference fields from its parent one, such as fetching contact names along with the name of the related account. 

Example:

SELECT FirstName, LastName, Account.Name FROM Contact

It’s like seeing who works where.

Aggregate Queries

Need some quick math? Aggregate functions like COUNT(), SUM(), and AVG() let users calculate data right in the query. This counts the number of accounts grouped by their industry. 

Example:

SELECT COUNT(Id), Industry FROM Account GROUP BY Industry

Perfect for spotting trends or tracking growth.

Geolocation Queries

Got location-based data? SOQL can query it using geolocation fields with functions like DISTANCE or GEOLOCATION. This one grabs all accounts within 50 miles of San Francisco.

Example:

SELECT Name FROM Account WHERE DISTANCE(BillingAddress, GEOLOCATION(37.7749, -122.4194), 'mi') < 50

Great for planning local campaigns.

Querying with Sorting

Want your results in a specific order? Add ORDER BY to sort the data. 

Example:

SELECT Name, AnnualRevenue FROM Account ORDER BY AnnualRevenue DESC

This sorts accounts by revenue, showing the big fish first.

SOQL vs. SQL: Key Differences

Users who have worked with SQL before might be happy to see how SOQL fits into the picture. Both fetch data, but SOQL is purpose-built for Salesforce, while SQL works with traditional relational databases. They share some similarities but have key differences, especially in handling relationships, performance, and scalability.

Let’s break it all down, including their strengths and best use cases.

Now, let’s compare both query languages in a clear and organized way.

Criteria	SOQL (Salesforce Object Query Language)	SQL (Structured Query Language)
Purpose	Designed for querying Salesforce objects and records.	Used for querying relational databases (MySQL, PostgreSQL, etc.).
Syntax	Limited to SELECT statements (no INSERT, UPDATE, or DELETE).	Supports full CRUD operations (SELECT, INSERT, UPDATE, DELETE).
Querying Relationships	Uses relationship queries (Parent-to-Child & Child-to-Parent) with subqueries.	Uses JOIN operations for table relationships.
Filtering Data	Uses WHERE clause but lacks advanced operators like HAVING.	Supports WHERE, HAVING, GROUP BY, and advanced conditions.
Aggregation	Supports COUNT(), SUM(), AVG(), MAX(), MIN() but requires GROUP BY for more complex queries.	More robust aggregation with advanced functions and HAVING.
Performance	Works well for structured, object-based data but isn’t designed for large-scale, complex joins.	Can handle massive datasets with advanced indexing and optimization techniques.
Data Storage Structure	Data is stored as objects (e.g., Account, Contact) with predefined relationships.	Data is stored in tables with rows and columns.
Indexing and Optimization	Queries are optimized using governor limits, indexed fields, and selective filtering.	Uses advanced indexing, partitioning, and query optimization techniques.
Security and Access Control	Follows Salesforce security settings (Profiles, Permission Sets, Sharing Rules).	Uses database access control mechanisms (Roles, Privileges, Views).
Scalability	Limited by Salesforce Governor Limits (e.g., 50,000 record retrieval limit).	Scales efficiently with proper database optimization.
Full-Text Search	Uses Salesforce Object Search Language (SOSL) for full-text search across multiple objects.	Supports LIKE, FULLTEXT INDEX, and search engines (e.g., Elasticsearch).
Integration with External Systems	Requires APIs, middleware, or ETL tools to connect with external databases.	Natively supports integrations via ODBC, JDBC, and APIs.
Use Cases	Best for querying Salesforce data for reports, dashboards, and Apex.	Ideal for handling complex, large-scale relational database queries.

So, at first glance, both solutions might seem similar, but they are built for different environments. SQL is a universal database language used to manage and manipulate relational databases, while SOQL is specifically designed for querying Salesforce data.

Key Differences between SOQL and SQL

1. Read-Only vs. Data Manipulation. SOQL is strictly used for retrieving records; it doesn’t support direct data modifications. In contrast, traditional query languages allow operations like INSERT, UPDATE, and DELETE.  To modify records in Salesforce, changes must be made through the user interface or Apex DML statements.
2. Data Storage Structure. Standard databases store information in tables with rows and columns representing records. Salesforce, however, organizes data as objects, which function like structured records with predefined relationships.
3. Relationship Queries vs. Traditional Joins. Unlike relational databases that support complex JOIN operations across multiple tables, Salesforce queries only work with predefined relationships. Instead of arbitrary joins, it uses relationship queries to fetch connected data. data from related objects.
4. No SELECT * for Fetching All Fields. In many databases, a simple SELECT * retrieves all fields from a table. Salesforce requires users to explicitly specify the fields they need (e.g., SELECT Name, Industry FROM Account). This limitation helps maintain performance and efficiency in a multi-tenant environment, preventing excessive data retrieval.
5. Querying Related Records. Since standard SQL supports flexible joins between tables, data can be retrieved from unrelated sources. In Salesforce, queries are limited to objects with an existing relationship (parent-child or lookup).

Example of a Parent-to-Child Query (Fetching Related Child Records):

SELECT Name, (SELECT LastName FROM Contacts) FROM Account

This one retrieves Accounts along with their related Contacts.

Example of a Child-to-Parent Query (Referencing Parent Object Fields):

SELECT FirstName, LastName, Account.Name FROM Contact

It fetches Contacts while pulling the associated Account Name.

Syntax Comparison: SOQL vs. SQL

Even though both query languages share some similarities, their syntax has key differences due to the object-based structure of Salesforce data versus the table-based structure of relational databases. 

• SOQL requires explicit field selection (SELECT * is not allowed).
• No arbitrary JOIN in SOQL; relationship queries replace joins.
• SOQL uses governor limits, requiring performance optimization.
• Full-text searches require SOSL, while SQL relies on LIKE.
• Both SQL and SOQL use ORDER BY, LIMIT, and GROUP BY, but SOQL has fewer aggregate functions.

Below is a breakdown of some common queries to highlight how they differ in terms of syntax and functionality.

1. Selecting All Records

SQL (Can use SELECT * to fetch all fields from a table):

SELECT * FROM Customers;

SOQL (Must explicitly specify fields, no SELECT *):

SELECT Name, Phone, Email FROM Contact;

Why? SOQL requires specifying fields to prevent excessive data retrieval in Salesforce’s multi-tenant environment.

2. Filtering Data Using WHERE Clause

SQL (Filtering with conditions):

SELECT Name, Industry FROM Customers WHERE Industry = 'Technology';

SOQL (Same WHERE syntax, but objects instead of tables):

SELECT Name, Industry FROM Account WHERE Industry = 'Technology';

Why? The structure is similar, but SQL queries tables while SOQL queries Salesforce objects.

3. Sorting Data with ORDER BY

SQL:

SELECT Name, Age FROM Customers ORDER BY Age DESC;

SOQL:

SELECT Name, Age FROM Contact ORDER BY Age DESC;

Why? The syntax is identical, but Salesforce has query performance limits, so sorting large datasets requires optimization.

4. Limiting Query Results

SQL (LIMIT clause to restrict results):

SELECT Name FROM Customers LIMIT 10;

SOQL (Same LIMIT functionality):

SELECT Name FROM Contact LIMIT 10;

Why? SQL and SOQL use LIMIT, but it is crucial to stay within governor limits in Salesforce.

5. Using Aggregate Functions

SQL (Aggregating data with GROUP BY):

SELECT Industry, COUNT(*) FROM Customers GROUP BY Industry;

SOQL (Supports limited aggregate functions, requires GROUP BY):

SELECT Industry, COUNT(Id) FROM Account GROUP BY Industry;

Why? SOQL has fewer aggregate functions than SQL, but COUNT(), SUM(), AVG(), MIN(), and MAX() are supported.

6. Querying Related Records (JOIN vs. Relationship Queries)

SQL (JOIN to combine multiple tables):

SELECT Customers.Name, Orders.OrderNumber 
FROM Customers 
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

SOQL (Uses relationship queries instead of JOIN)

Parent-to-Child Query (Subquery for related child records):

SELECT Name, (SELECT OrderNumber FROM Orders) FROM Account;

Child-to-Parent Query (Direct reference to parent fields):

SELECT FirstName, LastName, Account.Name FROM Contact;

Why? Unlike SQL, SOQL does not support arbitrary joins. Instead, it uses relationship queries based on predefined object relationships.

7. Searching for Text (SOQL Has SOSL for Full-Text Search)

SQL (Using LIKE for partial text search):

SELECT Name FROM Customers WHERE Name LIKE '%John%';

SOQL (LIKE works the same way but is limited to specific fields):

SELECT Name FROM Contact WHERE Name LIKE '%John%';

Salesforce-Specific Alternative: SOSL (Full-text search across multiple objects):

FIND 'John' IN ALL FIELDS RETURNING Contact(Name), Account(Name);

Why? Unlike SQL, SOSL is Salesforce’s full-text search tool, allowing queries across multiple objects.

Best Practices to Query Salesforce Database

Writing efficient SOQL queries is crucial for maintaining performance, staying within Salesforce’s governor limits, and ensuring smooth data retrieval. Unlike traditional SQL, it operates in a multi-tenant environment, so it helps prevent system slowdowns and unnecessary resource consumption.

Let’s consider what to follow when querying the Salesforce database.

1. Use Selective Queries to Stay Within Limits

Salesforce enforces strict governor limits, restricting the number of records a query can return. Always use filters (WHERE clauses) to narrow down results instead of querying entire objects.

Good Example (Using a Filtered Query):

SELECT Name, Industry FROM Account WHERE Industry = 'Technology'

Bad Example (Querying All Records Without a Filter):

SELECT Name, Industry FROM Account

Why? The second query might hit the 50,000 record limit, causing errors or performance issues.

2. Avoid SELECT *  Always Specify Fields

Unlike SQL, SOQL does not allow SELECT *, but even if it did, retrieving unnecessary fields would waste resources. Always specify only the fields you need.

Good Example (Fetching Specific Fields):

SELECT Name, Email FROM Contact

Why? This improves query efficiency and reduces data load.

3. Use Relationship Queries Instead of Multiple Queries

SOQL supports parent-to-child and child-to-parent relationship queries, reducing the need for multiple queries.

Good Example (Fetching Related Child Records in One Query):

SELECT Name, (SELECT LastName FROM Contacts) FROM Account

Why? Instead of querying Accounts first and then separately querying Contacts, this fetches both in one go.

4. Limit the Number of Records Returned

Use the LIMIT clause to avoid exceeding row limits and to optimize query performance.

Good Example (Limiting Query Results):

SELECT Name FROM Account LIMIT 100

Why? This ensures you only fetch what you need, preventing excessive data retrieval.

5. Optimize Query Performance with Indexing

Indexed fields significantly improve query speed, especially for large datasets. Use selective filters on indexed fields like ID, Name, or external IDs.

Good Example (Filtering on an Indexed Field):

SELECT Name FROM Account WHERE Id = '0015g00000V9zXxAAJ'

Why? Queries on indexed fields run much faster than those on non-indexed fields.

6. Use ORDER BY and GROUP BY Wisely

Sorting and grouping data can slow down performance, so use them only when necessary.

Good Example (Sorting Efficiently):

SELECT Name FROM Account ORDER BY CreatedDate DESC LIMIT 50

Why? The LIMIT keeps performance in check.

Good Example (Using Aggregation Properly):

SELECT Industry, COUNT(Id) FROM Account GROUP BY Industry

Why? Aggregating a manageable number of records prevents query slowdowns.

7. Use Special Tools for More Advanced Querying

While SOQL is explicitly built for Salesforce, some platforms, like Skyvia, Salesforce SOQL Builder, Salesforce Inspector, Workbench, etc., offer SQL-like querying to make data retrieval easier and more flexible. They help users:

• Run queries.
• Explore relationships.
• Bypass some SOQL limitations by providing a more user-friendly interface.

For instance, Skyvia allows running SQL queries in its console to Salesforce, making it ideal for users familiar with traditional SQL syntax. 

 Example Query Using Skyvia:

SELECT Contact.FirstName, Contact.LastName, Account.Name 
FROM Contact 
INNER JOIN Account ON Contact.AccountId = Account.Id

Why? Skyvia lets you use standard SQL syntax, making queries more familiar and flexible.

How to Run SQL Query in Salesforce using Skyvia?

Skyvia allows users to run SQL queries directly on Salesforce data in this field, making reporting and analysis much easier. Whether you need to join Salesforce data with other cloud solutions, perform advanced aggregations, or automate queries, Skyvia provides a no-code, scalable solution.

In this guide, we’ll walk through the step-by-step process of setting up Skyvia, connecting it to Salesforce, and executing SQL queries to fetch data, making reporting and analysis easier.

1. The first step is to visit the Skyvia website and sign up for a free account if you haven’t already.
2. After logging in, click +Create NEW, and navigate to the Connections tab to create a new connection. Click here for more details.

3. Select Salesforce from the list of available connectors and authorize Skyvia to access the Salesforce account by providing the necessary credentials.

4. Once your Salesforce connection is established, go to the Query tab and click on +Create NEW to create a new query.
5. In the query editor, write SQL statements to interact with Salesforce data.

For example, to retrieve all contacts from the Salesforce database, use:

SELECT * FROM Contact;

Note: Skyvia supports standard SQL syntax, allowing for complex queries involving joins, aggregations, and more.

6. Click the Execute button to run your query. The results will be displayed below the query editor, where you can review and analyze the data.

7. If you need to run this query regularly, save it by clicking the Save button.

Note: Skyvia also offers scheduling options to automate query execution and export results to various formats or destinations. For detailed instructions and visual aids, refer to Skyvia’s official documentation.

Conclusion: When to Use SOQL vs. SQL?

Both query languages are powerful, but they serve different purposes. Select SOQL when working within Salesforce to fetch data from objects like Accounts, Leads, or Opportunities, especially when needing to stay within Salesforce governor limits. It’s ideal for generating reports, and dashboards and automating CRM tasks.

On the other hand, choose SQL when querying external databases, performing complex joins across multiple tables, or handling large-scale aggregations and data analysis.

If a business needs SQL-like functionality within Salesforce, tools like Skyvia allow for bridging the gap. Understanding when to use each language ensures optimized queries, better performance, and smoother data operations.

F.A.Q.