SOQL vs SQL

Though both languages (SOQL - Salesforce Object Query Language/SQL - Structured query language) have many syntactical similarities but when I started working on writing queries in salesforce, I always find it a little difficult as I came from SQL background and was always following the SQL way. It took me sometime to get adjusted to SOQL, so I felt it's important to write about few main differences between SOQL and SQL.

SQL is ANSI?standard query language where as SOQL?is specially optimized version of SQL. It is specially designed for working with Salesforce.com underlying database.

SOQL is the language used to query data from your Salesforce org. It is exclusive to query only and only from the salesforce org. Not for updating or inserting the data. For updating and inserting the data, we have separate salesforce language - DML. While in SQL, we have the same language to query, update, insert etc.

1. Salesforce Object vs SQL Tables: To store the data in SQL, we have to create a SQL table, which stores data in the form of rows and columns. While the purpose of salesforce objects to store the data is almost same as SQL tables, but with many additional properties to simplify our development.

Salesforce provides many out-of-the box objects(standard) and of course developers can create your own objects(custom) too. SF Objects are not only tables to store data but its more than tables. When you create a SF object, page layout, search indexes are automatically generated for you. Many bindings between your UI and objects are automatically generated which is not the case with SQL.

In SQL we need to create tables for everything from scratch and also maintain normalization of database, while SF provides multiple out-of-the box standard objects with rich set of data types and relationship which eliminates the need of many reference tables. These features provides SF dev to create complex relationships with relatively lesser number of objects.

2. Governor limits/No "SELECT * from" clause- In SQL, we can use "select * from" to query all fields and if we run the query without any where clause, we can get all rows from the underlying SQL table.

Since Salesforce is a multitenant platform, so SF need to follow certain guidelines while writing SOQL queries to fetch data. These guidelines are called as Governor's limits and these are mainly to apply restrictions so that their query will not become bottleneck for the whole platform by executing endless loops or endless running queries to fetch data which is outside the limit.

As a general principle whether it's SQL or SOQL , our query should always be selective (select required fields) and also restrictive (apply where clause/Limit) rather than select everything. SQL advices developers to follow this principle as a standard and a good practice while SOQL imposes these as a rule in the form of Governor Limit.

Although it is not considered as a good practice but if you want to pull all fields from salesforce object, you can run query below. Remember it can only run for a max limit of 200 rows, if you provide limit above 200, query will not compile.

Select fields(All) from Account limit 200

Select fields(standard) from Account limit 200

Select fields (custom) from Account limit 200

3. Fetch the data from related objects:-

SQL is used for getting data from one or more tables. They can be either related or not. SOQL is used for getting the data of a particular object and ONLY from its related objects. You cannot pull data of two unrelated objects through SOQL.

In SQL, we have multiple types of joins to fetch the data while in SOQL this can be done in two possible ways -

a. Parent to child -

We can retrieve a list of child records as part of query result. The object used in from clause is the parent and we would like to fetch child object data as well. In order to do that we need to know the child relationship name.

In below example relationship name is contacts.

Example -

SELECT Name, (select ID, FirstName, LastName from contacts)

FROM Account

Please note- We need to know and verify the relationship name. In case of custom object , relationship is appended with __r.

b. Child to parent -

The object used in from clause is child object and we need to use dot(.) notation to fetch data from parent object.

Since while referring parent from child , we can only have a single record, it can be retrieved either by relationship (custom object) followed by dot notation and then field name or simply by parent object name(standard object) dot notation and then followed by field name. See examples below -

• Standard relationship -

[SELECT Id, FirstName, LastName, Account.Name,Account.ID from Contact]

In the above query we are extracting Account's Name and ID along with few contact's field.

• Custom relationship -

[SELECT Name,Opportunity__r.Name,Opportunity__r.Amount from Invoice__c]

You can notice the difference in the query in case of custom relationship. Relationship name is being used instead of object name. Rest all is almost same.

If you have any other difference between SQL and SOQL which you would like to highlight, please comment.

Thank you.