SQL JOINs Guide: Types, Examples & Interview Prep
SQL JOINs Explained: Types & Examples
Overview
SQL JOINs are fundamental commands used to combine rows from two or more tables based on related columns. They enable powerful data retrieval across relational databases, essential for reporting, analytics, and application development.
| JOIN Type | Description | Rows Included | Example Use Case |
|---|---|---|---|
| INNER JOIN | Returns matching rows from both tables | Only rows with matching keys in both tables | Customers with orders |
| LEFT JOIN | All rows from left table + matching right rows | All left table rows, NULLs if no match on right | All customers, including those without orders |
| RIGHT JOIN | All rows from right table + matching left rows | All right table rows, NULLs if no match on left | All orders, including those without customers |
| FULL OUTER JOIN | All rows from both tables, matched where possible | All rows from both tables, NULLs where no match | Complete dataset combining customers & orders |
Quick Reference: Which JOIN Should I Use?
Use this simple guide to choose the right JOIN type:
- Want only matching rows? → Use INNER JOIN
- Want all rows from the first table? → Use LEFT JOIN
- Want all rows from the second table? → Use RIGHT JOIN (or switch tables and use LEFT JOIN)
- Want all rows from both tables? → Use FULL OUTER JOIN
Explore detailed examples, additional JOIN types like SELF and NATURAL JOIN, performance tips, and interview questions below.
Introduction to SQL JOINs: Definition and Purpose
In relational databases, data is stored across multiple tables to reduce redundancy and improve organization. To get useful information, you often need to combine data from different tables. SQL JOINs let you do this.
A SQL JOIN combines rows from two or more tables based on a related column, usually a column that contains matching values, such as CustomerID or OrderNumber. JOINs let you link related data from different tables, which helps you create reports, analyze data, and build applications.
Understanding "Matching": Rows "match" when the values in the join columns are equal. For example, a customer with CustomerID = 1 matches an order with CustomerID = 1. When rows match, they are combined into a single row in the result.
Example: In an e-commerce platform, you can generate reports showing customers and their orders, including those who haven't placed any yet.
This guide is designed for beginners and intermediate users to understand the types of JOINs, their syntax, practical examples, performance considerations, and interview preparation.
Before You Start: Prerequisites
Before diving into JOINs, you should be familiar with:
- Basic SELECT statements: Knowing how to query data from a single table
- Understanding tables and columns: Familiarity with how data is organized in relational databases
- Basic WHERE clause: Understanding how to filter data
If you're new to SQL, consider learning these basics first, then return to this guide.
INNER JOIN: Syntax and Example
Syntax
SELECT columns
FROM table1
INNER JOIN table2
ON table1.common_column = table2.common_column;
Note: Replace
table1,table2, andcommon_columnwith your actual table names and column names. For example,FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID.
How it Works
INNER JOINreturns only the rows where there is a match in both tables.- The
ONclause specifies the condition for matching rows (which columns to compare). - If the
ONclause is missing or incorrect, the JOIN will cause an error or give you incorrect results.
Understanding NULL: NULL represents missing or unknown data. In JOIN results, NULL appears when there's no matching data from the other table.
A Practical Example
Consider two tables:
Customers
| CustomerID | Name | City | ReferredBy |
|---|---|---|---|
| 1 | Alice | London | NULL |
| 2 | Bob | Manchester | 1 |
| 3 | Charlie | Leeds | 1 |
Note: The
ReferredBycolumn shows which customer referred this customer (we'll use this for SELF JOIN examples later).CustomerIDis the PRIMARY KEY, which ensures each row has a unique identifier.
Orders
| OrderID | CustomerID | Product |
|---|---|---|
| 101 | 1 | Laptop |
| 102 | 3 | Smartphone |
| 103 | 1 | Keyboard |
| 104 | 4 | Tablet |
Query to find customers with orders
SELECT Customers.Name, Orders.Product FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
Result:
| Name | Product |
|---|---|
| Alice | Laptop |
| Charlie | Smartphone |
| Alice | Keyboard |
How this query works step-by-step:
- The query starts with the
Customerstable and looks at each customer row. - For each customer, it searches the
Orderstable for rows whereCustomerIDmatches. - When a match is found, it combines the customer's
Namewith the order'sProduct. - Only rows with matches in both tables are included in the result.
Note: Bob is excluded because he has no orders. Since there's no matching order for Bob, INNER JOIN doesn't include him in the results.
When to Use INNER JOIN
Use INNER JOIN when you only want rows that have matches in both tables. This is perfect for:
- Finding customers who have placed orders
- Matching products with their categories
- Linking employees to their departments
- Any scenario where you need complete data from both tables
USE ROLE SNOWFLAKE_LEARNING_ROLE; USE DATABASE SNOWFLAKE_LEARNING_DB; USE WAREHOUSE SNOWFLAKE_LEARNING_WH; SET schema_name = CONCAT(CURRENT_USER, '_SQL_JOIN_EXAMPLE'); CREATE OR REPLACE SCHEMA IDENTIFIER($schema_name); USE SCHEMA IDENTIFIER($schema_name); -- Customers table -- PRIMARY KEY ensures each row has a unique identifier CREATE OR REPLACE TABLE Customers ( CustomerID INT PRIMARY KEY, Name VARCHAR(50), City VARCHAR(50), ReferredBy INT ); INSERT INTO Customers VALUES (1, 'Alice', 'London', NULL), (2, 'Bob', 'Manchester', 1), (3, 'Charlie', 'Leeds', 1); -- Orders table CREATE OR REPLACE TABLE Orders ( OrderID INT PRIMARY KEY, CustomerID INT, Product VARCHAR ); INSERT INTO Orders VALUES (101, '1', 'Laptop'), (102, '3', 'Smartphone'), (103, '1', 'Keyboard'), (104, '4', 'Tablet'); -- INNER JOIN EXAMPLE SELECT Customers.Name, Orders.Product FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
LEFT JOIN: Use Cases and Examples
Understanding "Left" and "Right" Tables
In SQL JOINs, the left table is the one listed first in the FROM clause, and the right table is the one listed after the JOIN keyword. For example, in FROM Customers LEFT JOIN Orders, Customers is the left table and Orders is the right table.
Behavior
- Returns all rows from the left table.
- Includes matching rows from the right table.
- If no match exists, right table columns show NULL (which means there's no matching data).
Syntax
SELECT columns
FROM table1
LEFT JOIN table2
ON table1.common_column = table2.common_column;
Note: Replace
table1,table2, andcommon_columnwith your actual table names and column names.
Query to list all customers in the customer table and whether or not they purchased a product:
SELECT Customers.Name, Orders.Product -- What columns to show FROM Customers -- Start with this table (left table) LEFT JOIN Orders -- Add rows from this table (right table) ON Customers.CustomerID = Orders.CustomerID; -- Match on this column
Result:
Notice that Bob now appears in the results. LEFT JOIN includes every customer from the Customers table, even if they don't have any orders. When there's no matching order, the Product column shows NULL (meaning no data):
| Name | Product |
|---|---|
| Alice | Laptop |
| Alice | Keyboard |
| Charlie | Smartphone |
| Bob | NULL |
When to Use LEFT JOIN
Use LEFT JOIN when you want all rows from the first table, even if there's no match in the second table. This is useful for:
- Finding all customers, including those who haven't placed orders yet (useful for marketing outreach)
- Listing all products, including those that haven't been ordered
- Showing all employees, including those without assigned projects
- Any scenario where you need to see everything from the primary table, with optional related data
RIGHT JOIN: When and How to Use
Understanding "Left" and "Right" Tables
Remember: The left table is the one in the FROM clause, and the right table is the one after the JOIN keyword. In FROM Customers RIGHT JOIN Orders, Customers is the left table and Orders is the right table.
Behavior
- Returns all rows from the right table.
- Includes matching rows from the left table.
- If no match exists, left table columns show NULL (meaning there's no matching data).
Syntax
SELECT columns FROM table1 RIGHT JOIN table2 ON table1.common_column = table2.common_column;
Note: Replace
table1,table2, andcommon_columnwith your actual table names and column names.
Example
Using the previous Customers and Orders tables, to find all orders and their customers (including orders without customers, if any):
SELECT Customers.Name, Orders.Product FROM Customers RIGHT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
Result:
RIGHT JOIN returns all rows from the Orders table (the table after the JOIN keyword). Order 104 has CustomerID 4, but there's no customer with ID 4 in the Customers table, so the Name column shows NULL (meaning no matching customer):
| Name | Product |
|---|---|
| Alice | Laptop |
| Alice | Keyboard |
| Charlie | Smartphone |
| NULL | Tablet |
Note: Order 104 (Tablet) has no matching customer, so the Name column shows NULL (no matching data).
When to Use RIGHT JOIN
Use RIGHT JOIN when you want all rows from the second table, even if there's no match in the first table. This is useful for:
- Finding all orders, including those without customer records (useful for data quality checks)
- Listing all products, including those not yet assigned to categories
- Showing all events, including those without attendees
Tip: RIGHT JOIN can often be replaced by switching the table order and using LEFT JOIN for better readability. For example,
FROM Customers RIGHT JOIN Ordersis the same asFROM Orders LEFT JOIN Customers.
FULL OUTER JOIN vs FULL JOIN: Clarifying the Difference
Explanation
- FULL OUTER JOIN and FULL JOIN are synonyms in most SQL dialects.
- Returns all rows from both tables.
- Matches rows where possible; unmatched rows show NULL in the other table's columns.
Syntax
SELECT columns FROM table1 FULL OUTER JOIN table2 ON table1.common_column = table2.common_column;
Note: Replace
table1,table2, andcommon_columnwith your actual table names and column names.
Example
Using Customers and Orders:
SELECT Customers.Name, Orders.Product FROM Customers FULL OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
Result:
FULL OUTER JOIN returns all rows from both tables. Bob has no orders (shows NULL for Product, meaning no matching order), and Order 104 (Tablet) has no matching customer (shows NULL for Name, meaning no matching customer):
| Name | Product |
|---|---|
| Alice | Laptop |
| Alice | Keyboard |
| Charlie | Smartphone |
| Bob | NULL |
| NULL | Tablet |
When to Use FULL OUTER JOIN
Use FULL OUTER JOIN when you need to see all rows from both tables, regardless of matches. This is useful for:
- Creating complete datasets that show all customers and all orders, even if they don't match
- Data quality analysis to find orphaned records in both tables
- Merging data from two sources where you want to see everything
- Any scenario where you need a complete picture from both tables
Common Misconceptions
- Some beginners confuse
FULL JOINwithUNION;FULL JOINpreserves unmatched rows with NULLs. - Not all SQL engines support
FULL OUTER JOIN(e.g., MySQL before 8.0).
Additional JOIN Types: SELF JOIN and NATURAL JOIN Explained
Understanding Table Aliases
Before we explore SELF JOIN, it's important to understand table aliases. An alias is a shorter name you give to a table in your query. You create an alias by adding a name after the table name in the FROM clause.
For example:
FROM Customers cmeans you can refer to the Customers table ascFROM Orders omeans you can refer to the Orders table aso
Aliases are especially useful in SELF JOINs (where you join a table to itself) because you need to distinguish between the two "copies" of the same table. You'll see this in the SELF JOIN example below.
SELF JOIN
A SELF JOIN is when you join a table to itself. Think of it as creating two copies of the same table and joining them together. This is useful for hierarchical or relational data within the same table.
Understanding the syntax: Notice how we use aliases (c and r) to distinguish between the two "copies" of the Customers table. The alias c represents customers, and r represents their referrers.
Example: Customer Referral Relationship
Using the Customers table, we can find which customers referred other customers:
Query to list customers with their referrers:
SELECT c.Name AS Customer, r.Name AS ReferredBy FROM Customers c LEFT JOIN Customers r ON c.ReferredBy = r.CustomerID;
Breaking down the query:
FROM Customers c- Start with the Customers table, call itcLEFT JOIN Customers r- Join to the same Customers table again, but call itr(for referrer)ON c.ReferredBy = r.CustomerID- Match where a customer's ReferredBy value equals another customer's CustomerID
Query Result:
| Customer | ReferredBy |
|---|---|
| Alice | NULL |
| Bob | Alice |
| Charlie | Alice |
When to Use SELF JOIN
Use SELF JOIN when you need to find relationships within the same table. Common use cases include:
- Hierarchical data: Employee-manager relationships, organizational charts
- Referral systems: Customer referral programs (like our example)
- Sequential data: Finding previous/next records, time-based relationships
- Comparison within table: Comparing rows to other rows in the same table
NATURAL JOIN
Automatically joins tables on all columns with the same name. Syntax:
SELECT columns FROM table1 NATURAL JOIN table2;
Example
Using the Customers and Orders tables, which both have a CustomerID column:
SELECT Customers.Name, Orders.Product FROM Customers NATURAL JOIN Orders;
Result:
NATURAL JOIN automatically finds columns with the same name in both tables and uses them to join. In this case, both tables have CustomerID, so it joins on that column. This produces the same result as an INNER JOIN:
| Name | Product |
|---|---|
| Alice | Laptop |
| Alice | Keyboard |
| Charlie | Smartphone |
When to Use NATURAL JOIN
NATURAL JOIN is rarely recommended because:
- It can lead to unexpected results if tables share columns unintentionally
- It's less explicit than writing out the JOIN condition
- It can break if table schemas change
Best practice: Use explicit JOIN conditions (with the ON clause) instead of NATURAL JOIN. This makes your code clearer, easier to maintain, and less prone to errors.
⚠️ Use with caution:
- Can lead to unexpected results if tables share columns unintentionally.
- Not recommended for production code -- in production environments, explicit join conditions improve clarity and maintainability.
Performance Considerations: JOINs vs Subqueries
| Aspect | JOINs | Subqueries |
|---|---|---|
| Execution | Generally faster, especially with indexes | Can be slower, depending on query plan |
| Readability | Clear for combining related tables | Useful for filtering or aggregation |
| Optimization | Better optimizations by SQL engines | May cause nested loops or scans |
When to Prefer JOINs
- Combining related data from multiple tables.
- When indexes exist on join columns.
- For queries requiring multiple columns from joined tables.
When to Prefer Subqueries
- Filtering based on aggregated or computed values.
- When logic is easier to express in nested queries.
Indexing Tips for JOIN Performance
- Index columns used in JOIN conditions.
- Use composite indexes if joining on multiple columns.
- Avoid functions on join columns that prevent index use.
Performance Tips Callout
- Always specify explicit JOIN conditions (ON clause).
- Prefer INNER JOINs when possible for better performance.
- Use EXPLAIN plans to analyze query execution.
- Avoid unnecessary columns in SELECT to reduce data load.
- Consider denormalization if JOINs become a bottleneck in large datasets.
Troubleshooting Common Issues
When working with JOINs, you might encounter these issues. Here's how to solve them:
Issue: Getting Too Many Rows
Symptoms: Your query returns more rows than expected.
Possible Causes & Solutions:
- Multiple matches: If one row in the first table matches multiple rows in the second table, you'll get multiple result rows. This is correct behavior - check if this is what you intended.
- Missing ON clause: If you forgot the
ONclause, you'll get a Cartesian product (every row matched with every row). Always include theONclause. - Wrong join columns: Verify you're joining on the correct columns that should have a one-to-one or one-to-many relationship.
Issue: Getting NULLs When You Don't Expect Them
Symptoms: NULL values appear in your results unexpectedly.
Possible Causes & Solutions:
- Using OUTER JOIN instead of INNER JOIN: If you're seeing NULLs, you're likely using LEFT, RIGHT, or FULL OUTER JOIN. Switch to INNER JOIN if you only want matching rows.
- Missing data: NULLs indicate there's no matching data in the other table. Check if this is expected or if there's a data quality issue.
Issue: Query is Running Slowly
Symptoms: Your JOIN query takes a long time to execute.
Possible Causes & Solutions:
- Missing indexes: Check if the columns you're joining on are indexed. Indexes significantly speed up JOINs.
- Large tables: JOINs on very large tables can be slow. Consider filtering with WHERE clauses before joining.
- Too many columns: Selecting unnecessary columns increases data transfer. Only SELECT the columns you need.
Issue: Getting No Results
Symptoms: Your query returns zero rows.
Possible Causes & Solutions:
- No matching data: Check if there are actually matching values in both tables for your join condition.
- Wrong join columns: Verify you're joining on columns that contain matching values (same data type and format).
- Data type mismatch: Ensure the columns you're joining have compatible data types.
Common Mistakes and How to Avoid Them
When learning SQL JOINs, beginners often make these mistakes. Here's how to avoid them:
Mistake 1: Forgetting the ON Clause
Problem: Without the ON clause, you'll get a Cartesian product (every row from the first table matched with every row from the second table), which is usually not what you want.
Solution: Always include the ON clause to specify how tables should be joined.
-- ❌ Wrong - Missing ON clause SELECT Customers.Name, Orders.Product FROM Customers INNER JOIN Orders; -- ✅ Correct - Includes ON clause SELECT Customers.Name, Orders.Product FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
Mistake 2: Using the Wrong JOIN Type
Problem: Using INNER JOIN when you need LEFT JOIN (or vice versa) can exclude important data.
Solution: Think about what data you need:
- Need only matching rows? → INNER JOIN
- Need all rows from the first table? → LEFT JOIN
- Need all rows from both tables? → FULL OUTER JOIN
Mistake 3: Not Understanding NULL Values
Problem: Beginners sometimes expect NULL values to be excluded automatically, or are surprised when they appear.
Solution: Remember that NULL means "no matching data." In OUTER JOINs (LEFT, RIGHT, FULL), NULL appears when there's no match. INNER JOIN excludes rows with no matches entirely.
Mistake 4: Confusing LEFT and RIGHT JOIN
Problem: It's easy to mix up which table is "left" and which is "right."
Solution: Remember: the left table is in the FROM clause, and the right table is after the JOIN keyword. If you're confused, you can always switch the table order and use LEFT JOIN instead of RIGHT JOIN.
Mistake 5: Joining on the Wrong Columns
Problem: Joining on columns that don't have matching values will give you no results or incorrect results.
Solution: Make sure the columns you're joining on contain the same type of data and use the same values. For example, joining CustomerID (integer) to CustomerName (text) won't work.
Mistake 6: Getting Too Many Rows
Problem: Sometimes JOINs return more rows than expected, especially if there are multiple matches.
Solution: Check your data - if one customer has multiple orders, you'll get multiple rows (one for each order). This is correct behavior, but make sure it's what you want.
Practice Exercises
Try these exercises to test your understanding. Use the Customers and Orders tables from the examples above.
Exercise 1: Find Customers with Orders
Write a query to find all customers who have placed orders. Show the customer name and product.
Click to see solution
SELECT Customers.Name, Orders.Product FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
This uses INNER JOIN because we only want customers who have orders.
Exercise 2: Find All Customers, Including Those Without Orders
Write a query to list all customers, showing their orders if they have any. Include customers who haven't placed any orders yet.
Click to see solution
SELECT Customers.Name, Orders.Product FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
This uses LEFT JOIN because we want all customers, even if they don't have orders.
Exercise 3: Find All Orders, Including Those Without Customers
Write a query to list all orders, showing the customer name if available. Include orders that don't have a matching customer.
Click to see solution
SELECT Customers.Name, Orders.Product FROM Customers RIGHT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
This uses RIGHT JOIN because we want all orders, even if they don't have a matching customer.
Practical Example: Joining Multiple Tables
Joining Customers, Orders, and Referrers
This example demonstrates how to join three tables using the same Customers and Orders dataset. We'll join Customers to Orders, and then join back to Customers again (using the ReferredBy relationship) to show who referred each customer.
Tables:
- Customers (CustomerID, Name, ReferredBy)
- Orders (OrderID, CustomerID, Product)
Query:
List customers with their orders and who referred them:
SELECT c.Name AS Customer, o.Product, r.Name AS ReferredBy FROM Customers c INNER JOIN Orders o ON c.CustomerID = o.CustomerID LEFT JOIN Customers r ON c.ReferredBy = r.CustomerID;
Explanation
- First, join
Customers(aliased asc) withOrdersonCustomerIDto get each customer's orders. - Then, join the result with
Customersagain (aliased asrfor "referrer") onReferredBy = CustomerIDto find who referred each customer. - We use LEFT JOIN for the referrer because some customers (like Alice) may not have a referrer (ReferredBy is NULL).
Result:
| Customer | Product | ReferredBy |
|---|---|---|
| Alice | Laptop | NULL |
| Alice | Keyboard | NULL |
| Charlie | Smartphone | Alice |
Note: Alice appears twice because she has two orders. She has NULL for ReferredBy because she wasn't referred by anyone.
Common SQL JOIN Interview Questions and Answers
| Question | Answer |
|---|---|
| What is the difference between an INNER JOIN and a LEFT JOIN? | INNER JOIN returns only matching rows; LEFT JOIN returns all left table rows plus matches. |
| How do NULL values affect JOIN results? | NULLs appear in columns of unmatched rows in OUTER JOINs; INNER JOIN excludes unmatched rows. |
| When is a JOIN faster than a subquery? | JOINs are usually faster when combining related tables with indexes; subqueries may be slower. |
| Can you join more than two tables in SQL? | Yes, by chaining multiple JOINs with appropriate ON conditions. |
| What is a SELF JOIN and when should it be used? | Joining a table to itself to query hierarchical or relational data within the same table. |
| What is the difference between FULL JOIN and FULL OUTER JOIN? | They are synonyms; both return all rows from both tables, matched where possible. |
| What are best practices for optimizing JOIN queries? | Use indexes, specify join conditions explicitly, avoid SELECT *, analyze query plans. |
Summary and Best Practices for Using SQL JOINs
- Understand the purpose and behavior of each JOIN type.
- Always specify explicit join conditions using the ON clause.
- Prefer INNER JOINs for matching data; use LEFT/RIGHT/FULL OUTER JOINs for including unmatched rows.
- Use SELF JOINs for hierarchical data and avoid NATURAL JOINs unless you fully understand their behavior.
- Optimize JOIN performance with proper indexing and query analysis.
- Practice writing multi-table JOINs and review query plans regularly.
- Prepare for interviews by mastering JOIN concepts and common questions.
FAQ
❓Q: What are the different types of SQL JOINs?
💡A: INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, SELF JOIN, and NATURAL JOIN.
❓Q: How does an INNER JOIN differ from a LEFT JOIN?
💡A: INNER JOIN returns only matching rows; LEFT JOIN returns all rows from the left table plus matched rows from the right.
❓Q: Is a JOIN faster than a subquery in SQL?
💡A: Generally, JOINs are faster when properly indexed, but subqueries can be better for specific filtering or aggregation.
❓Q: What is the difference between FULL JOIN and FULL OUTER JOIN?
💡A: They are the same; both return all rows from both tables with NULLs where no match exists.
❓Q: Can you join more than two tables in SQL?
💡A: Yes, by chaining multiple JOIN clauses with appropriate ON conditions.
❓Q: What is a SELF JOIN and when should it be used?
💡A: A SELF JOIN joins a table to itself, useful for hierarchical data like customer referral relationships or employee-manager relationships.
❓Q: How do NULL values affect JOIN results?
💡A: In OUTER JOINs, unmatched rows show NULLs in columns from the other table; INNER JOIN excludes unmatched rows.
❓Q: What are the best practices for optimizing JOIN queries?
💡A: Use indexes on join columns, avoid SELECT *, specify join conditions explicitly, and analyze query plans.
This content is provided as is, and is not maintained on an ongoing basis. It may be out of date with current Snowflake instances
