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.
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)