JOIN

A joined table is a result set that is the product of two or more tables. AnalytiX-BI supports INNER, LEFT OUTER and RIGHT OUTER joins.

The following query retrieves the quantity of products for each order using an inner join.

SELECTOrderID,ProductName,Quantity

FROMProductsASpINNER JOINOrderDetailsASod

ONp.ProductID = od.ProductID

Note: selected columns do not have to be qualified when their name is unique among the joined tables.

The following example shows a query that uses a LEFT join to display the list of customers without any associated orders.

SELECTContactName,OrderID

FROMCustomersAScLEFT JOINOrdersASo

ONc.CustomerID = o.CustomerID

WHEREo.OrderIDIS NULL

ORDER BYc.ContactName

Similarly, the following query uses a RIGHT join to display all order numbers that do not have a customer associated with them.

SELECTContactName,OrderID

FROMCustomersAScRIGHT JOINOrdersASo

ONc.CustomerID = o.CustomerID

WHEREc.ContactNameIS NULL

ORDER BYo.OrderID

If relationships between tables are established in the data model, the JOIN keyword can be omitted. However, all columns in the query must be fully qualified. AnalytiX-BI will determine the join path among all the tables that are referenced in the query automatically.

The following query calculates the total for each order without having to specify explicitly the FROM or JOIN keywords for the Orders and OrderDetails tables.

SELECTOrders.CustomerID,Orders.OrderDate,SUM(OrderDetails.UnitPrice * OrderDetails.Quantity *(1 - OrderDetails.Discount)) ASOrderTotal

ORDER BYOrders.OrderDate

AnalytiX-BI automatically determines the join path between the Orders and OrderDetails table based on the relationships configured in the model.

Note: If the FROM and JOIN keywords are omitted, AnalytiX-BI automatically performs INNER joins.

Note: Although this syntax is supported for backwards compatibility, it is recommended to always specify the tables involved in the query using the FROM keyword and explicitly define the desired joins using the JOIN keyword.