Computer ScienceCBSE Class 12 · Structured Query Language (SQL)

Joins in SQL: Cartesian Product, Equi-Join and Natural Join (Class 12 Computer Science)

Updated 17 October 2026 · Fact-checked

A join combines rows of two tables. The Cartesian product pairs every row of the first table with every row of the second (cardinality = r1 x r2, degree = d1 + d2). An equi-join keeps only the pairs whose common column values are equal, and a natural join does the same but shows the common column once.

Understand Joins: Cartesian Product, Equi-Join and Natural Join

The examples use the EMPLOYEE table (degree 6, cardinality 6) and the DEPT table (degree 3, cardinality 4).

EMPLOYEE

EmpID	Name	Designation	City	Salary	DeptID
E1	Asha Rao	Manager	Delhi	61000	D1
E2	Ravi Kumar	Accountant	Mumbai	38000	D2
E3	Meena Iyer	Executive	Chennai	52000	D1
E4	Kiran Shah	Clerk	Pune	35000	D2
E5	Neha Singh	Analyst	Delhi	45000	D3
E6	Sunil Das	Developer	Kolkata	41000	D3

DEPT

DeptID	DName	Location
D1	Sales	Delhi
D2	Finance	Mumbai
D3	IT	Bengaluru
D4	HR	Delhi

Cartesian product. Naming two tables in FROM with no condition pairs every row of the first table with every row of the second.

SELECT * FROM EMPLOYEE, DEPT;

Cardinality = 6 x 4 = 24 rows.
Degree = 6 + 3 = 9 columns.

Most of those 24 rows are meaningless; a join adds a condition to keep the meaningful ones.

Equi-join. Keeps the pairs in which the values of the common column are equal.

SELECT EMPLOYEE.Name, DEPT.DName FROM EMPLOYEE, DEPT WHERE EMPLOYEE.DeptID = DEPT.DeptID;

Use table name or alias before the column when both tables have it: E.DeptID = D.DeptID after FROM EMPLOYEE E, DEPT D.
With SELECT * the common column appears twice (degree 9).
Other conditions combine with AND: ... AND DEPT.Location = 'Delhi'.
Result here has 6 rows. HR (D4) has no employee, so it does not appear.

Natural join. Joins on all columns with the same name and equal value, and shows the common column once.

SELECT Name, DName FROM EMPLOYEE NATURAL JOIN DEPT WHERE Salary > 45000;

Result: Asha Rao Sales, Meena Iyer Sales. With SELECT * the degree is 6 + 3 - 1 = 8 (MySQL lists the common column first). The natural join joins on all same-named columns, so two tables sharing a second column name would also be matched on it; EMPLOYEE and DEPT share only DeptID (City and Location have different names).

Joins with other clauses. Join conditions work with GROUP BY, HAVING and ORDER BY:

SELECT DName, COUNT(*) FROM EMPLOYEE E, DEPT D WHERE E.DeptID = D.DeptID GROUP BY DName;

Result: Finance 2, IT 2, Sales 2.

Joins: Cartesian Product, Equi-Join and Natural Join: key facts

Cartesian product: Cardinality = rows of table 1 x rows of table 2
Degree = columns of table 1 + columns of table 2
FROM table1, table2 with no condition; EMPLOYEE and DEPT: 24 rows, 9 columns.
Equi-join: FROM t1, t2 WHERE t1.col = t2.col
Common column appears twice when all columns are selected
Qualify the common column with table name or alias; Only pairs with equal values remain.
Natural join: t1 NATURAL JOIN t2
Joins on same-named columns, equal values
Common column appears once
Degree = d1 + d2 - number of common columns; EMPLOYEE NATURAL JOIN DEPT has degree 8.
Unmatched rows: Row with no match in the other table is dropped | HR (D4) has no employee, so it is absent from the join; Row count of the join is at most the product.

How to answer Joins: Cartesian Product, Equi-Join and Natural Join questions

Use this routine for any join question.

1Identify the two tables and the common column (same name and meaning).
2For a Cartesian product give cardinality r1 x r2 and degree d1 + d2.
3For an equi-join, write the condition table1.col = table2.col in WHERE, and qualify ambiguous columns.
4For a natural join, drop the repeated common column and the condition (it is automatic).
5Pair the rows by matching values, then apply any other WHERE condition and the column list.
6Write the result with headings; apply GROUP BY, HAVING and ORDER BY in their usual order.

Joins: Cartesian Product, Equi-Join and Natural Join in real life

The idea, met in an everyday situation. Answer the question in your own words, then compare with the model answer. The one-line hook is what to remember.

Employees with department names

Suppose the manager wants each employee listed with the name of the department, but the employee table stores only the department code.

Write the query and give the result for the Finance staff.

Try it first, then show the solutionSolution

Join EMPLOYEE and DEPT on DeptID.
Finance is D2: Ravi Kumar and Kiran Shah.

