HexLibraryHexLibrary
</> HexCodeSign inSign up
PythonJavaScriptTypeScriptCC++JavaPHPGoRustRubyBashSQLLuaPerlCOBOLPascalDelphiFortranDartSwiftC#JuliaGroovyRElixirHaskellKotlinScalaClassic ASPClipperMS-DOS
LESSONS
Introduction to SQL (SELECT)Tables (CREATE & INSERT)Filtering (WHERE)Sorting & LimitingAggregate FunctionsUpdate & DeleteJoins (JOIN)
REFERENCE
📖 Overview
🔎 Querying (SELECT)
🗃️ Creating a Table
✏️ Inserting & Modifying Data
🧲 Filtering (WHERE)
🔢 Sorting & Limiting
📊 Aggregate Functions
🔗 Joins (JOIN)
SQL›Joins (JOIN)

Joins (JOIN)

Combining two tables on a common column and querying them together.

Simple enough for a 5-year-old to understand

INNER JOIN

Connects related tables (e.g., person + order) using a common key.

Example code
1
CREATE TABLE person(id INTEGER, name TEXT);
2
CREATE TABLE orders(person_id INTEGER, item TEXT);
3
INSERT INTO person VALUES (1, 'Alex');
4
INSERT INTO orders VALUES (1, 'Pencil');
5
SELECT person.name, orders.item FROM person
Two tables...
Output / InputTwo tables...
6
JOIN orders ON person.id = orders.person_id;
...merge by common key: Alex|Pencil
Output / Input...merge by common key: Alex|Pencil
Output / Input
💡 Hint: You relate tables using a common column (key) with JOIN ... ON.

About this topic

The core idea of relational databases is not to repeat data: customer information sits in one table, orders in another, and the orders table points to the customer via an ID number. JOIN is the way to combine these separate tables at query time to produce a single result table.

The two most used types are INNER JOIN and LEFT JOIN, and the difference between them completely changes the result. INNER JOIN only brings matching rows from both sides. LEFT JOIN brings all rows from the left table; if there is no match on the right, those columns come as NULL.

This distinction corresponds to a practical question: do you want "customers who have orders", or "all customers, with their orders if any"? The first is INNER, the second is LEFT JOIN. The wrong choice silently drops records from your report.

What you will learn

  • Joining two tables with INNER JOIN
  • Bringing even unmatched rows with LEFT JOIN
  • Writing the ON condition correctly
  • Keeping the query readable with table aliases
  • Chaining more than three tables
  • Grouping together with JOIN

When to use?

JOIN is required in almost every meaningful report in a relational database: listing orders with customer names, showing products with their categories, calculating users' comment counts.

"Find what is missing" questions, such as finding customers who have never placed an order, are answered with a LEFT JOIN followed by a NULL check; this is a very common pattern.

Common mistakes

Wrong

SELECT * FROM customers, orders;

Writing two tables side by side without a join condition produces a cross join: every customer is matched with every order. 1000 customers and 1000 orders yield one million rows and the query stresses the server.

Correct

SELECT * FROM customers c
JOIN orders o ON o.customer_id = c.id;

Wrong

SELECT c.name, COUNT(o.id) FROM customers c
JOIN orders o ON o.customer_id = c.id
GROUP BY c.name;

Because INNER JOIN is used, customers with no orders drop out of the result entirely; "customers with zero orders" never appear in the report. You don't get an error message, the report is silently incomplete.

Correct

SELECT c.name, COUNT(o.id) FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.name;

Wrong

SELECT c.name, o.total FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.total > 100;

The WHERE clause filters out the NULL rows brought by LEFT JOIN, and the query effectively turns into an INNER JOIN. If you want to preserve the LEFT JOIN behavior, you must write the condition inside ON.

Correct

SELECT c.name, o.total FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.id AND o.total > 100;

Frequently asked questions

What is the difference between INNER JOIN and LEFT JOIN?

INNER JOIN only brings matching rows from both sides. LEFT JOIN brings all rows from the left table and leaves those columns as NULL if there is no match on the right. If you want "all customers, with their orders if any", you need LEFT JOIN.

What is the difference between ON and WHERE?

ON determines how tables match and is applied DURING the join. WHERE filters the result AFTER the join is COMPLETED. In INNER JOIN, both give the same result, but in LEFT JOIN, a WHERE clause ruins the purpose of the join by eliminating NULL rows.

How many tables can I join?

There is no technical limit, JOIN statements can be chained. However, each extra table slows down the query and makes it harder to read; after five or six tables, it is usually better to break down the query or create a view. Having indexes on the joined columns is critical for performance.

Summary

JOIN combines related data kept in separate tables at query time; INNER brings only matches, LEFT brings the entire left table. The most insidious mistake is writing a WHERE clause on a LEFT JOIN result and turning it into an INNER JOIN without realizing it.

HexLibraryHexLibrary

Learn programming more clearly. Every command is explained line by line with colors, reinforced with runnable exercises, test your code instantly — all in one place, free.

Learn

  • Home
  • Languages & Lessons
  • HexCode

Company

  • About
  • Contact

Legal

  • Privacy Policy
  • Terms of Use

Other Projects

  • HexToolz
  • Free Service Manuals
© 2026 HexLibrary · The clearest way to learn code