Beginning SQL

As a way of introducing beginning SQL concepts, this article focuses on the SQL produced by the Advanced Search Tool. The concepts presented here should be reviewed before tackling Intermediate and Advanced sections.

The basic structure of a SQL Query has the following three parts.

SELECT <fields>     
FROM <tables>            
WHERE <criteria>

There is another optional clause which is only necessary in specific circumstances (see Intermediate SQL).

ORDER BY <sort fields>

The Advanced Search (and therefore Views) write SQL queries under the hood to return records:

Example 1: Contacts Having a Date of Birth

The equivalent SQL query (using the simplest syntax possible):

SELECT Contact_ID, Display_Name, Date_of_Birth
FROM Contacts
WHERE Date_of_Birth IS NOT NULL

Verbose Syntax

You may notice that the syntax produced by the Advanced Search tool is more complex than the example above.

Here is the same query produced by the Advanced Search:

SELECT Contacts.[Contact_ID] AS [Contact ID]    
, Contacts.[Display_Name] AS [Display Name]    
, Contacts.[Date_of_Birth] AS [Date of Birth]
FROM Contacts
WHERE Contacts.[Date_of_Birth] IS NOT NULL

The Advanced Search Tool generates more verbose syntax because it's less error-prone when parsed by the SQL Server. Below are explanations of these extra features.

Fully Qualified Fields

Contacts.[Contact_ID]

When a field name is introduced by the table name followed by a dot (.), the statement is more precise. In the example, "Contacts" followed by the dot means the fields are referring to the Contacts table in the FROM clause. If there is only one table in the FROM clause, this can be omitted. It can also be omitted for any fields which are not ambiguous (where two tables are selected from and the field could be from either table).

Brackets

Contacts.[Contact_ID] AS [Contact ID]

Brackets are normally optional around field names. However, they are required if the field name has space or is the same as a reserved keyword (such as "Name").

In MinistryPlatform, no fields have spaces in the database (they have underscores where spaces might be). Using spaces in field names is poor database practice.

Brackets are always added by the Tool rather than trying to predict when they are required. This is a common practice when writing SQL.

Field Aliases

SELECT Contacts.[Contact_ID] AS [Contact ID]

A field in a query may be given a different name in the output using the AS clause. The Advanced Search Tool uses this technique to display fields with spaces. An example is: Contact_ID AS [Contact ID]. The brackets are required around the alias because of space. If the alias were omitted, the field would be presented as Contact_ID in the output (with the underscore).

In the Advanced Search Tool, the Column Name text box controls the field alias:

SELECT Contacts.[Display_Name] AS [Display Name]

Changing the Column Name results in a new alias in the SQL:

SELECT Contacts.[Display_Name] AS [First Name]

Comma Placement

SELECT Contacts.[Contact_ID] AS [Contact ID]       
, Contacts.[Display_Name] AS [Display Name]       
, Contacts.[Date_of_Birth] AS [Date of Birth]    
FROM Contacts    
WHERE Contacts.[Date_of_Birth] IS NOT NULL

While this is not considered verbose syntax, it does seem odd because it's different than normal punctuation. However, placing the commas at the beginning of each field makes editing the SQL easier without introducing errors due to omitted or extra commas. Because the comma and field are on the same line, the whole line may be deleted or moved without introducing an error, with the exception of the first line (which has no comma before it).

Filters: Comparisons & Search Terms

The Advanced Search Tool provides options for creating many types of filters. Each filter is a Comparison which further limits the number of resulting records. The tool includes the "WHERE" keyword behind the scenes and displays the <criteria> in the SQL Layout.

Comparisons Without Search Terms

Some comparisons check for values or missing values using the NULL keyword. NULL represents a missing value and NOT NULL represents an existing value. NULL cannot be compared to any value but always uses this special syntax for comparison. For further discussion of this, see Intermediate SQL - The Special Properties of NULL

The following query returns records with birth dates:

SELECT Contact_ID, Display_Name FROM Contacts WHERE Date_of_Birth IS NOT NULL

The following query returns records without birth dates:

SELECT Contact_ID, Display_Name FROM Contacts WHERE Date_of_Birth IS NULL

Single Value Comparisons

The majority of filter types are single value comparisons. Each of these require a single Search Term.

These can be thought of as mathematical comparisons, even for text. Any piece of text may be compared to another using these comparisons because the text is treated like a string of numbers in which A < B < C and so on.

SELECT Contact_ID, Display_Name 
FROM Contacts 
WHERE Contact_Status_ID = 1

Text and Wildcard Comparisons

Text comparisons support exact matches or wildcards so you can match when text is "like" a search term:

Exact Match

Exact Match uses an equals sign like number and date comparisons.

SELECT Contact_ID, Display_Name 
FROM Contacts 
WHERE Last_Name = 'Smith'

Other text comparisons use the LIKE keyword with the % wildcard.

Contains

Contains uses the % wildcard at the beginning and end.

SELECT Contact_ID, Display_Name 
FROM Contacts 
WHERE Last_Name LIKE '%Smith%'

Starts With

Starts With uses the % wildcard at the end.

SELECT Contact_ID, Display_Name FROM Contacts WHERE Last_Name LIKE 'Smith%'

Like

Like uses the LIKE keyword but does not insert the % wildcard, allowing custom patterns.

SELECT Contact_ID, Display_Name 
FROM Contacts 
WHERE Email_Address LIKE '%@%.com'

Ends With

Ends With uses the % wildcard at the beginning.

SELECT Contact_ID, Display_Name 
FROM Contacts 
WHERE Last_Name LIKE '%Smith'

Multiple Value Comparisons

Some comparisons are more complex and require multiple values (Search Terms).

BETWEEN

Between requires two values and matches anything between and including the two values.

The resulting SQL looks like this:

SELECT Contact_ID, Display_Name, Date_of_Birth  FROM Contacts  WHERE Date_of_Birth BETWEEN '1994-01-01' AND '1995-01-01'

In

IN requires a list of values separate by commas. Each value in the list must be an exact match.