Beginner SQL

As a way of introducing beginning SQL concepts, this article focuses on the SQL produced by the Advanced Search Tool. Review these concepts before tackling intermediate and advanced sections.

The basic structure of a SQL query has three clauses: a SELECT clause, a FROM clause, and a WHERE clause. See the following example:

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

You can also include an ORDER BY clause, which is an optional clause that 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:

The Advanced Search Tool opened to the SQL Layout tab. SELECT fields and FROM tables are in the Field List dialog box, WHERE filters is in the Filter Clause dialog box, and ORDER BY sort fields is in the Sort Clause dialog box

Contacts with a Date of Birth

In this example, you want to search for Contacts that have a Date of Birth listed in their Contact record. You make the following selections:

The Contact ID field column name with no comparison or search term, the Display Name column name with no comparison or search term, and the Date of Birth column name with the NOT comparison and NULL search term

Based on these selections, you pull the Contact ID, Display Name, and Date of Birth for all Contact records that have a date entered in the Date of Birth field. 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

Based on this query, you pull these results:

A list displaying five Contacts. Their Contact ID, Display Name, and Dates of Birth are listed in their own separate columns

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 table name followed by a dot (.) precedes a field name, the statement is more precise. In the example, "Contacts" followed by the dot means the fields refer to the Contacts table in the FROM clause. If there is only one table in the FROM clause, you don't need to include it. You can also omit it for any fields which are not ambiguous (where you select from two tables from and the field could be from either table).

Brackets

Contacts.[Contact_ID] AS [Contact ID]

Normally, brackets are 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) because spaces are poor database practice.

The Advanced Search Tool always adds brackets so it doesn't have to guess when they are required. This is a common practice when writing SQL.

Field Aliases

SELECT Contacts.[Contact_ID] AS [Contact ID]

Use an AS clause to give a field in a query a different name in your results. The Advanced Search Tool uses this technique to display fields with spaces. The brackets are required around the alias because of space. If you omit the alias, the field displays as Contact_ID (with the underscore) in your results.

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

The Display Name column name

SELECT Contacts.[Display_Name] AS [Display Name]

Change the Column Name for a new alias in the SQL:

The Display Name column name renamed to First Name

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 it easier to edit the SQL without causing errors from missing or extra commas. Because the comma and field are on the same line, you may move or delete the whole line without causing 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 to create 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. You can't compare NULL to any value but it always uses this special syntax for comparison. For further discussion of this, see Intermediate SQL.

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.

The Search Term drop-down list is open, with a red box around the single value comparisons: =, >=, <=, >, and <

Think of these as mathematical comparisons, even for text. You can compare any piece of text to another using these comparisons because 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 & Wildcard Comparisons

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

The Search Term drop-down list is open, with a red box around the text and wildcard comparisons: =, LIKE, CONTAINS, STARTS WITH, and ENDS WITH

= (EXACT MATCH)

Use the equal sign (=) to search for an 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.

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'

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%'

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 New/Copy View dialog box. The Date of Birth field has the BETWEEN search term selected and 01/01/1994 and 01/01/1995 selected

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 requires a list of values separate by commas. Each value in the list must be an exact match.

The New/Copy View dialog box. The Age field has the IN search term selected and 18, 19, 20 listed

The resulting SQL looks like this:

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

Search Terms

Formatting standards for text and dates in SQL in MinistryPlatform.

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

Learn how to add fields from different tables or records using the Advanced Search Tool, which employs the Table Lookup Convention for database searches.

With the Advanced Search Tool, you can add 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 is the JOIN clause in SQL.

What would you like to know more about?