Joins
Overview
Joins are essential for creating comprehensive analytics by combining data from different tables. They define how tables are related and how data should be connected when building queries.
Diagram showing different types of joins and their relationships
Use Cases
Joins are used for:
- User Analytics: Combine user data with their orders, sessions, and activities
- Product Analysis: Link products with categories, reviews, and sales data
- Financial Reporting: Connect transactions with accounts, customers, and products
- Performance Monitoring: Join application logs with user data and system metrics
- Marketing Attribution: Link campaigns with conversions and customer journeys
Syntax
Type: SemanticJoin
List of joins to be applied in the query. Defines relationships between tables for data combination.
{
"joins": [
{
"name": "user_orders",
"description": "Join users with their orders",
"join_type": "left",
"left_table": "users",
"right_table": "orders",
"conditions": [
{
"left_table": "users",
"left_column": "id",
"right_table": "orders",
"right_column": "user_id",
"operator": "="
},
{
"left_table": "orders",
"left_column": "status",
"right_table": "order_statuses",
"right_column": "status_code",
"operator": "="
}
],
"alias": "user_orders"
}
]
}
Parameters
name
Type: str
Required: Yes
Unique identifier name for this join. Used to reference the join in queries and debugging.
{
"name": "user_orders"
}
description
Type: Optional[str]
Default: None
Required: No
Human-readable explanation of this join relationship. Helps developers understand the business logic behind the join.
{
"description": "Links users to their order history for customer analytics"
}
join_type
Type: JoinType
Required: Yes
Type of join to perform. Determines how unmatched records are handled.
Available Values:
inner: Only matching records from both tablesleft: All records from left table, matching from rightright: All records from right table, matching from leftfull: All records from both tablescross: Cartesian product of both tables
{
"join_type": "left"
}
left_table
Type: str
Required: Yes
Primary table name for the join. This is the main table that other tables will be joined to.
{
"left_table": "users"
}
right_table
Type: str
Required: Yes
Table name to join with the primary table. This table will be connected to the left table.
{
"right_table": "orders"
}
conditions
Type: List[JoinCondition]
Required: Yes
Array of join conditions that define how tables are related. These are the actual join criteria. Multiple conditions will be combined using AND.
{
"conditions": [
{
"left_table": "users",
"left_column": "id",
"right_table": "orders",
"right_column": "user_id",
"operator": "="
},
{
"left_table": "users",
"left_column": "tenant_id",
"right_table": "orders",
"right_column": "tenant_id",
"operator": "="
}
]
}
alias
Type: Optional[str]
Default: None
Required: No
Optional alias for the joined table. Useful for disambiguating column references.
{
"alias": "user_orders"
}
Conditions
left_table
Type: str
Required: Yes
Source table name for the join condition.
{
"left_table": "users"
}
left_column
Type: str
Required: Yes
Source column name for the join condition.
{
"left_column": "id"
}
right_table
Type: str
Required: Yes
Target table name for the join condition.
{
"right_table": "orders"
}
right_column
Type: str
Required: Yes
Target column name for the join condition.
{
"right_column": "user_id"
}
operator
Type: str
Default: "="
Required: No
Comparison operator for the join condition.
{
"operator": "="
}
Patterns
One-to-Many Relationships
{
"joins": [
{
"name": "user_orders",
"description": "User to orders relationship",
"join_type": "left",
"left_table": "users",
"right_table": "orders",
"conditions": [
{
"left_table": "users",
"left_column": "id",
"right_table": "orders",
"right_column": "user_id",
"operator": "="
}
]
}
]
}
Many-to-Many Relationships
{
"joins": [
{
"name": "product_categories",
"description": "Products to categories through junction table",
"join_type": "inner",
"left_table": "products",
"right_table": "product_categories",
"conditions": [
{
"left_table": "products",
"left_column": "id",
"right_table": "product_categories",
"right_column": "product_id",
"operator": "="
}
]
},
{
"name": "categories",
"description": "Product categories junction to categories",
"join_type": "inner",
"left_table": "product_categories",
"right_table": "categories",
"conditions": [
{
"left_table": "product_categories",
"left_column": "category_id",
"right_table": "categories",
"right_column": "id",
"operator": "="
}
]
}
]
}
Self-Joins
{
"joins": [
{
"name": "manager_employee",
"description": "Employee to manager relationship",
"join_type": "left",
"left_table": "employees",
"right_table": "employees",
"alias": "managers",
"conditions": [
{
"left_table": "employees",
"left_column": "manager_id",
"right_table": "managers",
"right_column": "id",
"operator": "="
}
]
}
]
}
Complex Multi-Table Joins
{
"joins": [
{
"name": "order_details",
"description": "Complete order information",
"join_type": "left",
"left_table": "orders",
"right_table": "order_items",
"conditions": [
{
"left_table": "orders",
"left_column": "id",
"right_table": "order_items",
"right_column": "order_id",
"operator": "="
}
]
},
{
"name": "product_info",
"description": "Product details for order items",
"join_type": "left",
"left_table": "order_items",
"right_table": "products",
"conditions": [
{
"left_table": "order_items",
"left_column": "product_id",
"right_table": "products",
"right_column": "id",
"operator": "="
}
]
}
]
}
Join Types
Inner Join
-- Only records that exist in both tables
SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id
Left Join
-- All users, with their orders if they exist
SELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
Right Join
-- All orders, with user info if it exists
SELECT u.name, o.total
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id
Full Outer Join
-- All records from both tables
SELECT u.name, o.total
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id
Performance
Best Practices for Performance
- Index Foreign Keys: Ensure join columns are properly indexed for optimal performance
- Join Order: The order of joins in your array matters - start with tables that filter down the most rows
- Use Appropriate Operators: Standard equality (
=) operators are typically fastest - Consider Data Volume: Be mindful of the number of rows being joined
Best Practices
- Use Appropriate Join Types: Choose the right join type based on your data requirements
- Index Foreign Keys: Ensure join columns are properly indexed
- Avoid Cartesian Products: Be careful with cross joins
- Use Aliases: Use table aliases for clarity in complex joins
- Test Performance: Monitor query performance with different join strategies
Video Tutorial
Video: Step-by-step guide to setting up joins in the Cortex dashboard
Visual Join Builder
Screenshot of the visual join builder in the Cortex dashboard
Related Topics
- Measures Configuration - Learn about the data you'll be joining
- Dimensions Configuration - Group joined data effectively
- Filters Configuration - Filter joined data
- Measures Configuration - Complete metric setup guide