---
title: "SQL JOIN - Complete Guide to Database Table Relationships | Dawiso"
description: "SQL JOIN combines rows from multiple tables using shared keys. Covers INNER, LEFT, RIGHT, FULL OUTER, and CROSS JOIN with examples and performance tips."
url: "https://www.dawiso.com/glossary/sql-join-complete-guide-to-database-table-relationships"
language: "en"
---

February 20, 2026

sql jointable relationshipsdatabase queriesrelational database

# SQL JOIN - Complete Guide to Database Table Relationships

SQL JOIN combines rows from two or more tables based on a related column. Relational databases split data across tables to avoid duplication: customers in one table, orders in another, products in a third. JOINs are how you reassemble that data for queries. Understanding which JOIN type to use determines whether your results include all records, only matches, or both.

TL;DR

**SQL JOIN** combines rows from multiple tables using a shared column (usually a foreign key). INNER JOIN returns only matching rows. LEFT JOIN returns all rows from the left table plus matches from the right. RIGHT JOIN does the reverse. FULL OUTER JOIN returns all rows from both tables, matched where possible. CROSS JOIN produces every possible combination. Choose the type based on whether missing matches should appear as NULLs or be excluded.

## How JOINs Work

A JOIN operates on two tables and a **condition** that defines how rows match. The condition appears in the ON clause and almost always compares a foreign key in one table to a primary key in another.

Consider two tables: `customers` with a `customer_id` primary key, and `orders` with a `customer_id` foreign key. When you write `JOIN orders ON customers.customer_id = orders.customer_id`, the database finds every pair of rows where the customer IDs match. Customer "Acme Corp" with `customer_id = 7` gets matched to every order row where `customer_id = 7`.

This is why **normalization creates the need for JOINs**. Instead of duplicating the customer's name, address, and email on every order row, a normalized database stores customer data once and uses the foreign key to link tables. JOINs reassemble the full picture at query time.

The ON clause is not limited to simple equality. You can join on multiple columns, use inequality conditions, or combine equality with range checks. But equality joins on foreign keys are the most common pattern, and with a suitable index they are usually the cheapest to execute.

## JOIN Types

### INNER JOIN

Returns only rows where the ON condition matches in **both** tables. If a customer has no orders, they do not appear. If an order has no matching customer, it does not appear.

> `SELECT c.customer_name, o.order_date, o.total
> FROM customers c
> INNER JOIN orders o ON c.customer_id = o.customer_id;`

### LEFT JOIN

Returns **all rows from the left table** and matching rows from the right. Where there is no match, right-side columns are NULL. This is the join you use when you want "all customers, even those without orders."

> `SELECT c.customer_name, o.order_date, o.total
> FROM customers c
> LEFT JOIN orders o ON c.customer_id = o.customer_id;`

### RIGHT JOIN

The mirror of LEFT JOIN. Returns all rows from the right table plus matches from the left. In practice, most developers rewrite RIGHT JOINs as LEFT JOINs by swapping the table order, since it reads more naturally.

> `SELECT c.customer_name, o.order_date, o.total
> FROM customers c
> RIGHT JOIN orders o ON c.customer_id = o.customer_id;`

### FULL OUTER JOIN

