How to improve on this simple join?

Question

As I'm told it's better, I've been trying to filter out unnecessary rows at the join clause, like so:

SELECT whatever
FROM table1
INNER JOIN table 2
ON table1.id = table2.id
AND table2.active = 'Y'

Rather than saying "WHERE table2.active = Y" later in the statement.

Is this truly better? Also, is it possible to then somehow filter out rows unnecessary in the first mentioned table, table1? Something like:

SELECT whatever
FROM table1
ON table1.good_stuff = Y
INNER JOIN table2
ON table1.id = table2.id
AND table2.active = Y

Even looks funny. Is there a benefit of making ON clauses more elaborate? Can the first mentioned table even have something like an ON clause? Is there ever a reason to just list the tables such as:

SELECT whatever
FROM table1, table2
WHERE table1.id = table2.id
AND table2.active= Y

Answer 1

Does your AND version actually work (never tried that syntax)? I doubt it will matter based on the way query optimizers work. Basically, the conditional bubbles up to the "top" so that the database is only returning relevant data anyway.



I just took a database course from a guy w/ his own Oracle business. He said there's absolutely no difference between the old and new JOIN syntax internally. The only difference is the standard changed so that the JOIN version is preferred since it's more script friendly and also more intuitive.

Answer 2

It is more efficient to filter before joining if possible (whether the savings are significant is another story, especially with the newer DBMS versions - their query analyzers tend to do a decent job of optimization), but that's not the way I'd do it (and not even sure that's valid syntax). One way:



SELECT whatever

FROM table1 a JOIN

(SELECT table2 WHERE active= Y) b ON a.id = b.id

Answer 3

i does no longer ought to connect one yet, i does no longer mind vacationing. i might attempt my toughest to block out the historic persons and verify out all the warm women! C'mon who does no longer desire to aim this.