Subjects and lessons to explore.

Law

Databases & SQL · Foundations

JOIN

9 min read 5 practice questions
8 key termsSourcesReport an error
Want it in plain words first? Jump to Eli explains — the same idea, no jargon.
On this page 9 sections
  1. In 30 seconds
  2. Why this matters
  3. The college version
  4. Eli explains
  5. Worked example
  6. Key takeaway
  7. Quick check
  8. Study tools
  9. Sources & references

In 30 seconds

A JOIN combines rows from two tables based on a matching condition. The working definition from W3Schools: the is used to combine rows from two or more tables based on a related column between them. Joins exist because the data you want often lives in two tables: orders hold order details, customers hold customer names. The basic shape is SELECT columns FROM tableA JOIN tableB ON match. keeps only matching rows; keeps every row from the . Joins are where relational databases earn their name.

Why this matters

One table rarely holds everything you need. Orders say which customer placed them; customers say what each customer is called. A JOIN puts those two tables back together so one query can answer a question that spans both: list every order with the customer's name. Without joins, you would either copy customer names into every order or flip between tables by hand. Joins are the read feature that makes relational databases relational, and nearly every real reporting task depends on them. Learn JOIN and you move from browsing single tables to asking questions of a whole database.

The college version

The working definition

W3Schools' SQL tutorial gives the working definition: the JOIN clause is used to combine rows from two or more tables, based on a related column between them. PostgreSQL's documentation says the same thing in plainer terms: queries that access multiple tables at one time are called join queries, and they combine rows from one table with rows from a second table, with an expression specifying which rows are to be paired. Put the two together and the definition is short: a JOIN combines rows from two tables based on a matching condition. All the rest is detail around that one sentence — why the data was split up, what the query looks like, and what happens to rows that do not match.

Why joins exist: the data is split on purpose

Picture a bakery's database. A customers table keeps one row per customer, with columns like customer_id and name. An orders table keeps one row per order, with columns like order_id, customer_id, and total. Notice what the orders table does not store: the customer's name. It stores the customer's id instead, and the name lives in customers. The split is deliberate — repeating the name on every order would waste space and invite typos — though the full reasons belong to the relationships and normalization topics. The consequence is that a simple question, such as listing every order with the customer's name, needs both tables at once. Microsoft Learn puts it directly: joins retrieve data from multiple tables based on logical relationships between them, and they enable you to combine data from two or more tables into a single result set. The join exists to put split data back together for one query.

The basic shape of a join query

The shape has four parts. SELECT names the columns you want; FROM names the first table; JOIN names the second table; ON names the columns that must match. The bakery's question looks like this: SELECT orders.order_id, customers.name, orders.total FROM orders JOIN customers ON orders.customer_id = customers.customer_id; Each order row is compared with each customer row, and the keeps the pairs whose customer ids agree. PostgreSQL's tutorial describes exactly this process: the database compares a column of each row of one table with a column of all rows of the other, and selects the pairs where the values match. Writing the table name before each column, as in orders.customer_id, is good style in a , because it tells the database which table each column comes from.

INNER JOIN: only matching rows

The join type you will meet most often is INNER JOIN. W3Schools describes it in one line: (INNER) JOIN returns only rows that have matching values in both tables. The parentheses around INNER are significant — they mean the keyword is optional, so writing plain JOIN already gives you an inner join. In the bakery example, an order whose customer_id appears in customers gets a row in the result; an order whose customer id matches nothing is dropped, because there is no customer row to pair with it. SQLite's documentation shows the underlying mechanics: with an ON clause, the expression is evaluated for each row of the full pairing, and only the rows for which it is true are included. That filtering is the inner join in action.

LEFT JOIN: all rows from the left

LEFT JOIN changes exactly one thing: which rows are guaranteed to survive. W3Schools again in one line: LEFT (OUTER) JOIN returns all rows from the left table, and only the matched rows from the . The left table is the one named in FROM; the right table is the one named in JOIN. Every row from the left table appears in the result. When a right-table row matches, its columns fill in alongside; when nothing matches, the right-table columns come back empty. One-line contrast: INNER JOIN keeps only pairs that match; LEFT JOIN keeps every left-table row, matched or not.