Returns all rows from both tables. Where there is no match on either side, the missing columns are NULL. Useful for data reconciliation: finding records that exist in one system but not another. PostgreSQL, SQL Server, Oracle, Snowflake and Databricks support it, and SQLite added it in [version 3.39.0](https://www.sqlite.org/releaselog/3_39_0.html). MySQL's [JOIN syntax](https://dev.mysql.com/doc/refman/8.4/en/join.html) has no FULL OUTER JOIN; the usual workaround is a LEFT JOIN combined with a RIGHT JOIN through UNION.

> `SELECT c.customer_name, o.order_id, o.total
> FROM customers c
> FULL OUTER JOIN orders o ON c.customer_id = o.customer_id;`

### CROSS JOIN

Produces a **Cartesian product**: every row from the first table paired with every row from the second. If table A has 100 rows and table B has 50, the result has 5,000 rows. Useful for generating combinations (e.g., all products crossed with all store locations), but dangerous on large tables.

> `SELECT p.product_name, s.store_name
> FROM products p
> CROSS JOIN stores s;`

### SELF JOIN

A table joined to itself using different aliases. The classic use case is an employee-manager hierarchy where `manager_id` references another row in the same `employees` table:

> `SELECT e.employee_name AS employee,
> m.employee_name AS manager
> FROM employees e
> INNER JOIN employees m ON e.manager_id = m.employee_id;`

Because this is an INNER JOIN, the employee at the top of the hierarchy (whose `manager_id` is NULL) drops out of the result. Use a LEFT JOIN to keep them with a NULL manager.

## Multi-Table JOINs

Real queries often chain three, four, or more tables together. An e-commerce order detail query is a textbook example: you need data from customers, orders, order items, and products:

> `SELECT c.customer_name,
> o.order_date,
> p.product_name,
> oi.quantity,
> oi.unit_price,
> (oi.quantity * oi.unit_price) AS line_total
> FROM customers c
> JOIN orders o ON c.customer_id = o.customer_id
> JOIN order_items oi ON o.order_id = oi.order_id
> JOIN products p ON oi.product_id = p.product_id
> WHERE o.order_date >= '2025-01-01'
> ORDER BY o.order_date DESC;`

**Table aliases** (c, o, oi, p) are technically optional unless the same table appears twice, but in multi-table queries they keep the code readable, and qualifying every column with an alias prevents ambiguous column errors. Choose short but meaningful abbreviations.

When queries join five or more tables, understanding which columns match across tables requires documentation. Dawiso's [data catalog](https://www.dawiso.com/glossary/data-catalog) documents tables and columns with business descriptions and, for sources whose connectors read it, [lineage](https://www.dawiso.com/glossary/data-lineage), so analysts can check what a column means before relying on it in a JOIN condition instead of guessing from column names.

## JOIN vs Subqueries

JOINs and subqueries can often solve the same problem. The choice depends on what you need in the result and how the optimizer handles each pattern.

**Use JOIN when** you need columns from both tables in the output, or when the optimizer can leverage indexes on both sides efficiently:

> `-- JOIN: get customer names alongside their order totals
> SELECT c.customer_name, SUM(o.total) AS lifetime_value
> FROM customers c
> JOIN orders o ON c.customer_id = o.customer_id
> GROUP BY c.customer_id, c.customer_name;`

Group by the key as well as the name: two different customers can share a name, and grouping by name alone would merge their totals.

**Use a subquery when** the logic is a filter (yes/no), not a data source. EXISTS is particularly efficient for semi-joins because the database can stop searching after finding the first match:

> `-- EXISTS: find customers who ordered in 2025 (no order columns needed)
> SELECT c.customer_name
> FROM customers c
> WHERE EXISTS (
> SELECT 1 FROM orders o
> WHERE o.customer_id = c.customer_id
> AND o.order_date >= '2025-01-01'
> );`

The opposite pattern, an **anti-join**, finds rows with no match: customers who never ordered. Write it as `NOT EXISTS` or as a LEFT JOIN filtered on `o.customer_id IS NULL`. Avoid `NOT IN` when the subquery column can contain NULL. In the illustrative case below, a single order with a NULL `customer_id` makes the NOT IN query return no rows at all, while NOT EXISTS returns the customers without orders:

> `-- Returns nothing if any orders.customer_id is NULL
> SELECT customer_name FROM customers
> WHERE customer_id NOT IN (SELECT customer_id FROM orders);
> -- Returns customers without orders, NULLs or not
> SELECT c.customer_name FROM customers c
> WHERE NOT EXISTS (
> SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
> );`

If there are no equal right-hand values and at least one right-hand row yields null, the result of the NOT IN construct will be null, not true.

\- PostgreSQL Documentation, [Subquery Expressions](https://www.postgresql.org/docs/current/functions-subquery.html)

## Conditional and Advanced JOINs

### Additional ON conditions

You can add conditions to the ON clause beyond simple key equality. This filters rows *during* the join, which behaves differently from filtering in WHERE for outer joins:

> `-- Only join to orders from 2025; unmatched customers still appear
> SELECT c.customer_name, o.order_date
> FROM customers c
> LEFT JOIN orders o
> ON c.customer_id = o.customer_id
> AND o.order_date >= '2025-01-01';`

If you moved `o.order_date >= '2025-01-01'` to the WHERE clause instead, it would filter out customers without 2025 orders entirely, because their `o.order_date` is NULL and NULL never passes a comparison. That turns your LEFT JOIN into an effective INNER JOIN.

### Self-join patterns

Beyond employee-manager hierarchies, self-joins find rows that relate within the same table. For example, finding products in the same category whose prices are within 10% of each other (checked in both directions, so the result does not depend on which product has the lower ID):

> `SELECT a.product_name AS product_a,
> b.product_name AS product_b,
> a.price AS price_a,
> b.price AS price_b
> FROM products a
> JOIN products b
> ON a.category = b.category
> AND a.product_id < b.product_id
> AND b.price BETWEEN a.price * 0.9 AND a.price * 1.1
> AND a.price BETWEEN b.price * 0.9 AND b.price * 1.1;`

### JOIN with a derived table

You can join to the result of a subquery, treating it as a temporary table. The date arithmetic below is PostgreSQL syntax; MySQL writes it as `INTERVAL 30 DAY` and SQL Server uses `DATEADD`:

> `SELECT c.customer_name, recent.order_count
> FROM customers c
> JOIN (
> SELECT customer_id, COUNT(*) AS order_count
> FROM orders
> WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
> GROUP BY customer_id
> ) recent ON c.customer_id = recent.customer_id;`

## Performance

JOIN performance depends heavily on indexes, table sizes, and the optimizer's strategy. These are the high-impact optimizations:

**Index the JOIN columns.** Foreign key columns used in ON clauses usually deserve an index. Primary keys are indexed automatically, but PostgreSQL [does not automatically create an index](https://www.postgresql.org/docs/current/ddl-constraints.html) on the referencing column of a foreign key, so you add it yourself. Without a usable index, the optimizer cannot run an index nested loop on that side and typically falls back to a hash join or to repeated scans, which is fine for small tables and expensive for large ones.

**Read the EXPLAIN output.** PostgreSQL's planner chooses between [three join strategies](https://www.postgresql.org/docs/current/planner-optimizer.html), shown as `Nested Loop`, `Hash Join` and `Merge Join`. Hash joins suit large unsorted inputs. Merge joins suit inputs that are already sorted on the join key (or can be read in order from an index). Nested loops are efficient when the outer input is small and the inner side has an index on the join column. SQL Server uses the same [three algorithms plus adaptive joins](https://learn.microsoft.com/en-us/sql/relational-databases/performance/joins), while MySQL uses nested-loop algorithms and [hash joins](https://dev.mysql.com/doc/refman/8.4/en/hash-joins.html) but no merge join. Note that `EXPLAIN ANALYZE` actually runs the query:

> `EXPLAIN ANALYZE
> SELECT c.customer_name, o.total
> FROM customers c
> JOIN orders o ON c.customer_id = o.customer_id
> WHERE o.order_date >= '2025-01-01';`

**Filter before joining.** [WHERE clauses](https://www.dawiso.com/glossary/sql-where-clause-complete-guide-to-data-filtering) that reduce the row count early give the optimizer less work. A query that filters orders to the last 30 days before joining to customers is cheaper than joining all orders and filtering afterward, though most optimizers push such filters down automatically.

**Avoid accidental Cartesian products.** A missing or incorrect ON condition turns a JOIN into a CROSS JOIN. In an illustrative case, if `customers` has 10,000 rows and `orders` has 500,000, the accidental cross product is 5 billion rows. Always verify row counts with `SELECT COUNT(*)` after writing a new multi-table query.

The nested loops join delivers good performance if the driving query returns a small result set. Otherwise, the optimizer might choose an entirely different join algorithm, like the hash join.

\- Markus Winand, [Use The Index, Luke: Nested Loops](https://use-the-index-luke.com/sql/join/nested-loops-join-n1-problem)

## Troubleshooting

### Duplicate rows

If your query returns more rows than expected, the most likely cause is a **one-to-many relationship** you did not account for. Joining customers to orders gives one result row per order, not per customer. If you only want customer-level data, aggregate with [GROUP BY](https://www.dawiso.com/glossary/sql-group-by-complete-guide-to-data-aggregation-and-analysis) or use DISTINCT, but understand *why* duplicates appear before masking them. The same fan-out silently inflates aggregates: joining orders to order items and then summing `o.total` counts each order total once per line item. Aggregate the many side first, in a derived table, before joining.

### Missing data

If rows are unexpectedly absent, check three things: (1) Is the JOIN type correct? INNER JOIN excludes non-matching rows. (2) Do NULL values in the join columns prevent matching? NULLs never equal anything, including other NULLs; as the [SQL Server documentation](https://learn.microsoft.com/en-us/sql/relational-databases/performance/joins) puts it, null values in joined columns do not match each other. (3) Are data types mismatched? Joining a `VARCHAR` column to an `INTEGER` column behaves differently per engine: PostgreSQL rejects the comparison with an error, while MySQL and SQL Server convert one side implicitly, which can produce surprising matches (for example on leading zeros) and can prevent index use.

### NULL handling in LEFT JOIN results

LEFT JOIN fills right-side columns with NULL when there is no match. Use COALESCE to provide defaults:

> `SELECT c.customer_name,
> COALESCE(SUM(o.total), 0) AS lifetime_value
> FROM customers c
> LEFT JOIN orders o ON c.customer_id = o.customer_id
> GROUP BY c.customer_id, c.customer_name;`

## Database-Specific Syntax

### USING and NATURAL JOIN

When both tables have a column with the same name, the standard USING shorthand is shorter than ON, and the shared column appears only once in `SELECT *` output. PostgreSQL, MySQL, Oracle and SQLite support it; SQL Server does not:

> `SELECT c.customer_name, o.order_date
> FROM customers c
> JOIN orders o USING (customer_id);`

NATURAL JOIN goes one step further and joins on *every* column name the two tables share, which MySQL documents as [equivalent to USING with all common columns](https://dev.mysql.com/doc/refman/8.4/en/join.html). The PostgreSQL documentation calls it [considerably more risky](https://www.postgresql.org/docs/current/queries-table-expressions.html) than USING: add an `updated_at` column to both tables and the join condition changes silently.

### PostgreSQL: LATERAL JOIN

LATERAL lets a subquery in the FROM clause [reference columns from preceding FROM items](https://www.postgresql.org/docs/current/queries-table-expressions.html), enabling "for each row, run this subquery" patterns. MySQL supports [lateral derived tables](https://dev.mysql.com/doc/refman/8.4/en/lateral-derived-tables.html) too, and Snowflake and Databricks offer LATERAL as well. It is useful for finding the N most recent orders per customer:

> `SELECT c.customer_name, recent.order_date, recent.total
> FROM customers c
> LEFT JOIN LATERAL (
> SELECT order_date, total
> FROM orders o
> WHERE o.customer_id = c.customer_id
> ORDER BY order_date DESC
> LIMIT 3
> ) recent ON true;`

### SQL Server: CROSS APPLY / OUTER APPLY

SQL Server's equivalent of LATERAL JOIN, documented in the [FROM clause reference](https://learn.microsoft.com/en-us/sql/t-sql/queries/from-transact-sql). CROSS APPLY works like INNER JOIN LATERAL and OUTER APPLY like LEFT JOIN LATERAL:

> `SELECT c.customer_name, recent.order_date, recent.total
> FROM customers c
> OUTER APPLY (
> SELECT TOP 3 order_date, total
> FROM orders o
> WHERE o.customer_id = c.customer_id
> ORDER BY order_date DESC
> ) recent;`

## Sources

- PostgreSQL - [Table Expressions (joined tables, USING, NATURAL, LATERAL)](https://www.postgresql.org/docs/current/queries-table-expressions.html)
- PostgreSQL - [Subquery Expressions (EXISTS, IN, NOT IN)](https://www.postgresql.org/docs/current/functions-subquery.html)
- PostgreSQL - [Planner/Optimizer (join strategies)](https://www.postgresql.org/docs/current/planner-optimizer.html)
- PostgreSQL - [Constraints (foreign keys and indexes)](https://www.postgresql.org/docs/current/ddl-constraints.html)
- MySQL - [JOIN Clause](https://dev.mysql.com/doc/refman/8.4/en/join.html)
- MySQL - [Hash Join Optimization](https://dev.mysql.com/doc/refman/8.4/en/hash-joins.html)
- MySQL - [Lateral Derived Tables](https://dev.mysql.com/doc/refman/8.4/en/lateral-derived-tables.html)
- Microsoft Learn - [Joins (SQL Server)](https://learn.microsoft.com/en-us/sql/relational-databases/performance/joins)
- Microsoft Learn - [FROM clause plus JOIN, APPLY, PIVOT](https://learn.microsoft.com/en-us/sql/t-sql/queries/from-transact-sql)
- SQLite - [Release 3.39.0 (RIGHT and FULL OUTER JOIN)](https://www.sqlite.org/releaselog/3_39_0.html)
- Snowflake - [LATERAL](https://docs.snowflake.com/en/sql-reference/constructs/join-lateral)
- Databricks - [JOIN](https://docs.databricks.com/aws/en/sql/language-manual/sql-ref-syntax-qry-select-join)
- Markus Winand - [Use The Index, Luke: Nested Loops](https://use-the-index-luke.com/sql/join/nested-loops-join-n1-problem)

[See it in action](https://www.dawiso.com/product/data-catalog)

Data & Analytics Catalog

Create a unified view of your data assets and gain insights faster with automated data discovery.

### Table of contents

How JOINs WorkJOIN TypesMulti-Table JOINsJOIN vs SubqueriesConditional and Advanced JOINsPerformanceTroubleshootingDatabase-Specific SyntaxSources

## Related Terms

### [SQL COUNT - Complete Guide to Counting Records in Database Queries](https://www.dawiso.com/glossary/sql-count-complete-guide-to-counting-records-in-database-queries)

Master SQL COUNT function for accurate database record counting and data analysis

Read more →

### [SQL SELECT - Complete Guide to Database Queries](https://www.dawiso.com/glossary/sql-select-complete-guide-to-database-queries)

Master SQL SELECT statement fundamentals for database querying and data retrieval

Read more →

### [Power BI Data Modeling - Complete Guide to Effective Data Architecture](https://www.dawiso.com/glossary/power-bi-data-modeling-complete-guide-to-effective-data-architecture)

Power BI data modeling guide: star schema design, relationship configuration, measures vs. calculated columns, performance optimization, and row-level security - with concrete DAX examples.

Read more →

### [SQL DELETE - Complete Guide to Removing Database Records](https://www.dawiso.com/glossary/sql-delete-complete-guide-to-removing-database-records)

Learn SQL DELETE statement for safe database record removal and data management

Read more →

### [SQL INSERT - Comprehensive Guide to Database Data Insertion](https://www.dawiso.com/glossary/sql-insert-comprehensive-guide-to-database-data-insertion)

Master SQL INSERT statement for efficient database data insertion and record creation

Read more →

### [SQL UPDATE - Complete Guide to Updating Database Records](https://www.dawiso.com/glossary/sql-update-complete-guide-to-updating-database-records)

Learn SQL UPDATE statement for modifying database records and data maintenance

Read more →
