SELECT

SELECT is used to retrieve rows from a given list and can be futher specified with view or content type. It is also used to select attachments.

Syntax

SELECT
{select_expression [AS alias_name], ... | * }
FROM list_name [.{view_name | content_type_name | ALL | ATTACHMENTS}] [[AS] alias_name]
[{INNER JOIN | LEFT JOIN} list_name [[AS] alias_name] ON join_condition]
[WHERE where_condition]
[GROUP BY {col_name | expression}]
[HAVING where_condition]
[ORDER BY {col_name | expression} [ASC | DESC], ...]
[LIMIT [row_offset,] row_count];

The SELECT statement is used as follows:

  1. Each select_expression indicates a column that you want to retrieve. There must be at least on expression here and you can use any of the functions that the connector supports. Alternatively, you can specify an asterisk symbol (*) to select all columns that are visible in the view. If view is not specified, the default view of the list is used. The connector does not permit duplicate column names. That is, there can not be more than one select_expression with the same name. Each selected column can be given an alias using the AS keyword followed by the alias name. The alias is displayed as column name in the result table and can used to reference the column in subsequent clauses, e.g. in the WHERE clause.

    Example: to select all columns from the default view of list Employees:

    SELECT * FROM Employees

    Example: to select and reference a column with alias:

    SELECT CONCAT(Lastname, ', ', Firstname) AS Fullname FROM Customers ORDER BY Fullname

    Example: to select the reverse value of the Title column in uppercase and calculate the number of characters:

    SELECT UCASE(REVERSE(Title)), LENGTH(Title) FROM Employees

    Example: to select the maximum value of the ID column:

    SELECT MAX(ID) FROM Employees

  2. The list_name indicates the list from which to retrieve the rows. Optionally, you can also specify the view or content type that defines a subset of rows and columns. That is, if content type is specified only items of that type is retrieved by the query. If view or content type is not specified, the SELECT statement displays all columns from the default list view. Alternatively, the ALL keyword can be specified to widen the column-set to also include hidden list columns, i.e. columns that are not typically displayed in the SharePoint interface. The selected list can be given an alias using the AS keyword followed by the alias name. The alias name can then be used to reference the list in other clauses.

    Example: to select all columns, including those not visible in default view:

    SELECT * FROM Employees.ALL

    Note: The ALL keyword, view or content type is only applicable when no specific columns are specified.

    Example: to retrieve rows of content type Item:

    SELECT * FROM Employees.Item

    Example: to select columns from a list by referencing its alias name:

    SELECT e.ID, e.Title FROM Employees AS e

  3. The JOIN clause is used to select data from multiple lists in a single select based on join conditions. The query compares each row of the left side list with each row of the right side list to find all pairs of rows which satisfy the join condition. When the join condition is satisfied, column values from left side list and right side list are combined into a result row. For INNER JOIN, if the join condition evaluates false, the row is not selected. For LEFT JOIN, if the join condition evaluates false, all selected columns from the left side list are returned but with NULL in all selected columns from the right side list. You can use LEFT JOIN to find rows in a list that have no counterpart in another list. The JOIN clause can be used in combination with the WHERE clause. The WHERE condition is always executed after the JOIN condition. Each joined list can be given an alias using the AS keyword followed by the alias name. Column aliases can also be used in the join condition.

    Note: The connector does not permit duplicate column names. If you join two lists that contain equally named columns, it is recommended to use alias (AS) to name the columns, see last example below. If alias is not used, the connector will postfix the column names with a counter value.

    Example: Some join example:

    > SELECT * FROM table1
    INNER JOIN table2 ON table1.ID = table2.ID;

    > SELECT * FROM table1 AS A
    INNER JOIN table2 AS B ON A.ID = B.ID
    INNER JOIN table3 AS C ON B.ID = C.ID;

    > SELECT * FROM table1
    LEFT JOIN table2 ON table1.ID = table2.ID
    WHERE table2.ID IS NULL;

    > SELECT A.ID AS X, B.ID AS Y FROM table1 AS A
    INNER JOIN table2 AS B ON A.ID = B.ID;

  1. The WHERE clause, if provided, indicates conditions that each row must satisfy to be selected. The where_condition expression evaluates true or false for every row. The command selects all rows if there is no WHERE clause. The WHERE clause is evaluated after all join conditions have been applied. In the where_condition you can use any of the functions and operators that the connector supports except for aggregate functions. See Operators And Functions for detailed examples of WHERE usage and available operators.

    Example: to retrieve rows from list Employees where Role column is Developer or Salesman:

    SELECT * FROM Employees WHERE Role = 'Developer'" OR Role = 'Salesman'

  2. The GROUP BY clause groups the results according to the group by expression and calculates aggregate functions in each group. The group by expression can be a column or a function. The group by is always evaluated after the WHERE clause but before the HAVING clause, thus only the HAVING clause can include aggregate functions. If the statement contains aggregate functions but no GROUP BY clause is specified, all rows are grouped into one single row.

    Example: to calculate salaries for each Role in Employees:

    SELECT Role, AVG(Salary), MAX(Salary), MIN(Salary) FROM Employees GROUP BY Role

    Example: to count number of developers in Employees:

    SELECT COUNT(ID) FROM Employees WHERE Role = 'Developer'

  3. The HAVING clause is similar to the WHERE clause except that it is evaluated on the final results. Therefore the where_condition in this clause can also include aggregate functions. That is very useful in combination with GROUP BY to filter the final results based on aggregated values. See Operators And Functions for more examples.

    Example: to calculate salaries for each Role in Employees having at least 5 employees in that role:

    SELECT Role, AVG(Salary), MAX(Salary), MIN(Salary) FROM Employees
    GROUP BY Role HAVING Count(ID) >= 5

  4. The ORDER BY clause can be provided to sort the returned result-set by the specified column or columns. The default sort order is ascending, specified explicitly using the ASC keyword. To sort in reverse, add the DESC (descending) keyword to the column name in the ORDER BY clause. Multiple sort columns can be specified at which the sequence of the columns determines the mutual order of the rows.

    Example: to retrieve rows from list Employees ordered by the Salary column in descending order:

    SELECT * FROM Employees ORDER BY Salary DESC

  5. The LIMIT clause is used to limit the number of rows returned by the SELECT statement. LIMIT is either followed by a single integer argument that indicates the maximum number of retrieved rows or a combination of two integer arguments where the first integer indicates the start index and the second integer the number of rows from offset. The latter can be used to produce paging behaviour in applications.  If LIMIT clause is not provided, the maximum number of rows returned is constrained by the list default value, which is typically 100 rows. The default value can be overriden through the DefaultLimit option in the connection string.

    Example: to retrieve rows from list Employees and limit the number rows to 1000:

    SELECT * FROM Employees LIMIT 1000

    Example: to retrieve rows from list Employees and limit the number rows to 100 starting at row 500:

    SELECT * FROM Employees LIMIT 500, 100