Answer: SELECT E.Name, D.DName FROM EMPLOYEE E, DEPT D WHERE E.DeptID = D.DeptID; the Finance rows are Ravi Kumar and Kiran Shah.

Memory hook: The code is the key that unlocks the name in the other table.

All possible pairs

Suppose a school has 6 teachers and 4 clubs and wants to list every possible teacher-club pair before deciding who coordinates what.

How many pairs are there?

Try it first, then show the solutionSolution

A Cartesian product pairs every teacher with every club.
6 x 4 = 24 pairs.

Answer: 24 pairs

Memory hook: Everyone meets everyone, like handshakes between two teams.

Tricks to remember Joins: Cartesian Product, Equi-Join and Natural Join

Short memory aids. Say each one aloud once, then use it on a question the same day.

Rows multiply, columns add
Cartesian product: rows r1 x r2, columns d1 + d2.
Use it when: Degree and cardinality.
Join = product + condition
An equi-join is a Cartesian product filtered by equal common columns.
Use it when: Understanding the relation.
Natural drops the twin
Natural join shows the common column once.
Use it when: Equi-join versus natural join.
Qualify the clash
A column in both tables needs a table name or alias before it.
Use it when: Avoiding ambiguity errors.

Joins: Cartesian Product, Equi-Join and Natural Join: solved examples

Ordered from board level to HOTS. Try each one before you open the solution.

Example 1: Cartesian product size · 1 mark · MCQBoard level

Table A has 5 rows and 3 columns; table B has 4 rows and 2 columns. What is the cardinality and degree of their Cartesian product?
(a) 9 rows, 5 columns (b) 20 rows, 5 columns (c) 20 rows, 6 columns (d) 9 rows, 6 columns

Try it first, then show the solutionSolution

Rows multiply: 5 x 4 = 20.
Columns add: 3 + 2 = 5.

Answer: (b) 20 rows, 5 columns

Example 2: Equi-join query · 2 marks · Short answerBoard level

Write a query to display the Name of each employee along with the name of the department DName.

Try it first, then show the solutionSolution

SELECT EMPLOYEE.Name, DEPT.DName FROM EMPLOYEE, DEPT WHERE EMPLOYEE.DeptID = DEPT.DeptID; (2 marks, one for the tables and one for the join condition)

Answer: SELECT EMPLOYEE.Name, DEPT.DName FROM EMPLOYEE, DEPT WHERE EMPLOYEE.DeptID = DEPT.DeptID;

Example 3: Equi-join with a condition · 3 marks · Short answerHarder board level

Write the output of SELECT EMPLOYEE.Name, DEPT.DName, DEPT.Location FROM EMPLOYEE, DEPT WHERE EMPLOYEE.DeptID = DEPT.DeptID AND DEPT.Location = 'Delhi';

Try it first, then show the solutionSolution

Departments in Delhi: D1 (Sales) and D4 (HR). (1 mark)
Employees of D1: Asha Rao and Meena Iyer; D4 has no employees. (1 mark)
Output: Asha Rao Sales Delhi; Meena Iyer Sales Delhi. (1 mark)

Answer: Asha Rao | Sales | Delhi
Meena Iyer | Sales | Delhi

Example 4: Natural join · 3 marks · Short answerHarder board level

Write the output of SELECT Name, DName FROM EMPLOYEE NATURAL JOIN DEPT WHERE Salary > 45000; and the degree of the result of SELECT * FROM EMPLOYEE NATURAL JOIN DEPT;

Try it first, then show the solutionSolution

The common column is DeptID. Employees with Salary > 45000: Asha Rao (D1) and Meena Iyer (D1). (1 mark)
Output: Asha Rao Sales; Meena Iyer Sales. (1 mark)
Degree of SELECT *: 6 + 3 - 1 = 8, because DeptID appears once. (1 mark)

Answer: Asha Rao | Sales
Meena Iyer | Sales; degree 8

Example 5: Case: counting and filtering · 4 marks · Case-basedHOTS / competency

Suppose the management uses EMPLOYEE (6 rows) and DEPT (4 rows).
(i) How many rows does SELECT * FROM EMPLOYEE, DEPT; return? (1 mark)
(ii) How many rows does the equi-join on DeptID return? (1 mark)
(iii) Write the output of SELECT DName, COUNT(*) FROM EMPLOYEE E, DEPT D WHERE E.DeptID = D.DeptID GROUP BY DName; (1 mark)
(iv) Explain why HR does not appear in (iii). (1 mark)

Try it first, then show the solutionSolution

(i) 6 x 4 = 24.
(ii) Each of the six employees matches exactly one department: 6 rows.
(iii) Finance 2, IT 2, Sales 2.
(iv) No employee has DeptID D4, so no row pairs with HR and it forms no group.

Answer: (i) 24 (ii) 6 (iii) Finance 2, IT 2, Sales 2 (iv) no employee in D4

