Paradime | INNER JOIN / LEFT JOIN / RIGHT JOIN / FULL OUTER JOIN

SQL Keywords

INNER JOIN / LEFT JOIN / RIGHT JOIN / FULL OUTER JOIN

Feb 23, 2026

min read

Category: JOIN Operations

Platform Support:

✅ Snowflake | ✅ BigQuery | ✅ Databricks

Description

JOIN operations combine rows from two or more tables based on related columns. Different JOIN types determine which rows are included in the result set. Essential for relational data analysis and combining data from multiple sources.

Syntax

-- INNER JOIN (default)
SELECT columns
FROM table1
INNER JOIN table2 ON table1.column = table2.column;

-- LEFT JOIN (LEFT OUTER JOIN)
SELECT columns
FROM table1
LEFT JOIN table2 ON table1.column = table2.column;

-- RIGHT JOIN (RIGHT OUTER JOIN)
SELECT columns
FROM table1
RIGHT JOIN table2 ON table1.column = table2.column;

-- FULL OUTER JOIN
SELECT columns
FROM table1
FULL OUTER JOIN table2 ON

-- INNER JOIN (default)
SELECT columns
FROM table1
INNER JOIN table2 ON table1.column = table2.column;

-- LEFT JOIN (LEFT OUTER JOIN)
SELECT columns
FROM table1
LEFT JOIN table2 ON table1.column = table2.column;

-- RIGHT JOIN (RIGHT OUTER JOIN)
SELECT columns
FROM table1
RIGHT JOIN table2 ON table1.column = table2.column;

-- FULL OUTER JOIN
SELECT columns
FROM table1
FULL OUTER JOIN table2 ON

-- INNER JOIN (default)
SELECT columns
FROM table1
INNER JOIN table2 ON table1.column = table2.column;

-- LEFT JOIN (LEFT OUTER JOIN)
SELECT columns
FROM table1
LEFT JOIN table2 ON table1.column = table2.column;

-- RIGHT JOIN (RIGHT OUTER JOIN)
SELECT columns
FROM table1
RIGHT JOIN table2 ON table1.column = table2.column;

-- FULL OUTER JOIN
SELECT columns
FROM table1
FULL OUTER JOIN table2 ON

Platform-Specific Notes

Snowflake:

All JOIN types fully supported
Also supports LATERAL joins for unnesting
Efficient join optimization
Can use USING clause for same column names

BigQuery:

All JOIN types fully supported
CROSS JOIN UNNEST for array expansion
Comma joins deprecated (use explicit JOIN)
Optimized for large-scale joins

Databricks:

All JOIN types fully supported
Broadcast joins for small tables
Spark-optimized join strategies
Supports LATERAL VIEW for array expansion

JOIN Types Visual Guide

Tables for examples:

customers: customer_id, name
orders: order_id, customer_id, total

Example 1: INNER JOIN (Only Matching Rows)

All Platforms:

SELECT 
    c.customer_id,
    c.name,
    o.order_id,
    o.order_date,
    o.total
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
ORDER BY c.customer_id,

SELECT 
    c.customer_id,
    c.name,
    o.order_id,
    o.order_date,
    o.total
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
ORDER BY c.customer_id,

SELECT 
    c.customer_id,
    c.name,
    o.order_id,
    o.order_date,
    o.total
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
ORDER BY c.customer_id,

Sample Data:

customers table:

customer_id	name
1	John Smith
2	Maria Garcia
3	Sarah Johnson
4	Ahmed Hassan

orders table:

order_id	customer_id	order_date	total
101	1	2024-01-15	250.00
102	2	2024-01-16	180.00
103	1	2024-01-20	320.00
104	5	2024-01-22	450.00

Result (INNER JOIN):

customer_id	name	order_id	order_date	total
1	John Smith	101	2024-01-15	250.00
1	John Smith	103	2024-01-20	320.00
2	Maria Garcia	102	2024-01-16	180.00

Note: Customer 3, 4 excluded (no orders); Order 104 excluded (customer 5 not in customers table)

Example 2: LEFT JOIN (All Left Rows + Matching Right)

All Platforms:

SELECT 
    c.customer_id,
    c.name,
    o.order_id,
    o.order_date,
    o.total,
    COALESCE(o.total, 0) as order_total,
    CASE 
        WHEN o.order_id IS NULL THEN 'No Orders'
        ELSE 'Has Orders'
    END as order_status
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
ORDER BY c.customer_id,

SELECT 
    c.customer_id,
    c.name,
    o.order_id,
    o.order_date,
    o.total,
    COALESCE(o.total, 0) as order_total,
    CASE 
        WHEN o.order_id IS NULL THEN 'No Orders'
        ELSE 'Has Orders'
    END as order_status
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
ORDER BY c.customer_id,