The clauses used must be given in the exact order shown in the syntax description. The maximum length of identifiers (list names, column names and aliases) is 255 characters. Identifiers that contain special characters or reserved words, must be quoted by enclosing them within back-tick (`) characters. The following characters have special meanings in identifiers, thus requires quotation marks.

  1. White space
  2. Period (.) is the delimiter character used to separate parts of a identifier name.
  3. Back-tick (`) is the identifier quote character. This character must also be escaped within identifiers by doubling up on them as illustrated below.

Example: to select a column containing white space:

SELECT `First Name` FROM Employees

Example: to select a column enclosed within backticks:

SELECT ```foo``` FROM bar

Code examples

Print all customers

using (var connection = new SharePointConnection(connectionString))
{
    connection.Open();
    using (var command = new SharePointCommand("SELECT Title, Country FROM Customers", connection))
    {
        using (var reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                Console.WriteLine(reader["Title"].ToString().PadRight(40) + " : " + reader["Country"].ToString());
            }
        }
    }
}

Code result

Sven Andersson                           : Sweden
John Smith                               : USA
Brett Wayne                              : United Kingdom
Nils Larsson                             : Sweden
Tim Sherwood                             : United Kingdom
Adam Sandler                             : USA
Mary Anderson                            : USA
Brian Aherne                             : United Kingdom
Steve Allen                              : USA
John Boles                               : USA
Anders Englund                           : Sweden

Print all customers from USA and Sweden

using (var connection = new SharePointConnection(connectionString))
{
    connection.Open();
    using (var command = new SharePointCommand("SELECT Title, Country FROM Customers WHERE Country IN ('Sweden', 'USA')", connection))
    {
        using (var reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                Console.WriteLine(reader["Title"].ToString().PadRight(40) + " : " + reader["Country"].ToString());
            }
        }
    }
}

Code result

Sven Andersson                           : Sweden
John Smith                               : USA
Nils Larsson                             : Sweden
Adam Sandler                             : USA
Mary Anderson                            : USA
Steve Allen                              : USA
John Boles                               : USA
Anders Englund                           : Sweden

Print all customers where name contains "anders" ordered

using (var connection = new SharePointConnection(connectionString))
{
    connection.Open();
    using (var command = new SharePointCommand("SELECT Title, Country FROM Customers WHERE Title LIKE '%anders%' ORDER BY Country, Title", connection))
    {
        using (var reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                Console.WriteLine(reader["Title"].ToString().PadRight(40) + " : " + reader["Country"].ToString());
            }
        }
    }
}

Code result

Anders Englund                           : Sweden
Sven Andersson                           : Sweden
Mary Anderson                            : USA

Print all customers where last name starts with "s"

using (var connection = new SharePointConnection(connectionString))
{
    connection.Open();
    using (var command = new SharePointCommand(@"SELECT Title, Country FROM Customers WHERE Title REGEXP '^(?i)([a-z]{1,}\s[s]{1,})'", connection))
    {
        using (var reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                Console.WriteLine(reader["Title"].ToString().PadRight(40) + " : " + reader["Country"].ToString());
            }
        }
    }
}

Code result

John Smith                               : USA
Tim Sherwood                             : United Kingdom
Adam Sandler                             : USA

Print customer log joined

using (var connection = new SharePointConnection(connectionString))
{
    connection.Open();
    using (var command = new SharePointCommand(@"SELECT Customers.Title AS Name, Log.Title AS Event, Log.Created FROM Customers INNER JOIN Log ON Log.Customer = Customers.ID WHERE Customers.ID = 6 ORDER BY Log.Created ASC", connection))
    {
        using (var reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                Console.WriteLine(reader["Name"].ToString().PadRight(30) + " : " + reader["Event"].ToString().PadRight(30) + " : " + reader["Created"].ToString());
            }
        }
    }
}

Code result

Adam Sandler                   : Logged in                      : 2012-04-19 22:05:31
Adam Sandler                   : Edited personal details        : 2012-04-19 22:06:31
Adam Sandler                   : Created new ticket             : 2012-04-19 22:07:30
Adam Sandler                   : Logged out                     : 2012-04-19 22:08:09


Skip Navigation Links.

Visa     MasterCard