A Complete Guide To SQL Joins For Data Bridges

We often find ourselves working with isolated islands of information - a manually maintained Excel workbook here, a cloud-based warehouse there, scattered data points everywhere. Even in its most basic form, there are often separate tables that are related, whether they inform one, the other, or both. 

Like island chains in a vast digital ocean, these islands hold valuable insights, but their isolation limits their potential. To unlock the full value of our data, we need to build bridges between these islands. These bridges are known as joins in Structured Query Language (SQL).

These powerful connectors give us the ability to: 

• Transform fragmented data into cohesive insights 
• Answer complex questions by connecting multiple data points 
• Unlock deeper analytical possibilities 
• Build efficient systems that maximize both performance and clarity

Whether you're exploring data in Sigma using SQL or working with any other database system, mastering joins is crucial for elevating your data analysis capabilities. In this guide, we’ll walk you through how to level up your SQL game by covering the basics about joins in SQL, leveraging joins for deeper analysis, the keys and relationships that make joins, and some best practices when using joins in your analyses.

Scratching the surface, the official definition of a join is a clause used to combine rows from two or more tables based on a related column between them. Combining the rows from multiple tables allows us to piece together different worlds of our data and tell a fleshed-out story. However, before we talk about the different types of joins, we need to learn about the foundation on which joins are made.

The foundation: Keys as bridge anchors

Two foundational qualities in data allow for joins to occur, namely keys and relationships between tables. Let’s go through these concepts individually and then tie them together.

We need strong foundations on both shores before we can build bridges between our data islands. In database terms, these foundations are called keys. These keys are leveraged to define the relationships between each table. Keys come in two forms:

Primary keys

These are unique identifiers, like a building's foundation - solid, unmovable, and distinct. Just as every bridge needs a secure anchor point, every table needs a primary key that uniquely identifies each record. These keys cannot contain NULL values and must be unique - much like how every Social Security Number or ISBN must be one-of-a-kind.

Foreign keys

These connectors reach out to other tables, like a bridge's spanning cables connecting to the opposite shore. They create relationships between tables by referring to primary keys in different tables, maintaining the structural integrity of our data connections.

For example, if there were a table named “Book” that contained the data of each book in a given library, with its primary key as each book’s ISBN, and another table called “Customer” that recorded each customer’s information, including the current books they have currently checked out, to connect these two tables and get deeper insights into each customer, ISBN would need to be a foreign key in the “Customer” table to join the data from those tables together and begin to transform data into information by adding additional context.

Just as relationships serve an essential purpose in connecting humans, they serve an important purpose in connecting tables of data. Relationships inform you of how the different joins will treat our data upon computation. The appearance of primary and foreign keys in other tables dictates the different relationships. 

‍

There are three types of relationships: one-to-one (denoted as 1:1), one-to-many (denoted as 1:N), and many-to-many (denoted as M:N), each describing how the keys of tables connect based on their records. Let's unpack each of these now, remembering that records are related by keys.

Developing relationships between data islands

Just as bridges can connect different types of terrain in various ways, database relationships come in three fundamental patterns. Each describes a different way that tables can connect through their keys:

One-to-one relationship

In this relationship, each record in Table A relates to exactly one record in Table B, and vice versa. Each unique key corresponds to only one other unique key.

Real-world examples:

• Car and Vehicle Identification Number (VIN): Every car has a unique VIN, and each VIN corresponds to precisely one vehicle
• Country and Capital City: One country has one capital city, and one capital city belongs to one country

One-to-many relationship

Each record in Table A can relate to multiple records in Table B, but each record in Table B relates to only one record in Table A. A single unique key from one table can correspond to many unique keys in another table.

Real-world examples:

• Department and employees: One department has many employees, but each employee belongs to only one department
• Customer and orders: One customer can place many orders, but each order belongs to only one customer

Many-to-many relationship

In this relationship, records in Table A can relate to multiple records in Table B and vice versa. This type requires a junction (also called a bridge or associative) table to implement. Many unique keys can correspond to many unique keys on both sides.

Real-world examples:

• Students and classes: Students can take multiple classes, and classes can have multiple students
• Actors and movies: Actors can appear in multiple movies, and movies can feature multiple actors

Planning your bridges: Understanding SQL join types

With keys and relationships down, you are now ready to explore the exciting world of SQL joins. We will discuss the common and uncommon types of joins used and know when to use which type of join is best for your intended result. 

Defining the types of SQL joins

SQL offers four commonly used join types and two specialized joins for specific scenarios. Each type serves a unique purpose in connecting our data bridges.

Common SQL join types

• Inner join: This is a strict bridge that only connects matching records from both tables. Like a party that only admits couples, this join returns records with matching values in both tables.
• Left join: This bridge maintains all records from the left table while connecting to matching records from the right table. When no match exists, the right side shows NULL values. Imagine a party where everyone from the first group attends, and they can bring their partner if they have one.
• Right join: Similar to a left join but reversed, this bridge keeps all records from the right table and connects to matching records from the left. It's like ensuring everyone from the second group attends the party, with or without their partner.
• Full join: This comprehensive bridge connects all records from both tables, filling gaps with NULL values where matches don't exist. Picture an inclusive party where everyone is welcome, regardless of whether they have a partner.

