Tutorial :Which one is better in SQL? [closed]



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 info.. http://publib.boulder.ibm.com/infocenter/iisclzos/v9r1/index.jsp?topic=/com.ibm.websphere.ii.federation.classic.tuning.doc/tuning/iiyfctqcjoin.html


Solution:1

Neither.

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

If you're asking about performance (you didn't) then test on your particular platform.


Solution:2

Shouldn't make any difference since it is likely that one of this statements will be converted to the other one internally by the optimizer. But as always to realy be sure: run tests with both, there might by slight differences depending on the database you are using


Solution:3

I guess it does not at all matter, you call both tables in either way.


Solution:4

It depends on the DB system we are talking about. In many RDBMS's, there is some sort of query optimisation - so how you construct your query may not be the way the server goes about processing it...

For example, there is the Query Optimizer which will work out an execution plan; usually it gets it right, but you can help it along the way.

Most other major DBs have something similar... so in your example, there is every likelihood that your two examples would be processed in precisely the same way.


Solution:5

SQL is declarative so you're telling the optimizer what you want, not how to do it.

They are equal because it's based on mathematical theory, not order of execution

However, spender's answer is correct since this is ANSI-92 using JOIN rather than the older "filtered cartesian" you posted.

Why are you reading WebSphere documentation about SQL Server? You'd really have to work hard to avoid finding a SQL Server article.

Indexing (in the article) makes no difference to the results in SQL Server, only to performance and the execution plan used. For SQL Server, indexing is considered separately from the the JOIN/WHERE order (which don't matter, it's declarative of course). If that WebSphere changes it's plan based on JOIN order then frankly it's more shit than I thought (I have clients using my DB from WebSphere...)


Solution:6

Like in the link you post described it depends in which table an unique Index is set (maybe especially for IBM DBs). Your Answer is the help page you linked :)

reagards


Solution:7

I'd 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 views.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »