0

I have a few tables in their third normal form and I need to do some cross table queries to get the information I need.

I looked at joins but it seems like it will create a new table. Is this the proper way to perform such queries? Or should I just do nested queries ? I guess it might make sense if I have to do these queries alot? I'm really not sure how well optimize these operations are. I'm using the sequelize ORM and I'm not sure I see any clear solution.

Clodoaldo Neto
  • 118,695
  • 26
  • 233
  • 260
user3791980
  • 435
  • 9
  • 18
  • 3
    A join does not "create" a new table. It, well, joins the tables. And yes, joins **are** the proper way to combine the information from several tables –  Oct 23 '16 at 12:49
  • Given that you are new to databases, I've heard good things about the book, Teach Yourself SQL in 10 Minutes. – Dan Bracuk Oct 23 '16 at 12:52
  • I must be doing it wrong then. I used the relationship api in sequelize and it created new tables. I shall recheck my code. Thanks for the clarification and I shall take a look at that book. – user3791980 Oct 23 '16 at 12:53
  • 4
    @user3791980 Then perhaps your question is about sequelize, and not about general SQL. You should post your code, etc. and ask question about that – nos Oct 23 '16 at 12:59
  • This might be on point: http://stackoverflow.com/a/20467208/562459 – Mike Sherrill 'Cat Recall' Oct 23 '16 at 15:45

1 Answers1

0

It seems to me you are asking about joins vs subqueries. These are to some extent different. But let's start with a couple of points.

  1. A join creates a new relvar, not a new table. A relvar is a variable standing in for the relation output by the join operation. It is transient (as opposed to a view which would be persistent).
  2. Joins and subqueries are not always perfect substitutes. Sometimes you will need both.
  3. Your query output is also a relvar.

The above being said, generally where possible I think joins are preferable. The major reason is that a SQL query that can be written using the structure below is far easier (as you master the language) to both understand and debug than most alternatives, and also subqueries in column lists necessarily perform badly:

SELECT [column_list]
  FROM [initial_table]
 [join list]
 WHERE [filters]
GROUP BY [grouping list]
HAVING [post-aggregation filters]
LIMIT [limit and offset]

If your query fits the above structure then you can usually expect that specific kinds of problems will occur in logic in specific parts of the query. On the other hand, with subqueries, you have to check these independently.

Chris Travers
  • 25,424
  • 6
  • 65
  • 182