JOIN

The JOIN clause is used in SELECT statements to join results from multiple lists based on join conditions.

The JOIN 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 the left side list and the right side list are combined into a single result row.

The connector supports two types of joins; INNER JOIN and LEFT JOIN. For INNER JOIN, each and every row in the first list is joined to each and every row in the second list where the join condition is satisfied.  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 OUTER is an optional keyword in LEFT JOIN but does not change its behaviour.

The join_condition used with ON is any conditional expression of the form that can be used in a WHERE clause. Generally, you should use the ON clause for conditions that specify how to join tables and the WHERE clause to restrict which rows you want in the final results.

Syntax

SELECT ...
[INNER JOIN | LEFT [OUTER] JOIN] list_name ON join_condition
[[INNER JOIN | LEFT [OUTER] JOIN] list_name ON join_condition]

The JOIN clause is used as follows:

  1. The JOIN requires using the ON clause and specifying a join_condition. However, you can still produce a Cartesian product between the specified tables, that is, each and every row in the first table is joined to each and every row in the second table, by specifying a condition that always evaluates true.

  2. The join_condition is any conditonal expression that can be used in a WHERE clause. The join condition may use any columns from the left side list and the right side list.

  3. You can specify multiple JOIN clauses in the query. Each JOIN will join the right side list with all lists added on the left side of the JOIN clause.

  4. You can select any columns from all the lists that you join, but each one must be named uniquely. If two lists contained equaly named columns, it is recommended that you use column alias to name these columns. If two columns have the same name, the connector will add a postfix counter value to these columns.

  5. Uniquely named columns can be referenced with column name only, e.g. User. Ambigious columns must be referenced with list prefix, e.g. Log.User.
  6. You can use asterisk symbol (*) to select all columns from all lists that you join.

  7. The WHERE and ORDER BY clause can use any columns from all the lists that you join.

    Example: simplest join between two tables:

    SELECT table1.a, table2.b FROM table1
    INNER JOIN table2 ON table1.a = table2.b

    Example: join between two tables with alias:

    SELECT table1.a AS x, table2.a AS y FROM table1
    INNER JOIN table2 ON x = y

    Example: join between two tables with WHERE and ORDER BY:

    SELECT table1.a AS x, table2.a AS y FROM table1
    INNER JOIN table2 ON x = y
    WHERE table1.b = 'foo' AND table2.b = 'bar'
    ORDER BY x, y

    Example: use the WHERE clause in combination with LEFT JOIN to select non-matching rows:

    SELECT table1.a AS x, table2.a AS y FROM table1
    LEFT JOIN table2 ON x = y
    WHERE y IS NULL

Code examples

Inner join customers with log

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 Customers.ID = Log.Customer", 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
King Carl XVI Gustaf           : Logged in                      : 2012-04-19 22:32:22
King Carl XVI Gustaf           : Edited 'Phone number'          : 2012-04-19 22:36:44

Left join customers with log

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 LEFT JOIN Log ON Customers.ID = Log.Customer", 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

Sven Andersson                 :                                :
John Smith                     :                                :
Brett Wayne                    :                                :
Nils Larsson                   :                                :
Tim Sherwood                   :                                :
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
Mary Anderson                  :                                :
Brian Aherne                   :                                :
Steve Allen                    :                                :
John Boles                     :                                :
Anders Englund                 :                                :
King Carl XVI Gustaf           : Logged in                      : 2012-04-19 22:32:22
King Carl XVI Gustaf           : Edited 'Phone number'          : 2012-04-19 22:36:44
Jerry Buss                     :                                :

Left join customers with log where no log exists

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 LEFT JOIN Log ON Customers.ID = Log.Customer WHERE Log.ID IS NULL", 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

Sven Andersson                 :                                :
John Smith                     :                                :
Brett Wayne                    :                                :
Nils Larsson                   :                                :
Tim Sherwood                   :                                :
Mary Anderson                  :                                :
Brian Aherne                   :                                :
Steve Allen                    :                                :
John Boles                     :                                :
Anders Englund                 :                                :
Jerry Buss                     :                                :

Left join customers with log where customer last name starts with "s"

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 LEFT JOIN Log ON Customers.ID = Log.Customer WHERE Name REGEXP '^(?i)[a-z]{1,}\ss'", 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

John Smith                     :                                :
Tim Sherwood                   :                                :
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