Where students lose marks in Joins: Cartesian Product, Equi-Join and Natural Join

The slips examiners see most often, and what to write instead.

Mistake: Forgetting the join condition
Without it the query returns the Cartesian product (24 rows).
Wrong working
SELECT Name, DName FROM EMPLOYEE, DEPT;
Right working
SELECT Name, DName FROM EMPLOYEE, DEPT WHERE EMPLOYEE.DeptID = DEPT.DeptID;
Do this: Always connect the common columns.
Usually costs: 1 mark
Mistake: Ambiguous column name
DeptID exists in both tables; MySQL cannot choose.
Wrong working
SELECT DeptID FROM EMPLOYEE, DEPT WHERE ...
Right working
SELECT EMPLOYEE.DeptID FROM EMPLOYEE, DEPT WHERE ...
Do this: Prefix the column with a table name or alias.
Usually costs: 1 mark
Mistake: Wrong degree of the product
Degrees add; cardinalities multiply.
Wrong working
Degree 6 x 3 = 18, cardinality 6 + 4 = 10
Right working
Degree 6 + 3 = 9, cardinality 6 x 4 = 24
Do this: Say rows multiply, columns add.
Usually costs: 1 mark

Exam tips

Write the join condition with table names or aliases, for example E.DeptID = D.DeptID.
State the degree and cardinality of a product before you start drawing its rows.
In outputs, remember that a department with no employee does not appear in an equi-join.
A natural join has no WHERE condition for the join, but it can still have other conditions.
Check that every selected common column is qualified.

Practice questions on Joins: Cartesian Product, Equi-Join and Natural Join

Write each answer on paper first. Open the hint only if you are stuck, and the answer only when you have finished.

Q1 · 1 mark · MCQBoard level
Which of the following gives the Cartesian product of two tables?
(a) SELECT * FROM A, B; (b) SELECT * FROM A, B WHERE A.k = B.k; (c) SELECT * FROM A NATURAL JOIN B; (d) none of these
Hint
No condition at all.
Answer
(a) SELECT * FROM A, B;
Q2 · 1 mark · Very short answerBoard level
In a natural join, how many times does the common column appear in the result?
Hint
Think of the twin.
Answer
Once.
Q3 · 3 marks · Short answerHarder board level
Write the output of SELECT E.Name, E.City, D.Location FROM EMPLOYEE E, DEPT D WHERE E.DeptID = D.DeptID AND E.City = D.Location;
Hint
Match the department first, then compare the cities.
Answer
Asha Rao | Delhi | Delhi (D1 is in Delhi)
Ravi Kumar | Mumbai | Mumbai (D2 is in Mumbai). Others have city different from the department location.
Q4 · 1 mark · Assertion-reasonHarder board level
Assertion (A): SELECT * from an equi-join of EMPLOYEE and DEPT on DeptID shows DeptID twice.
Reason (R): An equi-join keeps both copies of the common column.
(a) Both A and R are true and R is the correct explanation of A. (b) Both A and R are true but R is not the correct explanation of A. (c) A is true but R is false. (d) A is false but R is true.
Hint
Compare with the natural join.
Answer
(a) Both are true and R explains A.
Q5 · 3 marks · Short answerHOTS / competency
Write a query to display each department name and its highest salary, only for departments with more than one employee, in alphabetical order of DName, and give the output.
Hint
Join, group, filter the group, sort.
Answer
SELECT DName, MAX(Salary) FROM EMPLOYEE E, DEPT D WHERE E.DeptID = D.DeptID GROUP BY DName HAVING COUNT(*) > 1 ORDER BY DName; Output: Finance 38000; IT 45000; Sales 61000.

Test your recall

Answer in your head or on paper first, then open the card. Pulling an answer out of memory makes it stick far better than re-reading.

1.How many rows does a Cartesian product have?Show answer
The product of the numbers of rows of the two tables.
2.What is the degree of a Cartesian product?Show answer
The sum of the degrees of the two tables.
3.What does an equi-join compare?Show answer
The values of the common columns, for equality.
4.How does a natural join differ from an equi-join?Show answer
It shows the common column only once and needs no join condition.
5.Why use aliases in joins?Show answer
To shorten table names and to avoid ambiguity.
6.What happens to a row with no match in the other table?Show answer
It does not appear in the join result.

Joins: Cartesian Product, Equi-Join and Natural Join: frequently asked questions

What is the Cartesian product in SQL?

It is the result of listing two tables in FROM with no condition: every row of the first table is paired with every row of the second.

What is the difference between equi-join and natural join?

Both keep the rows with equal values in the common column. An equi-join is written with a condition in WHERE and shows the common column twice; a natural join joins automatically on same-named columns and shows it once.

How do I find the degree of a join result?

Add the degrees of the two tables; for a natural join subtract the number of common columns that are shown once.

Why is my join giving too many rows?

The join condition is probably missing, so the query returns the Cartesian product.