Less commonly used SQL joins

• Cross join: This bridge creates every possible connection between two tables, pairing each row from the first table with every row from the second. It is like speed dating, where everyone must meet everyone else exactly once. Due to its resource-intensive nature, use this bridge sparingly.
• Self join: A unique bridge that connects a table to itself, typically used for hierarchical relationships. Think of an organizational chart where employees and their managers come from the same employee list.

Choosing the right bridge: When to use which SQL join

Now that we’ve defined each type of join in SQL, let’s tackle the nuanced cases of using any of these join types over another.

Should I use an Inner Join or Left Join?

Use an Inner Join when you only want records with valid relationships between tables, such as customers with existing orders. It’s ideal when data integrity is crucial and you want to exclude any "orphaned" records. This is a good choice when you're confident that missing relationships indicate insufficient data that should be excluded.

Use a Left Join when you need to preserve all records from the main table, even if there are no matches. For example, you might want to see all customers, including those who haven’t placed an order. Left Joins are also useful for identifying missing relationships, like customers with no orders, or when performing aggregate calculations that include zeros or null values.

Should I use a Left Join or Right Join?

Left Joins are generally preferred because they are more intuitive to read from left to right, maintain a consistent pattern in your query structuring, and place the "main" table first.

Use a Right Join if you are appending to an existing query and restructuring it would be difficult if the right table is your main focus or if you're working with legacy code that consistently uses right joins.

Should I use a Full Join or a Left/Right Join?

Full Joins are appropriate when you need to audit data inconsistencies between systems, merge two datasets that should match but might have discrepancies, or identify orphaned records on both sides. They’re also helpful during data cleanup to see all possible mismatches.

When should I use a Cross Join?

Cross Joins are best for specific use cases like generating all possible combinations (e.g., product-color combinations), creating calculation tables such as multiplication tables, or performing time series analysis where every possible combination of dates and events is needed. However, they should be avoided for routine relational queries due to their impact on performance.

When should I use a Self Join?

Self Joins are useful for hierarchical data, such as employee-manager relationships, parent-child categories, thread-reply structures in comments, or bills of materials where parts contain other parts. They’re also helpful for comparing rows within the same table, such as finding duplicate records, comparing time periods (e.g., current vs previous), or identifying related items.

Construction techniques: How to build SQL joins

We’ve now learned about the different types of joins and when using one over the other is appropriate. Next, let’s dive deeper into the syntax of joins and look at the specific syntax and structure of the various joins in SQL. It is akin to building different types of bridges, as each structure is similar, though each has its specific nuances.

* (Note: Though common table expressions (CTEs) and subqueries are integral concepts to learn for writing complex queries with and without joins, they’ve been intentionally excluded to keep our discussion of joins more focused.)

Building the Inner Join

-- Scenario: Analyzing Customer Purchase Patterns 
-- This bridge connects customer profiles with their purchase history 
-- to identify high-value customers and their buying behavior

SELECT customers.name, orders.order_date
FROM customers
INNER JOIN orders
    ON customers.customer_id = orders.customer_id
WHERE orders.total > 100;

-- Building blocks show how this bridge is constructed: 
-- 1. Start with customer foundation (FROM customers) 
-- 2. Connect to order data (INNER JOIN orders) 
-- 3. Establish connection points (ON customer_id match) 
-- 4. Filter for insights (WHERE total > 100)

Building the Left Join

-- Scenario: Customer Engagement Analysis 
-- This bridge helps identify both active and inactive customers
-- This bridge type is common for finding “missing” relationships
-- Perfect for customer retention and reactivation campaigns

SELECT customers.name,
    COALESCE(COUNT(orders.order_id), 0) as order_count
FROM customers
LEFT JOIN orders
    ON customers.customer_id = orders.customer_id
GROUP BY customers.name;

-- Building blocks emphasize customer-centric analysis: 
-- 1. Preserve all customer records 
-- 2. Count orders (even zero) 
-- 3. Group to see patterns

Building the Right Join

-- Scenario: Sales Team Performance Overview 
-- This bridge connects all sales employees with their handled orders 
-- Useful for team performance analysis and workload distribution

SELECT orders.order_id,
    employees.name as handled_by
FROM orders
RIGHT JOIN employees
    ON orders.employee_id = employees.employee_id
WHERE employees.department = 'Sales';

-- Building blocks focus on employee coverage: 
-- 1. Keep all employees in view 
-- 2. Show their order handling 
-- 3. Focus on sales department

Building the Full Join

-- Scenario: Department Resource Analysis 
-- This complex bridge connects three data points to create 
-- a complete picture of departmental resources and utilization

SELECT a.department_name,
    COALESCE(b.employee_count, 0) as current_employees,
    COALESCE(c.budget, 0) as department_budget
FROM departments a
FULL JOIN employee_counts b
    ON a.dept_id = b.dept_id
FULL JOIN financial_data c
    ON a.dept_id = c.dept_id;

