Skip to main content

​
Joining Tables

RevenueBase data is organized into separate tables that link together through shared keys. This page explains how the tables relate and how to join them correctly.

​
Entity relationship overview

Here’s how the main tables connect: 
companies (1) ──── (many) contacts
    │                        │
    │                        │
    └──── (many) emails ─────┘

​
Join keys reference

From tableTo tableJoin keyJoin typeNotes
contactscompaniescontacts.company_id = companies.company_idLEFT JOINNot all contacts have a company
contactsemailscontacts.contact_id = emails.contact_idLEFT JOINA contact may have multiple emails
companiesemailscompanies.company_id = emails.company_idLEFT JOINAll emails associated with a company
TODO: Add more joins

​
Common join patterns

​
Contacts with company info

The most common join — enriching contacts with their company’s firmographic data: 
SELECT
    c.first_name,
    c.last_name,
    c.email,
    c.title,
    co.company_name,
    co.industry,
    co.employee_count,
    co.city,
    co.state
FROM contacts c
LEFT JOIN companies co ON c.company_id = co.company_id
WHERE c.email_verified = TRUE;
Use LEFT JOIN (not INNER JOIN) if you want to include contacts that don’t have a company record. Use INNER JOIN if you only want contacts with a known company.

​
Contacts with their primary email verification details

SELECT
    c.first_name,
    c.last_name,
    e.email_address,
    e.verification_status,
    e.last_verified_at,
    e.bounce_risk
FROM contacts c
JOIN emails e ON c.contact_id = e.contact_id
WHERE e.is_primary = TRUE
  AND e.verification_status = 'valid';

​
Full join: contacts + companies + emails

SELECT
    c.first_name,
    c.last_name,
    c.title,
    co.company_name,
    co.industry,
    co.employee_count,
    e.email_address,
    e.verification_status,
    e.last_verified_at
FROM contacts c
LEFT JOIN companies co ON c.company_id = co.company_id
LEFT JOIN emails e ON c.contact_id = e.contact_id AND e.is_primary = TRUE
WHERE e.verification_status = 'valid'
ORDER BY e.last_verified_at DESC;

​
Gotchas and tips

If a contact has multiple email records, joining contacts to emails will produce one row per email. To avoid duplicates, filter on e.is_primary = TRUE or use a subquery to pick one email per contact.
Not every contact has a linked company. If you use INNER JOIN on companies, you’ll silently drop those contacts. Use LEFT JOIN unless you specifically want to exclude unmatched contacts.
When joining across large tables, always include filter conditions (WHERE clauses) to reduce the working set before the join. Filtering on email_verified, last_verified_at, or industry early will significantly speed up queries.
Fill in based on common support questions and customer mistakes.

​
Joining with your own data

You can also join RevenueBase tables with your own internal data in Snowflake. The most common approach is matching on domain: 
-- Match your account list against RevenueBase companies by domain
SELECT
    your_accounts.account_name,
    co.company_name,
    co.employee_count,
    co.industry
FROM your_database.your_schema.accounts your_accounts
JOIN revenuebase.core.companies co
  ON LOWER(your_accounts.website_domain) = LOWER(co.domain);
Always normalize domains when joining — use LOWER() and strip www. prefixes to avoid mismatches.