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
views:
138answers:
7
+1
A:
I guess it does not at all matter, you call both tables in either way.
ApoY2k
2010-07-09 11:34:11
+8
A:
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.
spender
2010-07-09 11:35:02
His code is an equivalent of your inner join, this will make no difference
Tobias P.
2010-07-09 11:42:50
I can't imagine juggling the order of the columns and join will either. This is better (in my mind) in terms of readability.
spender
2010-07-09 11:46:00
@Tobias P: all 3 are equivalent but this is *correct* since ANSI-92. SQL is declarative of course so you're telling the optimiser what you want and let it sort it out.
gbn
2010-07-09 11:51:14
Not only are you supplying optimisation hints but you are being explicit, which is far more readable (and maintainable) IMHO.
CJM
2010-07-09 12:10:25
+1
A:
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.
CJM
2010-07-09 11:35:24
+3
A:
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
DrColossos
2010-07-09 11:36:35
A:
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
ReaperXmac
2010-07-09 11:38:10
+1
A:
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...)
gbn
2010-07-09 11:54:43
A:
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.
Brian Hooper
2010-07-09 12:36:07
Possibly, but not probably. The query optimiser will rewrite the sub-query as a join if it thinks fit, and evaluate it as it stands if not. It saves you worrying about what kind of join to use (see spender's comment to original question).
Brian Hooper
2010-07-09 13:35:25
True. I was perhaps lead astray by the example problem, in which none was used.
Brian Hooper
2010-07-10 08:30:00