Are INTERNAL WORK and ENTRY necessary?

Question

I understand that any connection can be made using the CROSS connection and the WHERE clause.

I did some experiments, and it seems that placing the equality predicate inside the where clause or as an internal join parameter gives the same result with the same performance.

In addition, the use of internal joins does not preserve any characters, since the join predicate must be specified.

I suppose the same is true for different kinds of external connections. Just indicate that the values ​​can be zero or non-zero.

Is it possible to simply switch only with cross connections?

Answer 1

No merging can be done using the CROSS join and the WHERE clause. Each connection cross, internal and external, has its logical meaning.

• Cross joining applies only to one facto Cartesian product.
• The internal connection uses two phases: the Cartesian product and the filter.
• The outer join applies to three phases: the Cartesian product, the filter, and the addition of outer rows.

An obscure aspect of queries containing an OUTER JOIN clause is whether to specify a Boolean expression in an ON file or in a WHERE filter. The main difference between the two is that ON is applied before adding external lines , and WHERE is applied after . Deleting a row from a saved table (specified as a left outer or right outer) using the ON filter is not final, because it will be added back; the exception of a line by the WHERE filter, on the contrary, is final.

This logical difference between ON and WHERE clauses exists only when using an external join. When you use an inner join, it doesn’t matter where you specify your logical expressions in the ON clause with the inner join table statement or in the Where clause with the cross join table statement.

Hope this helps !!!

Answer 2

Answer: performance cannot be affected, however code readability and clarity are difficult when you have experienced developers who look at cross joins when they are truly internals. In addition, it is a matter of personal preference.

Answer 3

You are halfway in your question. You said you tested and compared JOIN and Dekart. And the result that you got is that they are equal, however a year ago I did the same test in the MySQL database, and JOIN was much faster. This is because the SQL standard specifies how the join operation should be performed and that the output of the Dekart product should be the same as INNER JOIN .

Therefore, my advice is to use the standard way to record requests for some reason:

• Queries that work well in a DBMS (database management system) may not work as they do on another.
• Previous versions of the same DBMS may not optimize the Dekart product, as well as the latest.
• And, probably, the most important. The structure of a query using JOIN much easier to read and understand what it does, so it’s easier to modify the query.

All in all, my advice is to use JOIN instead.