SELECT 
    c.customer_id,
    c.name,
    o.order_id,
    o.order_date,
    o.total,
    COALESCE(o.total, 0) as order_total,
    CASE 
        WHEN o.order_id IS NULL THEN 'No Orders'
        ELSE 'Has Orders'
    END as order_status
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
ORDER BY c.customer_id,

Result (LEFT JOIN):

customer_id	name	order_id	order_date	total	order_total	order_status
1	John Smith	101	2024-01-15	250.00	250.00	Has Orders
1	John Smith	103	2024-01-20	320.00	320.00	Has Orders
2	Maria Garcia	102	2024-01-16	180.00	180.00	Has Orders
3	Sarah Johnson	NULL	NULL	NULL	0.00	No Orders
4	Ahmed Hassan	NULL	NULL	NULL	0.00	No Orders

Note: All customers included; NULLs for customers without orders

Example 3: RIGHT JOIN (All Right Rows + Matching Left)

All Platforms:

SELECT 
    o.order_id,
    o.order_date,
    o.total,
    c.customer_id,
    c.name,
    COALESCE(c.name, 'Unknown Customer') as customer_name
FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id
ORDER BY

SELECT 
    o.order_id,
    o.order_date,
    o.total,
    c.customer_id,
    c.name,
    COALESCE(c.name, 'Unknown Customer') as customer_name
FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id
ORDER BY

SELECT 
    o.order_id,
    o.order_date,
    o.total,
    c.customer_id,
    c.name,
    COALESCE(c.name, 'Unknown Customer') as customer_name
FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id
ORDER BY

Result (RIGHT JOIN):

order_id	order_date	total	customer_id	name	customer_name
101	2024-01-15	250.00	1	John Smith	John Smith
102	2024-01-16	180.00	2	Maria Garcia	Maria Garcia
103	2024-01-20	320.00	1	John Smith	John Smith
104	2024-01-22	450.00	NULL	NULL	Unknown Customer

Note: All orders included; NULL for order 104 (customer 5 not in customers table)

Example 4: FULL OUTER JOIN (All Rows from Both Tables)

All Platforms:

SELECT 
    COALESCE(c.customer_id, o.customer_id) as customer_id,
    c.name,
    o.order_id,
    o.total,
    CASE 
        WHEN c.customer_id IS NULL THEN 'Order Without Customer'
        WHEN o.order_id IS NULL THEN 'Customer Without Orders'
        ELSE 'Matched'
    END as match_status
FROM customers c
FULL OUTER JOIN orders o ON c.customer_id = o.customer_id
ORDER BY customer_id,

SELECT 
    COALESCE(c.customer_id, o.customer_id) as customer_id,
    c.name,
    o.order_id,
    o.total,
    CASE 
        WHEN c.customer_id IS NULL THEN 'Order Without Customer'
        WHEN o.order_id IS NULL THEN 'Customer Without Orders'
        ELSE 'Matched'
    END as match_status
FROM customers c
FULL OUTER JOIN orders o ON c.customer_id = o.customer_id
ORDER BY customer_id,

SELECT 
    COALESCE(c.customer_id, o.customer_id) as customer_id,
    c.name,
    o.order_id,
    o.total,
    CASE 
        WHEN c.customer_id IS NULL THEN 'Order Without Customer'
        WHEN o.order_id IS NULL THEN 'Customer Without Orders'
        ELSE 'Matched'
    END as match_status
FROM customers c
FULL OUTER JOIN orders o ON c.customer_id = o.customer_id
ORDER BY customer_id,

Result (FULL OUTER JOIN):

customer_id	name	order_id	total	match_status
1	John Smith	101	250.00	Matched
1	John Smith	103	320.00	Matched
2	Maria Garcia	102	180.00	Matched
3	Sarah Johnson	NULL	NULL	Customer Without Orders
4	Ahmed Hassan	NULL	NULL	Customer Without Orders
5	NULL	104	450.00	Order Without Customer

Note: All customers AND all orders included, with NULLs where no match

Example 5: Multiple JOINs

All Platforms:

SELECT 
    c.customer_id,
    c.name as customer_name,
    o.order_id,
    o.order_date,
    p.product_name,
    oi.quantity,
    oi.unit_price,
    oi.quantity * oi.unit_price as line_total
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
INNER JOIN order_items oi ON o.order_id = oi.order_id
INNER JOIN products p ON oi.product_id = p.product_id
WHERE o.order_date >= '2024-01-01'
ORDER BY c.customer_id, o.order_id,

SELECT 
    c.customer_id,
    c.name as customer_name,
    o.order_id,
    o.order_date,
    p.product_name,
    oi.quantity,
    oi.unit_price,
    oi.quantity * oi.unit_price as line_total
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
INNER JOIN order_items oi ON o.order_id = oi.order_id
INNER JOIN products p ON oi.product_id = p.product_id
WHERE o.order_date >= '2024-01-01'
ORDER BY c.customer_id, o.order_id,

