Joins and Relationships

Execute complex queries across multiple tables using various JOIN operations.

JOIN Types

Infino supports various JOIN types for multi-table queries:

JOIN Type	Description
INNER JOIN	Returns rows with matches in both tables
LEFT JOIN	Returns all rows from left table, matched rows from right
RIGHT JOIN	Returns all rows from right table, matched rows from left
FULL JOIN	Returns all rows from both tables
CROSS JOIN	Cartesian product of both tables

INNER JOIN

-- Basic inner join
SELECT l.timestamp, l.message, u.username
FROM logs l
INNER JOIN users u ON l.user_id = u.id
WHERE l.level = 'error';

-- Join with additional filtering
SELECT l.service, l.level, u.department
FROM logs l
INNER JOIN users u ON l.user_id = u.id
WHERE l.timestamp >= '2024-01-01'
  AND u.department = 'Engineering';

LEFT JOIN

-- Left join to include all users, even without logs
SELECT u.username, COUNT(l.id) as log_count
FROM users u
LEFT JOIN logs l ON u.id = l.user_id
GROUP BY u.username;

-- Left join with filtering
SELECT u.username, l.level, l.message
FROM users u
LEFT JOIN logs l ON u.id = l.user_id
WHERE u.active = true
ORDER BY u.username, l.timestamp DESC;

RIGHT JOIN

-- Right join to ensure all logs are included
SELECT l.timestamp, l.message, u.username
FROM users u
RIGHT JOIN logs l ON u.id = l.user_id
WHERE l.level = 'error';

FULL JOIN

-- Full outer join to see all users and all logs
SELECT u.username, l.level, l.timestamp
FROM users u
FULL JOIN logs l ON u.id = l.user_id
ORDER BY u.username, l.timestamp;

CROSS JOIN

-- Cross join for all combinations (use with caution)
SELECT s.name as service_name, e.environment
FROM services s
CROSS JOIN environments e;

Complex Join Examples

Multi-Table Analysis

-- Join logs with users and departments
SELECT 
  d.name as department,
  u.username,
  COUNT(l.id) as error_count
FROM departments d
INNER JOIN users u ON d.id = u.department_id
LEFT JOIN logs l ON u.id = l.user_id AND l.level = 'error'
GROUP BY d.name, u.username
ORDER BY error_count DESC;

Time-Based Joins

-- Join current logs with historical user data
SELECT 
  l.timestamp,
  l.service,
  l.message,
  u.username,
  u.last_login
FROM logs l
INNER JOIN users u ON l.user_id = u.id
WHERE l.timestamp >= '2024-01-01'
  AND u.last_login >= '2023-12-01'
ORDER BY l.timestamp DESC;

Aggregated Joins

-- Service performance with user engagement
SELECT 
  s.name as service_name,
  COUNT(DISTINCT l.user_id) as active_users,
  COUNT(l.id) as total_requests,
  AVG(l.response_time) as avg_response_time
FROM services s
LEFT JOIN logs l ON s.name = l.service
WHERE l.timestamp >= '2024-01-01'
GROUP BY s.name
HAVING COUNT(l.id) > 100
ORDER BY avg_response_time DESC;

Best Practices

Performance Optimization

1. Use appropriate JOIN types based on your data requirements:

   -- Good: Use INNER JOIN when you only need matching records
   SELECT l.message, u.username
   FROM logs l
   INNER JOIN users u ON l.user_id = u.id;
   
   -- Consider: Use LEFT JOIN when you need all records from one side
   SELECT u.username, COUNT(l.id) as log_count
   FROM users u
   LEFT JOIN logs l ON u.id = l.user_id
   GROUP BY u.username;

2. Filter early to reduce JOIN overhead:

   -- Good: Filter before joining
   SELECT l.message, u.username
   FROM logs l
   INNER JOIN users u ON l.user_id = u.id
   WHERE l.timestamp >= '2024-01-01'  -- Filter applied early
     AND l.level = 'error';

3. Use table aliases for readability:

   -- Good: Clear aliases
   SELECT l.timestamp, u.username, d.name
   FROM logs l
   INNER JOIN users u ON l.user_id = u.id
   INNER JOIN departments d ON u.department_id = d.id;

Common Patterns

User Activity Analysis

-- Users with their most recent activity
SELECT 
  u.username,
  MAX(l.timestamp) as last_activity,
  COUNT(l.id) as total_logs
FROM users u
LEFT JOIN logs l ON u.id = l.user_id
GROUP BY u.username
ORDER BY last_activity DESC;

Service Dependency Mapping

-- Services and their dependent services
SELECT 
  s1.name as service,
  s2.name as dependent_service,
  COUNT(l.id) as interaction_count
FROM services s1
INNER JOIN logs l ON s1.name = l.source_service
INNER JOIN services s2 ON s2.name = l.target_service
GROUP BY s1.name, s2.name
ORDER BY interaction_count DESC;