Using SQL to Query Gmail

The Gmail Data Provider makes it easy to search email from a Gmail account. Instead of learning the details of the IMAP search command, you can simply use the simple SQL syntax. However, the full IMAP search specification is supported. You can also search based on multiple criteria at the same time. This article will demonstrate how to use SQL to execute simple and advanced IMAP searches.

Message Fields

For simple searches, such as searching for all emails from a particular person, you can use message fields in the WHERE clause. The following columns can be used in the WHERE clause: To, From, BCC, CC, Subject, MessageBody, Flags, Labels, Size, or Date. Below are some examples of the simple syntax used by the Gmail Data Provider:

To search for all emails from Twitter in the Inbox from a certain date, you can use the query:

SELECT * FROM Inbox
WHERE (FROM='Twitter' AND Date > '11-25-2012')

To find all emails with subject 'CData Data Provider':

SELECT * FROM Inbox
WHERE (Subject = 'CData ADO.NET Data Provider')

To find all sent emails between September 23rd, 2011 and September 23rd, 2012:

SELECT * FROM 'Gmail/Sent Mail' 
WHERE (Date > '9-23-2011' AND Date < '9-23-2012')

To find all the mail messages in the Inbox from either Twitter or anyone with 'Microsoft' in the name:

SELECT * FROM Inbox 
WHERE (From='Twitter' OR From LIKE '%Microsoft%')

To find all unseen emails since October 1st, 2012 and everything from Twitter:

SELECT * FROM Inbox 
WHERE (Flags LIKE '%UNSEEN%' AND Date > '10-1-2012' OR From='Twitter')

Attachments

You can also search by whether the email has an attachment and by the attachment file name.

To find all the mail messages in the Inbox from that have attachments with the full file name including '.java':

SELECT * FROM Inbox 
WHERE (HasAttachments='TRUE' AND Attachments='.java')

Limit and Count

You can also use the LIMIT and COUNT statements in SELECT queries:

To find the first 30 mail messages in the Inbox with Cc of "Microsoft" and Bcc of "Twitter" without any attachments:

SELECT * FROM Inbox 
WHERE (CC='Microsoft' AND BCC ='Twitter' AND HasAttachments='FALSE') 
LIMIT 30

To find the number of messages in the Inbox:

SELECT COUNT(Id) FROM Inbox

Advanced IMAP Searches

The Data Provider enables you to use any of the search terms in the syntax defined by the IMAP Search command. To search directly using the IMAP Search command, use the Search Criteria pseudo column. To directly use IMAP to find all the mail messages in the Inbox that haven't been seen and are flagged as important that were delivered before October 1st, 2012, use:

SELECT * FROM Inbox 
WHERE (SearchCriteria='UNSEEN FLAGGED BEFORE 1-Oct-2012')