SELECT 
    c.customer_id,
    c.name as customer_name,
    o.order_id,
    o.order_date,
    p.product_name,
    oi.quantity,
    oi.unit_price,
    oi.quantity * oi.unit_price as line_total
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
INNER JOIN order_items oi ON o.order_id = oi.order_id
INNER JOIN products p ON oi.product_id = p.product_id
WHERE o.order_date >= '2024-01-01'
ORDER BY c.customer_id, o.order_id,

Sample Data:

order_items table:

order_id	product_id	quantity	unit_price
101	1	2	29.99
101	2	1	89.99
102	1	1	29.99

products table:

product_id	product_name
1	Mouse
2	Keyboard

Result:

customer_id	customer_name	order_id	order_date	product_name	quantity	unit_price	line_total
1	John Smith	101	2024-01-15	Keyboard	1	89.99	89.99
1	John Smith	101	2024-01-15	Mouse	2	29.99	59.98
2	Maria Garcia	102	2024-01-16	Mouse	1	29.99	29.99

Example 6: Self JOIN

All Platforms:

-- Find employees and their managers
SELECT 
    e.employee_id,
    e.name as employee_name,
    e.manager_id,
    m.name as manager_name,
    e.department
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id
ORDER BY e.department,

-- Find employees and their managers
SELECT 
    e.employee_id,
    e.name as employee_name,
    e.manager_id,
    m.name as manager_name,
    e.department
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id
ORDER BY e.department,

-- Find employees and their managers
SELECT 
    e.employee_id,
    e.name as employee_name,
    e.manager_id,
    m.name as manager_name,
    e.department
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id
ORDER BY e.department,

Sample Data (employees table):

employee_id	name	manager_id	department
1	Alice Chen	NULL	Executive
2	Bob Smith	1	Sales
3	Carol White	1	Engineering
4	David Brown	2	Sales
5	Emma Davis	3	Engineering

Result:

employee_id	employee_name	manager_id	manager_name	department
3	Carol White	1	Alice Chen	Engineering
5	Emma Davis	3	Carol White	Engineering
1	Alice Chen	NULL	NULL	Executive
2	Bob Smith	1	Alice Chen	Sales
4	David Brown	2	Bob Smith	Sales

JOIN Types Summary

JOIN Type	Returns	Use When
INNER	Only matching rows from both tables	Need only records that exist in both
LEFT	All from left + matching from right	Need all records from primary table
RIGHT	All from right + matching from left	Need all records from secondary table
FULL OUTER	All from both tables	Need to see all records, matched or not
CROSS	Cartesian product (all combinations)	Need all possible combinations

Common Use Cases

INNER JOIN: Order details with customer info
LEFT JOIN: All customers with their orders (including those without)
RIGHT JOIN: All products including those never ordered
FULL OUTER JOIN: Data reconciliation, finding orphaned records
SELF JOIN: Hierarchical data (employees/managers, categories/subcategories)

Performance Tips

Index join columns: Create indexes on columns used in ON clause
Filter early: Use WHERE before JOIN when possible
Join order matters: Start with smallest table
Avoid SELECT *: Only select needed columns
Use INNER JOIN when possible: More efficient than OUTER joins
Analyze query plans: Use EXPLAIN to optimize

Best Practices

Always use explicit JOIN syntax: Avoid comma joins
Use table aliases: Make queries more readable
Qualify all columns: Prefix with table alias
Handle NULLs: Use COALESCE for OUTER joins
Document complex joins: Explain business logic
Test with real data: Verify join conditions are correct

Interested to Learn More?
Try Out the Free 14-Days Trial

Start free trial

Learn

Feb 18, 2026

BigQuery Global Queries: How to Run Cross-Region SQL in 2026

Analytics

Feb 18, 2026

Context Engineering and AI Quality for Data Teams

Product

Feb 18, 2026

Accelerate Analytics Development with Paradime and Tableau

Product

Feb 18, 2026

Accelerate Analytics Development with Paradime and Tableau

Experience Analytics for the AI-Era

Start your 14-day trial today - it's free and no credit card needed

Start for free

Experience Analytics for the AI-Era

Start your 14-day trial today - it's free and no credit card needed

Start for free

Experience Analytics for the AI-Era

Start your 14-day trial today - it's free and no credit card needed

Start for free

Platform

Resources

ADD-ONs

Industries

About

Legal

Made with ❤️ in San Francisco ・ London

*dbt® and dbt Core® are federally registered trademarks of dbt Labs, Inc. in the United States and various jurisdictions around the world. Paradime is not a partner of dbt Labs. All rights therein are reserved to dbt Labs. Paradime is not a product or service of or endorsed by dbt Labs, Inc.

Start for free

Platform

Resources