Which one is better in SQL?

Question

Which one is better in SQL?

SELECT A.COL_A1, B.COL_B1 FROM TABLE1 A, TABLE2 B WHERE A.COL_A1 = B.COL_B1 

or

 SELECT B.COL_B1, A.COL_A1 FROM TABLE2 B, TABLE1 A WHERE B.COL_B1 = A.COL_A1 

more details .. http://publib.boulder.ibm.com/infocenter/iisclzos/v9r1/index.jsp?topic=/com.ibm.websphere.ii.federation.classic.tuning.doc/tuning/iiyfctqcjoin.html

Answer 1

None.

 SELECT A.COL_A1, B.COL_B1 FROM TABLE1 A INNER JOIN TABLE2 B ON A.COL_A1 = B.COL_B1 

If you ask about performance (you didn’t), check on your specific platform.

Answer 2

You should not make a difference, since it is likely that one of these operators will be converted to another inside the optimizer. But, as always, be sure to: run the tests with both, there may be slight differences depending on the database you are using.

Answer 3

I think this is not the case at all, you both name both tables anyway.

Answer 4

It depends on the database system we are talking about. In many RDBMSs there is some query optimization - since you create your request, it may not be the way the server is processing it ...

For example, there is a query optimizer that will develop an implementation plan; this is usually correct, but you can help him along the way .

Most of the other main databases have something similar ... so in your example, there is a chance that your two examples will be processed exactly the same.

Answer 5

SQL declarative , so you tell the optimizer what you want, not how to do it.

They are equal because they are based on mathematical theory and not on execution order

However, the hideer's answer is correct, as it is ANSI-92 using JOIN, not the older "filtered cartesian" you posted.

Why are you reading the WebSphere documentation about SQL Server? You really need to work hard not to find the SQL Server article.

Indexing (in the article) does not matter for the results in SQL Server, only for the performance and execution plan used. For SQL Server, indexing is considered separately from the JOIN / WHERE order (which does not matter, it is, of course, declarative). If this WebSphere changes its plan based on the JOIN order, then frankly, this is more shit than I thought (I have clients using my database from WebSphere ...)

Answer 6

As in the link you described, it depends on which table the unique index is set to (maybe, especially for IBM DB). Your answer is the help page you linked :)

reagards

Answer 7

I would suggest ...

 SELECT COL_A1 FROM TABLE1 A WHERE EXISTS (SELECT * FROM TABLE2 B WHERE A.COL_A1 = B.COL_B1); 

because I prefer subqueries to submissions.