Salesforce Object Query Language(SOQL) is used to search your organization’s data for specific information.
SOQL is similar to the SELECT statement in the widely used Structured Query Language (SQL) but is specifically designed for Salesforce.

SOQL can be used with Apex, Visualforce and Schema explorer of Force.com IDE.

It will help you in retrieving and manipulating data, In this you can’t specify * as for all the fields that are used for other SQL languages.

Hence you must specify an exact field you want to get the information about.

While writing query one have to combine SELECT command with a list of fields to retrieve as well as the conditions for selecting rows.
SOQL uses the SELECT statement combined with filtering statements to return sets of data, which can optionally be ordered:

SELECT fieldList FROM object [WHERE conditionExpression] [ORDER BY fieldName ASC | DESC ? NULLS FIRST | LAST ? ] LIMIT number_of_rows
The query can be broken into following logical units:
1. Select Clause
2. From Clause
3. Where Clause
4. Order by Clause
5. Limit Clause

Condition Expression Syntax (WHERE Clause)
So, if you we want to get all the Leads from your Salesforce.com account where the email address equals = “naresh@anycompany.com” you would use the following SOQL statement:
Select ID, Name from Lead WHERE email = ‘naresh@anycompany.com’

If you want get all contact where name start with ‘N’ and state is’California’
Select Id FROM Contact WHERE Name LIKE ‘N%’ AND MailingState =’California’

SOQL – COUNT()
Getting the “Count” from results returned in a SOQL data set is very simple. For example, if I wanted to know how many Leads were going to be returned in my SELECT statement above, I can use the COUNT() function below:
SELECT COUNT() from Lead WHERE email = ‘naresh@anycompany.com’

SOQL – Like Operator

The LIKE operator provides a way to match full or partial text strings and includes support for wildcards. Let’s say in some case we want to find all the Leads where the email domain is the same. Here, we can use a “LIKE” operator.

For Example

SELECT Id, Name from Lead WHERE email LIKE ‘%anycompany.com’

Here we wants all Leads where email ends with “anycompany.com”. So we place the ‘%’ sign at the beginning of whatever we are looking for.” Any record where email end with “anycompany.com” returned.

SELECT Id, Name from Lead WHERE email LIKE ‘%anycompany%’

The above query return all the leads where the email contains “anycompany” it may by anycompany.com or anycompany.co.in.

You can use other wildcard character which is the underscore “_”. Its used to match exactly one character before or after as you want.

SOQL Comparison Operators

Operator	Common name
=	Equals
!=	Not equals
<	Less than
<=	Less than or equal
>	Greater than
>=	Greater than or equal
IN	In
NOT IN	Not in (WHERE clause)
INCLUDES EXCLUDES	Applies to multi-select picklists
LIKE	Like (see section below)

Using null in SOQL Queries
You can search for null values by using the null keywords. Use null to represent null values in SOQL queries.
SELECT name FROM account WHERE phone != null

OFFSET
When expecting many records in a query’s results, you can display the results in multiple pages by using the OFFSET clause on a SOQL query. For example, you can use OFFSET to display records 51–75 and then jump to displaying records 301–350. Using OFFSET is an efficient way to handle large results sets.
You could use OFFSET 10 in your query to skip the first 10 rows:
Select name from Account order by name Limit 100 OFFSET 10

Considerations When Using OFFSET
The maximum offset is 2,000 rows. Requesting an offset greater than 2,000 results in a NUMBER_OUTSIDE_VALID_RANGEerror.
OFFSET is intended to be used in a top-level query, and is not allowed in most subqueries, so the following query is invalid and returns a MALFORMED_QUERY error:
SELECT Name, Id FROM Merchandise__c WHERE Id IN (SELECT Id FROM Discontinued_Merchandise__c LIMIT 100 OFFSET 20)ORDER BY Name

A subquery can use OFFSET only if the parent query has a LIMIT 1 clause. The following query is a valid use of OFFSET in a subquery:
SELECT Name, Id (SELECT Name FROM Opportunity LIMIT 10 OFFSET 2)
FROM Account ORDER BY Name LIMIT 1

OFFSET cannot be used as a subquery in the WHERE clause, even if the parent query uses LIMIT 1.
NOTE : Use ORDER BY clause when you use OFFSET to ensure that the result set ordering is consistent.

Querying Multi-Select Picklists
You can search for individual values in multi-select picklists, which are regularly used in client applications.
Examples
The following query filters mypicklist__c field that are equal to Class-A and Class-B selected (exact match):
SELECT Id, name FROM CustObj__c WHERE mypicklist__c = ‘Class-A;Class-B’
That match either of these values ‘Class-A;Class-B’ or ‘Class-C’
SELECT Id, name from CustObj__c WHERE mypicklist__c includes (‘Class-A;Class-B’,’Class-C’)

Location-Based SOQL Queries
Using Location-based SOQL queries you can calculate the distance between two location values, such as between a warehouse and a store.
Or you can calculate the distance between a location value and fixed latitude-longitude coordinates. The geolocation custom field type allows you to create a field to store location values.
SELECT Clause
Retrieve records with locations saved in geolocation or address fields as individual latitude and longitude values by appending “__latitude__s” or “__longitude__s” to the field name, instead of the usual “__c”. For example:
SELECT Name, Location__latitude__s, Location__longitude__s FROM Warehouse__c

WHERE Clause
Compare two field values, or a field value with a fixed location. For example:
SELECT Name, Location__c
FROM Warehouse__c
WHERE DISTANCE(Location__c, GEOLOCATION(37.775,-122.418), ‘mi’) < 20

ORDER BY Clause
Sort records by distance using a distance condition in the ORDER BY clause. For example:
SELECT Name, StreetAddress__c
FROM Warehouse__c
WHERE DISTANCE(Location__c, GEOLOCATION(37.775,-122.418), ‘mi’) < 20
ORDER BY DISTANCE(Location__c, GEOLOCATION(37.775,-122.418), ‘mi’)
LIMIT 10

Location-Based SOQL Query Considerations
Location-based queries are supported in SOQL in Apex and in the SOAP and REST APIs. Keep in mind these considerations.

• DISTANCE and GEOLOCATION are supported in WHERE and ORDER BY clauses in SOQL, but not in GROUP BY. DISTANCE is supported in SELECT clauses.
• DISTANCE supports only the logical operators > and <, returning values within (<) or beyond (>) a specified radius.
• When using the GEOLOCATION function in SOQL queries, the geolocation field must precede the latitude and longitude coordinates. For example, DISTANCE(warehouse_location__c, GEOLOCATION(37.775,-122.418), ‘km’) works but DISTANCE(GEOLOCATION(37.775,-122.418), warehouse_location__c, ‘km’) doesn’t work.
• Apex bind variables aren’t supported for the units parameter in DISTANCE or GEOLOCATION functions. This query doesn’t work.

String units = ‘mi’;
List<Account> accountList =
[SELECT ID, Name, BillingLatitude, BillingLongitude
FROM Account
WHERE DISTANCE(My_Location_Field__c, GEOLOCATION(10,10), :units) < 10];

Salesforce Apex: SOQL limitations

1. By default, 20,000 characters for a Statement.
2. The number of SOQL rows – 50k.
3. The number of SOSL Queries – 20.
4. The number of DML Statements – 150.
5. The number of DML Rows – 10k.
6. The number of Script Statements – 200k.
7. Maximum Heap size – 3000k.
8. The number of callout – 10.
9. The number of Email invocation – 10.
10. Number of Filed describes – 100.
11. The number of Child relationships – 100.
12. The number of picklists describes – 100.
13. The number of future calls – 10.