-- Building blocks demonstrate multi-table connections: 
-- 1. Start with department structure 
-- 2. Add employee data 
-- 3. Layer in financial information

Building the Self Join

-- Scenario: Organizational Hierarchy Mapping 
-- This self-referential bridge creates a clear view of 
-- reporting relationships within the organization

SELECT 
    emp.name as employee,
    mgr.name as manager
FROM employees emp
LEFT JOIN employees mgr
    ON emp.manager_id = mgr.employee_id
ORDER BY mgr.name, emp.name;

-- Building blocks show hierarchical connections: 
-- 1. Connect table to itself 
-- 2. Preserve all employees 
-- 3. Order for clarity

Building the Cross Join

-- Scenario: Product Variant Generation 
-- This bridge creates all possible product combinations 
-- Useful for inventory planning and catalog generation

SELECT
    products.name as product,
    colors.color_name,
    sizes.size_code
FROM products
CROSS JOIN colors
CROSS JOIN sizes
WHERE products.category = 'shirts';

-- Building blocks explain combination generation: 
-- 1. Start with base products 
-- 2. Multiply by all colors 
-- 3. Multiply by all sizes

Bridge maintenance: Best practices and optimization for SQL joins

Naming conventions and syntax

1. Always use explicit join syntax (JOIN) instead of implicit (WHERE) joins

-- Good
FROM orders o JOIN customers c ON o.customer_id = c.id
-- Bad
FROM orders o, customers c WHERE o.customer_id = c.id

2. Use consistent, descriptive table aliasessome text
   • Use meaningful abbreviations (e.g., 'cust' for customers)
   • For self-joins, use descriptive aliases (e.g., 'manager', 'employee')
   • Keep aliases short but clear (2-4 characters typically)
3. Standardize join terminologysome text
   • Use INNER JOIN instead of just JOIN for clarity
   • Use LEFT JOIN instead of LEFT OUTER JOIN (OUTER is redundant)
   • Prefer LEFT JOIN over RIGHT JOIN for consistency

How to maintain SQL join code readability

Format joins consistently

SELECT c.name,
    o.order_date,
    p.product_name
FROM customers c
INNER JOIN orders o
    ON c.id = o.customer_id
LEFT JOIN products p
    ON o.product_id = p.id
WHERE c.status = 'active'

Indent join conditions and align related elements

• Put each join on a new line
• Indent ON clauses
• Align multiple conditions within the same join

Comment complex joins

FROM orders o
LEFT JOIN refunds r
    ON o.order_id = r.order_id
    AND r.status = 'approved'  -- Only include approved refunds

Optimizing your SQL queries

Join order matters for performance

• Start with the largest/filtered table in the FROM clause
• Join smaller tables to larger ones when possible
• Use subqueries or CTEs* for complex filtering before joining

Include only necessary joins

• Avoid joining tables just to get columns you don't need
• Consider using EXISTS instead of JOIN for validation checks

General best practices

Always specify join criteria explicitly

-- Good
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id

-- Bad (implicit cross join)
FROM orders o
INNER JOIN customers c

Use table prefixes for column names in join conditions

-- Good
ON employees.department_id = departments.id

-- Bad
ON department_id = id

Handle NULL values appropriately

-- Consider COALESCE when joining on nullable columns
ON COALESCE(a.nullable_col, 0) = COALESCE(b.nullable_col, 0)

Default to LEFT JOIN over RIGHT JOIN

Use LEFT JOIN unless you have a specific reason for preferring a RIGHT JOIN. Left joins are more intuitive and consistent in most cases.

Use INNER JOIN when data integrity is crucial

Rely on INNER JOIN when you only want to include records with valid relationships between tables.

Use FULL JOIN sparingly

FULL JOIN should primarily be used for tasks like data analysis or cleanup, where you need to see all possible mismatches or inconsistencies between tables.

Consider performance implications

Be mindful of the performance impact of certain joins:

• CROSS JOINs can generate an extremely large dataset, so use them only when necessary for specific use cases like generating all possible combinations.
• Complex FULL JOINs can also be computationally expensive, so use them cautiously.

Document your join choices

When the choice of join isn't apparent, add comments to explain why you chose a specific type. This will help others (and your future self) understand your logic.

Beyond basic bridges: SQL recap and going forward

You're now well on your way to mastering building bridges! SQL joins are powerful tools that serve as the foundation for working with relational databases, allowing us to bridge different islands of data and extract meaningful insights. 

As you continue to develop your skills, you'll discover there's a whole world of advanced techniques waiting to be explored: from the elegance of CTEs that help you build complex, multi-layered bridges to the precision of optimized indexes that make your bridges more efficient, to the sophistication of dynamic SQL that allows your bridges to adapt to changing conditions.

The best way to master joins is to start using them in your queries. Begin with simple Inner and Left Joins, following the best practices for syntax and organization we discussed, and then gradually experiment with more complex join operations as your confidence grows.

So take that first step. Open up Sigma, leverage your SQL knowledge to connect your data islands, and build bridges that transform scattered data points into valuable insights. Your journey to becoming a master bridge builder begins now. 

‍