CODEWITHSUNDEEP
sqljoin
Dan
Dan

Reputation: 7737

do database JOINs make many copies of data in left and right sides of join?

I'm curious: do databases return JOINs as the results of two (or more) SELECT statements with pointers from (segments of) rows in the left side to (segments of) rows in the right, or do they return result sets which may contain many copies of entries from the left and right sides?

You can imagine this being particularly inefficient for dense N:N relations, where N copies of a row on the left might be made to pair up with N matches on the right, and vice versa.

You could hide this optimization in client drivers and make it totally transparent to the database user.

EDIT To clarify, by "dense N:N relations," I mean something like (for a "Facebook clone") you have a "users" table and a "friends" table, and each user is friends with every other user. You would need two JOINs to pull all the pairs of friends out, but could pass the same information to the client as the result of three SELECT statements and let the client match up rows using pointers instead of actually duplicating each user 2(N-1) times (N-1 times on the left, N-1 times on the right).

Upvotes: 1

Views: 62

Answers (1)

David Aldridge
David Aldridge

Reputation: 52376

If you did request from a database a result set based on an n:n relationship then it would presumably be because this represents a real situation -- that many people buy products from many shops, for example. It's not inefficient to represent this real situation in such a way, because the result set is simply a representation of the truth.

In general an n:n relationship would need three relations to be joined -- database normalisation effectively forbids n:n relationships between two tables, for equijoins at any rate.

Upvotes: 1

Related Questions