Joins

Introduction

Every query so far read from one single table. This closing chapter covers combining two related tables into one result — starting with what happens when you don't tell SQL how they're related, and what changes once you do.

Concept

Cartesian Product — combining two tables by pairing every row of the first with every row of the second, with no matching condition at all. A 4-row table joined this way with a 3-row table produces 4 × 3 = 12 rows, almost all of them meaningless.
Equi-Join — combining two tables using an equality condition on a shared column, keeping only the row-pairs that actually match — exactly the meaningful subset of the full Cartesian Product.

Worked Example

SELECT * FROM STUDENT, RESULT;

SELECT STUDENT.Name, RESULT.Marks
FROM STUDENT, RESULT
WHERE STUDENT.RollNo = RESULT.RollNo;

The first query — naming two tables in FROM with no WHERE — produces the full Cartesian Product: every STUDENT row paired with every RESULT row, regardless of whether they actually belong together at all. The second query adds WHERE STUDENT.RollNo = RESULT.RollNo, keeping only the row-pairs where the RollNo genuinely matches between both tables — an Equi-Join, returning exactly one meaningful combined row per real student.

Warning: Forgetting the WHERE condition when combining two tables silently produces a Cartesian Product instead of the intended join — technically valid SQL, but a very common, easily-missed mistake that returns far too many, mostly meaningless rows.

Explanation

An Equi-Join is really a Cartesian Product with a filter applied — the same WHERE-based filtering idea from every earlier chapter, here used specifically to discard every mismatched pair the full Cartesian Product would otherwise include. This is precisely the same operation pd.merge() performed automatically, back in the Pandas unit — a join is just that same idea, written directly in SQL.

Summary

A Cartesian Product pairs every row of one table with every row of another, with no condition — almost always far too many rows to be useful. An Equi-Join adds an equality condition on a shared column (WHERE table1.col = table2.col), keeping only genuinely matching pairs — the SQL equivalent of pd.merge().

Classroom Toolkit

System Update: SQL Engine Ready.
Action: Run this block to create TWO tables: STUDENT and STREAM. We will try to connect them.

/* ==================================================
   SYSTEM SETUP (Two Related Tables)
   Table 1: STUDENT (Holds Name and StreamID)
   Table 2: STREAM (Holds StreamID and StreamName)
   ================================================== */
DROP TABLE IF EXISTS STUDENT;
DROP TABLE IF EXISTS STREAM;

CREATE TABLE STREAM (Code INT, StreamName VARCHAR(20));
INSERT INTO STREAM VALUES (1, 'Science');
INSERT INTO STREAM VALUES (2, 'Commerce');
INSERT INTO STREAM VALUES (3, 'Humanities');

CREATE TABLE STUDENT (Name VARCHAR(20), Code INT);
INSERT INTO STUDENT VALUES ('Amit', 1);  -- Amit is in Science
INSERT INTO STUDENT VALUES ('Ravi', 2);  -- Ravi is in Commerce
INSERT INTO STUDENT VALUES ('Pooja', 1); -- Pooja is in Science

1. What happens if you join blindly?

If you try to select from two tables without a condition, SQL creates a Cartesian Product (also called a Cross Join).

It joins Every Row of Table A with Every Row of Table B.
Formula: If Table A has 3 rows and Table B has 3 rows, the result is 3 x 3 = 9 rows.
Is it useful? Rarely. It usually creates junk data (e.g., matching a Science student with a Humanities subject).

SELECT * FROM STUDENT, STREAM;

/* ==================================================
   TASK: The Mistake (Cartesian Product)
   Count the rows. 3 Students x 3 Streams = 9 Rows.
   ================================================== */
SELECT * FROM STUDENT, STREAM;

2. The Equi-Join (The Fix)

To get meaningful data, we must tell SQL how the tables are related. We do this using a WHERE clause that matches the common column.

This is called an Equi-Join because we use the Equal sign (=).


            SELECT Name, StreamName 

            FROM STUDENT, STREAM 

            WHERE STUDENT.Code = STREAM.Code;

Logic: "Only join rows where the Code in Student matches the Code in Stream."

/* ==================================================
   TASK: Correct Join
   We use the common column 'Code' to link them.
   ================================================== */
SELECT 
    STUDENT.Name, 
    STREAM.StreamName 
FROM STUDENT, STREAM 
WHERE STUDENT.Code = STREAM.Code;