The ON condition: how the tables connect

ON names the columns that must agree, and Microsoft Learn describes what those columns usually are: a typical join condition specifies a foreign key from one table and its associated key in the other table. In the bakery, orders.customer_id is the foreign key — a column in orders that points at customers — and customers.customer_id is the primary key it points to. The ON condition pairs them: orders.customer_id = customers.customer_id. What keys are, why they exist, and how they are declared are topics of their own; here it is enough to recognize the pattern: the column that points from one table to another is matched with the column it points at.

The honest framing: where relational databases earn their name

Relational databases store data in separate tables and connect them through related columns. The JOIN is the command that walks those connections, which is why Microsoft Learn calls joins fundamental to relational database operations. It is also the most powerful thing SQL can do: one short query pulls data from two or more tables into a single result set, and real queries routinely join three or more tables. That power is the honest reason joins matter — they are the step where a database stops being a pile of tables and starts answering the questions you actually have.

Eli, the EliExplains learning guide

Eli explains

The same idea, in plain words

Explain it like I’m 10

A JOIN reads two tables at once and stitches them together on a shared column. Say orders lists each order with a customer id, and customers lists each id with a name. The query SELECT orders.order_id, customers.name FROM orders JOIN customers ON orders.customer_id = customers.customer_id; walks the orders, finds the customer with the matching id for each one, and returns a row per order with the customer's name attached. INNER JOIN keeps only orders whose customer exists. LEFT JOIN keeps every order and leaves the name blank when no customer matches. You decide which guarantee you need, and the ON condition says which column holds the shared id.

Picture it like this

Picture two stacks of index cards. The orders stack holds one card per order, each showing an order number and a customer id. The customers stack holds one card per customer, each showing an id and a name. A JOIN is you holding an order card, reading its customer id, finding the customer card with that same id, and clipping the two together. Work through the orders stack and every clipped pair is your result. INNER JOIN means you clip a pair only when the customer card exists. LEFT JOIN means you lay every order card on the table and clip a customer card next to it when you can find one — and leave the spot empty when you cannot.

Where the picture stops working

The card trick suggests the database physically checks every possible pair of cards, which is how the result is defined but not how it is computed — real engines skip most pairs using indexes and smarter algorithms, a topic of its own. The analogy also joins two stacks at a time, while a single SQL query can join three or more tables. And a clipped pair of cards keeps both cards whole, whereas a real join result is a fresh set of rows with columns from both tables.

Worked example

Meadow Bakery keeps two tables. customers holds customer_id, name, and city; orders holds order_id, customer_id, and total. The owner wants every order with the customer's name, so she runs SELECT orders.order_id, customers.name, orders.total FROM orders JOIN customers ON orders.customer_id = customers.customer_id; The database pairs each order with the customer whose id matches. The result: order 1001 with Lena at $14, order 1002 with Marcus at $9, order 1003 with Lena at $22, and order 1004 with Priya at $31. She then suspects one customer has no orders yet, so she switches the tables around and runs FROM customers LEFT JOIN orders ON the same condition. Now every customer appears, and the order columns come back empty for the customer with nothing ordered. Same question, one keyword changed, and a different promise about which rows survive.

Key takeaway

JOIN combines rows from two tables on a matching condition: INNER JOIN keeps only the matches, LEFT JOIN keeps every left-table row, and the ON condition usually compares a foreign key with the primary key it points to — the SQL feature where relational databases earn their name.

Quick check

3 questions here, of 5 in this lesson’s practice set. Answers stay hidden until you check.

Question 1 of 3foundational

What does the SQL JOIN clause do?

Choose an answer, then check it.
Question 2 of 3intermediate

Harbor Books keeps customers (customer_id, name) and orders (order_id, customer_id, total). Which query lists each order with its customer's name?

Choose an answer, then check it.
Question 3 of 3advanced

