Querying Many-to-Many Relationships with Joins
SQL does not support arrays as a native data type, making array-based many-to-many workarounds impossible.
The tempting shortcut
Suppose a course can have many students, and a student can enroll in many courses. A natural first idea is to place every student ID directly inside the course record. In a programming language, an array or list would be an obvious container. In SQL, however, this approach runs into a fundamental limit: SQL does not support arrays as a native data type, and each column holds a single, indivisible value.
The join-table representation
A many-to-many relationship is represented by a proper join table rather than by an array inside either entity table. The course table stores course records, the student table stores student records, and the join table stores the relationships between them. Each relationship occupies its own row, so a course with several students is connected to several join-table rows, and each of those rows points to a student.
The join table may look more structurally complex because it uses multiple rows, but those rows give the database separate relationship values that can be indexed and queried.
The comma-separated workaround
Finding a course for student 14
A course record has course ID si311 and a student_ids text value of 1,3,4,5,6,9,14. How can the database determine whether student 14 is enrolled?
Store the relationships: The multiple student IDs are placed into one text value rather than into separate relationship rows.
Receive the query: The database is asked to find courses containing student ID 14.
Inspect the course rows: The database cannot perform a simple indexed lookup into the list. It must scan course rows, obtain each student_ids string, and check whether the relevant substring appears inside it.
Return matching courses: A course is returned when the string-matching work finds the requested ID inside its stored text.
The workaround can store and display the relationships, but finding one student requires a full-table scan and substring matching rather than a simple indexed lookup.
The work hidden in every query
The problem is not that a text column cannot contain the IDs. A TEXT column can hold the comma-separated string, and the value can be inserted, retrieved, and displayed. The problem appears when the database must search for one ID within that string. It must scan the course table, extract the text value from each row, and perform substring matching. These string-based queries cannot use indexes, so the work grows linearly with the number of rows.
| Representation | Where relationships are stored | How a requested ID is found | Indexing behavior |
|---|---|---|---|
| Comma-separated string | One text value inside a course row | Scan rows and search inside strings | String-based queries cannot use indexes |
| Join table | Separate relationship rows | Query indexed foreign keys | Indexed foreign keys enable fast queries |
Why growth changes the result
As the number of courses grows, the comma-separated approach has more rows to scan. As the number of relationships grows, the stored strings also contain more IDs to inspect. Because the string query scales linearly with the number of rows and cannot use indexes, the design becomes less efficient as data volume increases.
In the source scenario, a registration system has 500,000 courses and 2 million students. Using a comma-separated student list, finding all courses for one student requires scanning all 500,000 course rows and performing a substring search on each one. The source contrasts this with a join table and proper indexes, where the same query returns much faster: approximately 10 to 50 milliseconds instead of approximately 5 to 10 seconds.
Mistakes that hide the cost
Treating a database column like a programming-language list
SQL does not support arrays as a native data type, and a column holds a single indivisible value.
Fix:
Represent each course-to-student relationship as a row in a proper join table.Assuming that a TEXT column solves the relationship problem
The value can be stored, but queries for one ID require scanning rows and performing substring searches that cannot use indexes.
Fix:
Store relationship values separately in a join table with indexed foreign keys.Evaluating the design only on a small dataset
The string approach scales linearly with the number of rows, so its query work increases as the data grows.
Fix:
Choose the representation based on the lookup work required at larger data volumes.Confusing fewer visible rows with less total work
Compressing relationships into one row forces expensive scanning and string matching during queries.
Fix:
Use separate relationship rows so indexed foreign keys can support the query.
Choose the queryable structure
A team proposes storing every student ID for each course in one comma-separated text column. Explain what the database must do to find all courses for student 14, why an index cannot make that string search behave like an indexed foreign-key lookup, and which structure should replace the text column.
Hints
- Start with the number of course rows the database must inspect.
- Identify the substring-matching operation performed inside each student_ids value.
- Compare that work with separate relationship rows and indexed foreign keys.
A strong answer should mention a full-table scan, substring matching, the inability to use indexes for the string-based query, and the indexed join table as the scalable alternative.
The scalable choice
- SQL does not support arrays as a native data type, so an array of foreign keys is not a valid relational solution.
- Comma-separated strings can store multiple IDs, but querying them requires full-table scans and substring searches.
- String-based relationship queries cannot use indexes and therefore degrade linearly as the number of rows grows.
- A proper join table stores relationships in separate rows, allowing indexed foreign keys to support fast, logarithmic-time queries.
- The join-table design may use more rows, but it keeps relationship data queryable as the database grows.
Key Takeaways
- SQL columns do not hold arrays as native values, so arrays cannot directly represent many-to-many relationships.
- Comma-separated foreign keys hide multiple relationships inside one text value and force scanning and substring matching during queries.
- Because string-based queries cannot use indexes, their work grows linearly with the number of rows.
- A join table with indexed foreign keys separates relationships into queryable rows and scales more effectively.