File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes SQL AntiPattern - A lot of Join Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of Java Interview Guide this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "SQL AntiPattern - A lot of Join" Watch "SQL AntiPattern - A lot of Join" New topic

SQL AntiPattern - A lot of Join

Mark Reyes
Ranch Hand

Joined: Jul 09, 2007
Posts: 426

I have been working on this notion for some time but I would like to ask you about this.

Most of the table structure that I am working does not come with naturally occurring key so we have to create a makeup key for this.

Tables are normalize but the end result is that the query has become complex because of the lots of JOINS.

Can I consider this as an antipattern? My thoughts is that, there should be some limit on the number of joins..


Sean Clark ---> I love this place!!!
Me ------> I definitely love this place!!!
Gian Franco
Ranch Hand

Joined: Dec 16, 2003
Posts: 977
I don't think a lot of joins are an antipattern. It is more
a result of the design of the database in combination
with the particular query someone asked for. So it might
be perfectly legitimate and necessary.

You could 'hide' the joins by creating one or more views
that will take care of the joins and make your original
query select from these views instead.


"Eppur si muove!"
Bill Karwin
Ranch Hand

Joined: Aug 02, 2010
Posts: 32
Hi Mark,

I wouldn't set a hard limit on the number of joins, or consider too many joins to be an antipattern. This would be like saying it's a Java antipattern that you have more than a certain number of classes. Sometimes you do have too many classes in one application, but the same number of classes in another application might be just what is needed.

In my experience, it's more common for developers to use fewer joins than they need, than for them to use too many joins. They read some blog that says "joins are slow" and so they try anything they can to avoid using joins -- even if by eliminating joins they have actually made the problem harder to solve or slower to run. See the example given by Razvan Popovici earlier this week:

One common place I see people use too many joins is when you use the Entity-Attribute-Value design and you try to query a result set from it on one row per entity, as if it were a proper table. You end up doing an extra join to your EAV table for each attribute. The EAV design is of course an antipattern.

Bill Karwin is the author of SQL Antipatterns: Avoiding the Pitfalls of Database Programming
I agree. Here's the link:
subject: SQL AntiPattern - A lot of Join
It's not a secret anymore!