I have three tables with a chain of two one-to-many relationships. There are two ways I can think of of fetching the data based on the primary key of the first table.
JOINeach table (or
LEFT JOINif there are nonexistent rows) in a single query (
SELECT * FROM t1, t2, t3 WHERE t1.id = 123 AND t2.t1_id = t1.id AND t3.t2_id = t2.id)
- Run three queries linking to the first table's primary key each time (
SELECT t3.* FROM t3, t2, t1 WHERE t3.t2_id = t2.id AND t2.t1_id = t1.id AND t1.id = 123). Keep the database connection open until the data has been received from all three tables
Using a single
JOIN you will return redundant data, and have to handle the special case when there are zero rows on the right side separately. Running three queries means the database engine will have to do very similar things three times, and could possibly increase latency. It is possible to say which one is "better"? Does it depend on the situation, e.g. the size of the data being retrieved, is the database on the same server/network as the calling code, etc.?