0

I am doing an assignment for class. My SQL works and I get the expected return. However I was talking to a person and he said that you can speed up DB searches by using "ON" in the "FROM" clause. His explanation was that the "WHERE" clause eliminates data after the pull while the "FROM" clause eliminates data before the pull.

Here is my SQL that works

SELECT ProductID, ProductName
FROM Products 
WHERE Discontinued = False
ORDER BY ProductName;

This is what I have tried but it produces a syntax error in Access

SELECT ProductID, ProductName
FROM Products ON Discontinued = False
ORDER BY ProductName;

Discontinued is a checkbox. I do realize the for the small DBs we work with in class it is not a big deal. But I would like to have a better understanding of optimizing query's for production use.

Bob Fincheimer
  • 17,978
  • 1
  • 29
  • 54
user1793408
  • 113
  • 11
  • 5
    "His explanation was that the "WHERE" clause eliminates data after the pull while the "FROM" clause eliminates data before the pull." --- never listen to that guy any more, he has no idea what he is talking about (or you didn't get him correct). – zerkms Sep 11 '13 at 06:01
  • 2
    If you would like to have a better understanding of optimizing queries, start by don't listening to that guy. – ypercubeᵀᴹ Sep 11 '13 at 06:01
  • Apart from the fact that the recommendation is total nonsense, that is invalid SQL. Any other DBMS (at least Postgres, Oracle, SQL Server and DB2) will reject that. –  Sep 11 '13 at 06:08
  • 2
    The `FROM` clause (where any `JOIN` and `ON` appears) is only **logically** processed before the `WHERE` clause. All modern DBMS know that and that for INNER joins, whether a condition is at `ON` or `WHERE` is irrelevant. And have many optimizations so the actual physical processing can be totally different than the logical. See: [SQL JOIN: is there a difference between USING, ON or WHERE?](http://stackoverflow.com/questions/5654278/sql-join-is-there-a-difference-between-using-on-or-where/5654338#5654338) – ypercubeᵀᴹ Sep 11 '13 at 06:08
  • And as @a_horse_with_no_name says, you can only have `ON` attached to a `JOIN` clause. So, you need at least 2 tables (or the same table twice) to appear in the `FROM` clause to have `ON` and a valid query. – ypercubeᵀᴹ Sep 11 '13 at 06:10

1 Answers1

1

ON is used to specify the JOIN condition specifically.

So if you don't have a JOIN - you cannot use it.

Is there anything particularly wrong with WHERE?

zerkms
  • 249,484
  • 69
  • 436
  • 539
  • 1
    I'd also add that this talk about speeding up query with ON sounds very unprofessional to me – Roman Pekar Sep 11 '13 at 06:01
  • ON and WHERE should perform exactly the same. Unless they return different data (which is possible). But in that case, you don't really have a choice, as only one of the two will be correct. – Thilo Sep 11 '13 at 06:02
  • @Thilo: "ON and WHERE should perform exactly the same" --- you can only state that if you know mysql sources well. Do you? There *might be* different optimizations applied to them. – zerkms Sep 11 '13 at 06:03
  • 1
    I say "should". As in "my expectation of a decent query optimizer". – Thilo Sep 11 '13 at 06:03
  • 1
    @zerkms: But how is MySQL relevant here to begin with? The OP is using Access. – Andriy M Sep 12 '13 at 08:33
  • @Andriy M: oh, I need glasses – zerkms Sep 12 '13 at 08:39