CODEWITHSUNDEEP
sqljoin
St.Antario
St.Antario

Reputation: 27435

Data size of result with join, how to optimize?

Suppose I have a query of the form:

SELECT *
FROM tbl1
LEFT OUTER JOIN tbl2 on tbl1.id = tbl2.user_id
LEFT OUTER JOIN tbl3 on tbl3.submit_id = tbl2.user_id
.....................................................
LEFT OUTER JOIN tbl3123 on tbl3123.mega_id = tbl223.super_hyper_id;

And I need to compute data size of the result (i.e. row count). Can I remove all of that LEFT OUTER JOIN and write just the following query:

SELECT COUNT(*)
FROM tbl1

Is result I reveive correct?

Upvotes: 1

Views: 87

Answers (2)

HLGEM
HLGEM

Reputation: 96630

There is no way to generalize this without knowing the joins in advance becasue the count will vary depending on which tables are in the joins and what the where clauses are.

Suppose you have tablea and tableb

Tablea left joined to tableb is 118 rows, table a inner joined to tableb is 107 rows, tablea has 89 rows and tableb has 107 rows and 11 rows of tableA have no tableb record. This is because tablea is in a one to many relationship to table b and there is not alawys a record created in tableb when tablea records are created.

Or it is possible that Tablea left joined to tableb is 1,00,786 rows, table a inner joined to tableb is 987,123 rows when tablea has 89 rows (becasue inthis case the one-many is very many!). Or you could have a one to one realtionships where tablea has 89 records, tableb has 89 records and both joins have 89 records. Or table b coudl have orphaned reciords that don't exist in table A becasue you designed the datbase badly without referential integrity. Or table b could be the pranet table and tablea the child table.

There is simply no way to know the count until you add in the joins (and any where clauses).

Upvotes: 0

Ndech
Ndech

Reputation: 965

No it is not, because one line in your table tbl1 can match multiple records in any other tables.

This query :

SELECT COUNT(*)
FROM tbl1

only gives a lower bound, you can be sure that there will be at least that many records in the result.

Upvotes: 2

Related Questions