A query uses LEFT JOIN, and an order's customer_id matches no row in customers. What happens to that order in the result?

Choose an answer, then check it.
Practice all 5

Keep learning

Ready to build on this? Continue to the next lesson.

Practice this lesson
Study tools & related lessonsYou’ll learn to · Common mistakes · Easily confused · Key vocabulary · Related

You’ll learn to

  • Define JOIN as the SQL clause that combines rows from two tables based on a matching condition, using the working definitions of W3Schools and PostgreSQL.
  • Explain why joins exist: related data is stored in separate tables, so answering a question often means reading two tables at once.
  • Identify the parts of the basic join shape: SELECT columns FROM tableA JOIN tableB ON matching columns.
  • Distinguish INNER JOIN, which keeps only matching rows, from LEFT JOIN, which keeps every row of the left table.
  • Recognize that the ON condition usually compares a foreign key with the primary key it points to, and that key mechanics are covered in their own topics.

Common mistakes

  • Forgetting the ON condition.

    A join without ON pairs every row of one table with every row of the other — SQLite calls the result the cartesian product — and the result balloons. Always name the matching columns with ON.

  • Joining on the wrong columns.

    The ON condition must compare the related columns, usually a foreign key with the primary key it points to. Matching order ids to customer ids pairs rows at random and produces nonsense.

  • Thinking LEFT JOIN keeps unmatched rows from both tables.

    It guarantees only the left table's rows. Rows from the right table that match nothing are still dropped; guaranteeing both sides is the job of a full join, beyond this lesson.

  • Expecting the join to change or copy the tables.

    A join returns a result set computed at query time. The tables themselves stay exactly as they were — joining is a read, like SELECT.

  • Leaving column names unqualified when both tables share one.

    If orders and customers both had a column named id, the database could not tell which one you meant. PostgreSQL's tutorial recommends qualifying every column with its table name in a join query.

Easily confused

INNER JOIN vs. LEFT JOIN

Inner keeps only the row pairs that satisfy the ON condition; left keeps every row from the left table and adds matches from the right, leaving unmatched right-side columns empty.

The ON condition vs. A WHERE filter

ON names the columns that must match to pair rows during the join; WHERE filters the rows that come back. The older pre-SQL-92 syntax wrote the matching condition in WHERE, and for inner joins the results are identical, which is why PostgreSQL recommends the explicit JOIN/ON form for clarity.

A join vs. A subquery

A join combines rows from two tables side by side in one result set; a subquery nests one query inside another and feeds its result to the outer query. Both combine data, and subqueries have their own topic.

Key vocabulary

JOIN clause
The SQL clause that combines rows from two tables by pairing rows whose values match a condition; written as JOIN table ON condition.
INNER JOIN
The default join type, written JOIN or INNER JOIN: it keeps only the row pairs that satisfy the ON condition and drops unmatched rows.
LEFT JOIN
A join type that keeps every row from the left table and adds matching rows from the right, leaving unmatched right-side columns empty.
ON condition
The part of a join query, introduced by the keyword ON, that names the columns which must match for two rows to be paired.
left table
The table named in the FROM clause of a join query; LEFT JOIN guarantees that every one of its rows appears in the result.
right table
The table named after the JOIN keyword in a join query; in a LEFT JOIN its rows appear only when they match a left-table row.
join query
A query that reads from two or more tables at once, pairing their rows on a condition; PostgreSQL uses the term for any such query.
cartesian product
Every possible pairing of rows from two tables; SQLite explains that a join with no condition produces it, and the ON condition filters it.

Sources & references

  1. SQL Joins — W3Schools
  2. PostgreSQL Documentation: 2.6. Joins Between Tables — The PostgreSQL Global Development Group
  3. SQLite Query Language: SELECT — SQLite Consortium
  4. Joins (SQL Server) — Microsoft Learn — Microsoft Learn

EliExplains lessons are original prose written from the open, credible references above. See Copyright & Licensing.

Researched 2026-08-21

Educational content only. It is not medical, legal or professional advice. Found an error? Tell us.