Gyanrush
Start freeOpen Gyanrush
Start freeOpen Gyanrush
  1. LPU MCA Semester 1
  2. CAP570
  3. Unit 4

CAP570 · Unit 4: Database joins

Concept and need of database joins

9 practice questions · in the ETE syllabus

Practise this topicPractise this topic

Key points

  • Joins combine rows of related tables at query time
  • JOIN ... ON states the matching condition
  • No condition gives a cross join of m x n rows
  • Repeated keys on both sides multiply the matches

A relational database normalises data into several tables so that facts are stored once, for example student holds students and dept holds departments. A join combines rows from two or more tables at query time, using a condition that matches columns, usually a foreign key to a primary key.

The explicit syntax is SELECT ... FROM a JOIN b ON a.col = b.col. The ON clause states which rows belong together. Without any condition the result is a cross join (Cartesian product): every row of one table pairs with every row of the other, so tables of 5 and 4 rows give 20 rows.

SituationResult size
Cross join of m and n rowsm x n rows
Equality join, each key unique on one sideAt most the rows of the other side
Key repeated on both sidesMatches multiply, for example 2 x 3 = 6

Join conditions are usually equalities, but any comparison such as BETWEEN can be used. A join can be followed by WHERE, GROUP BY and aggregates, and three or more tables are chained with further JOIN ... ON clauses. A join does not copy data; it only builds a temporary result.

Exam trap: forgetting the join condition silently produces a cross join with far more rows than expected.

Try two questions

Question 1 · Tier 2, Understand

A table of 5 rows is joined with a table of 4 rows using a cross join. How many rows does the result contain?

  1. 9
  2. 5
  3. 4
  4. 20
Show the answer

D. A cross join returns every pairing of rows, so 5 x 4 = 20.

Why not A: 9 is the sum of the row counts, but a cross join multiplies them.

Why not B: 5 is the size of the first table only.

Why not C: 4 is the size of the second table only.

Question 2 · Tier 1, Recall

Which keyword introduces the matching condition in explicit JOIN syntax?

  1. SET
  2. INTO
  3. LIKE
  4. ON
Show the answer

D. JOIN ... ON states which columns must match, for example ON s.dept = d.did.

Why not A: SET is used in UPDATE statements.

Why not B: INTO is used with INSERT and SELECT INTO.

Why not C: LIKE is a pattern-matching operator.