Dutch Bangla Bank Ltd. — Probationary Officer (Software) Written Test: 2018প্রশ্ন ও সমাধান

(i) LEFT OUTER JOIN: returns all rows from the left table plus matching rows from the right table; unmatched right-side columns are filled with NULL.

SELECT * FROM A LEFT JOIN B ON A.id = B.a_id;

(ii) RIGHT OUTER JOIN: the mirror of a left join — returns all rows from the right table plus matching left-table rows, with NULL where there is no match. A LEFT JOIN B ≡ B RIGHT JOIN A (columns reordered).

SELECT * FROM A RIGHT JOIN B ON A.id = B.a_id;

(iii) FULL OUTER JOIN: returns all rows from both tables — matched rows are combined, and unmatched rows from either side appear with NULLs for the other side's columns.

SELECT * FROM A FULL OUTER JOIN B ON A.id = B.a_id;

(iv) One-to-Many: one row in table A relates to many rows in table B, but each row in B relates to only one row in A (e.g. one Department has many Employees).

(v) Many-to-Many: rows in A can relate to many rows in B and vice versa (e.g. Student ↔ Course); implemented in a relational DB via a junction/associative table holding both foreign keys.

erDiagram
  DEPARTMENT ||--o{ EMPLOYEE : "one-to-many"
  STUDENT }o--o{ COURSE : "many-to-many via ENROLLMENT"

Exam takeaway: LEFT/RIGHT/FULL joins differ only in which side's unmatched rows are preserved with NULLs; many-to-many relationships always require a junction table since a relational column can only hold one foreign key value.