Fluent SQL

Table Joins

The SQL FROM clause can take many different kinds of table source as its argument. So far, we’ve seen SELECT statements that use literal table names and Table objects with qualified names. (See Specifying Table Names.)

To select data from more than one table, Fluent SQL supports joins.

Use a join function (InnerJoin(), LeftJoin(), RightJoin(), or FullJoin()) to join one table with another. The join expression has an On() method that takes a predicate specifying the join condition. In the select list, use table objects and their indexers to disambiguate common column names like [ID] and [Name].

var customer = Table.Create("dbo", "Customer").As("cust");
var country = Table.Create("dbo", "Country").As("ctry");

var selectStatement = Select
    .All(customer["ID"], customer["Name"], customer["Country"], country["Name"])
    .From(
        customer.InnerJoin(country)
            .On(customer["Country"].IsEqualTo(country["Alpha2Code"]))
    );

SELECT
        [cust].[ID],
        [cust].[Name],
        [cust].[Country],
        [ctry].[Name]
FROM [dbo].[Customer] AS [cust]
        INNER JOIN [dbo].[Country] AS [ctry]
                ON [cust].[Country] = [ctry].[Alpha2Code];