Should I avoid loops in connections?

Question

I remember being taught to never create a loop when connecting tables in sql. In fact, using Business Objects, he even tells me if there are cycles in the circuit that I defined in the Universe.
I tried searching the Internet for this statement, but I could not find the link.
Why is this dangerous?

Edit: maybe I was too succinto.

My question was not about a loop designed as "FOR LOOP" or similar. I talked about something like this WHERE clause in a SELECT statement:

WHERE TABLE1.foo = TABLE2.foo AND TABLE2.bar = TABLE3.bar AND TABLE3.baz = TABLE1.baz 

if you draw a relation, you will see a “loop” in the connection. Is it dangerous in terms of correctness and / or performance?
Thanks to everyone.

Edit 2: added an example.

I just came up with an example, maybe this is not the best, but I think it will help to understand.

 ------------ ----------------- ---------------------- - DELIVERY - - DELIVERY_DATE - - DELIVERY_DETAILS - ------------ ----------------- ---------------------- - id - <--- - id - <----- date_id - - company - |----- delivery_id - - product - - year - - date - - quantity - - number - ----------------- - datetime_of_event - - customer - ---------------------- - ---------- 1 <-----> N 1 <----> N 

• In the DELIVERY table, each delivery appears only once.
• At DELIVERY_TABLE, we have a list of each date on which delivery was processed. Thus, delivery can be prepared in a few days.
• In the last table, we describe each delivery in detail. So, in this table we track every event related to the preparation of the delivery.

So, the capacities are 1: N for each pair of tables.

The connection is very simple:

 DELIVERY.id = DELIVERY_DATE.delivery_id AND DELIVERY_DATE.id = DELIVERY_DETAILS.date_id 

Now suppose I want to join another table where I have other information for delivery on a specific date. Let him determine it:

 ------------ - EMPLOYEE - ------------ - company - - year - - number - - date - - employee - ------------ 

Now the connection should be:

 DELIVERY.id = DELIVERY_DATE.delivery_id AND EMPLOYEE.company = DELIVERY.company AND EMPLOYEE.year = DELIVERY.year AND EMPLOYEE.number = DELIVERY.number AND EMPLOYEE.date = DELIVERY_DATE.date 

To summarize, I conclude that EMPLOYEE will join both DELIVERY and DELIVERY_DATE, having a loop in the join.
Should I rewrite it this way?

 EMPLOYEE.company = DELIVERY.company AND EMPLOYEE.year = DELIVERY.year AND EMPLOYEE.number = DELIVERY.number AND EMPLOYEE.date IN (SELECT date FROM DELIVERY_DATE d WHERE d.delivery_id = DELIVERY.id) 

Edit 3: finally found the link

As usual, when you refuse to search for a link, you will find it.
So this article explains everything. This is related to business objects, but the content is shared.
Thank you all for your time.

Answer 1

EDIT: This update shows that this is a problem specific to the BO constructor, where the table is used several times, but BO automatically merges join clauses, which then incorrectly (or rather, inadvertently) limit the result set. This question actually has nothing to do with loops, in fact, it is really about using entities in more than one context in a single query. I will leave my initial answer below, although this does not really apply to the OP problem.

Disclaimer: This is the answer to the disclaimer because it is both an answer and a question. This should probably be a comment, but you cannot comment if you do not ask / answer questions, but since I sincerely want to help, I will do it as soon as I can, even if it is not. So judge me.

The short answer is no, you should not avoid loops (or "loops") in connections.

In more detail, the queries are designed to declare the correct logical condition (s) for obtaining the data you are looking for. The same logical condition can often be established in different ways, so it sometimes makes sense to ask whether one method is preferable to another. This becomes especially interesting when performance is important. But first of all, the query should return the correct set of results. How this is achieved depends on the basic design. And you really have to focus on that.

In your example, why does the role of EMPLOYEE play in relation to DELIVERY tables? Why are you joining these columns? What would mean that EMPLOYEE has the same “date” as delivery? I understand that this is a far-fetched example, but the point I'm trying to make is that if the join creates a cycle on the chart or not, it completely (well, basically) depends on what specific result sets the logical meaning.

And due to the JOIN syntax, using JOIN ... ON clauses is preferable to WHERE clauses, since it separates what you need to do to combine the objects in the data filtering operations.

Answer 2

Ok, your question is simple. You should not use where clauses to perform joins. You should use the on clause. Your connections can be expressed as:

 from Table1 join Table2 on Table1.foo = Table2.foo join Table3 on Table2.bar = Table3.bar and Table1.baz = Table3.baz 

Whether this is suitable or not depends on your data structures. Sometimes it is. You do not have to worry about this.

By the way, I would not call it "loops", which are very related to "for loops" in programming and nested loops in SQL. You can refer to this as loops in join conditions.

Wmax., As for the new join syntax. This is not a “simple” matter of taste. The "," parameter in from means cross-connect. In most cases, this is a very expensive operation. It’s much better to understand what you want to achieve:

 FROM A cross join B 

much clearer about their intentions than:

 FROM A, B 

Secondly, if you leave "," you still have valid syntax:

 FROM AB 

However, this means something completely different (assigning an alias B to table A).

The third reason is the most important reason. The old syntax has no way to express left outer join , right outer join and full outer join .

So, in order to write clearer queries, to avoid errors and gain access to more functional functions, you must learn the new syntax.