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 (the bold sections are those visible in the tool):

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. This may be used for date ranges, alphabetical ranges, or number ranges.

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.

The resulting SQL looks like this:

 SELECT Contact_ID, Display_Name
 FROM Contacts
 WHERE __Age IN (18, 19, 20)

Search Terms

Text

Text is always placed in single quotes.

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

Dates

Dates are always placed in single-quotes formatted in the following fashion:

Date only : 'yyyy-mm-dd'
Date and time: 'yyyy-mm-dd hh:mm:ss'

 SELECT Contact_ID, Display_Name, Date_of_Birth
 FROM Contacts
 WHERE Date_of_Birth = '1994-01-01'

For more on Comparisons and Values, see SQL Comparisons.

Fields From Multiple Tables or Records

The Advanced Search Tool supports adding fields from multiple tables. The resulting code is specific to MinistryPlatform, so it is not covered in this article. The tool uses the Table Lookup Convention which in SQL is the JOIN clause.