WHERE Versus AND

Question

I am trying to determine if I should use WHERE, or AND, or if it matters:

FROM table1

LEFT OUTER JOIN table3
ON --------

JOIN table2 
ON --------
AND -------
AND --------
AND year = ii_year

For the last line I can also use the following: WHERE year = ii_year and I get the same results. Which should I use? Thank you I actually do have a LEFT OUTER JOIN

Read http://stackoverflow.com/questions/354070/sql-join-where-clause-vs-on-clause All answers are subset of this thread(including its outgoing links ;)). Hope it helps. — instanceOfObject, Mar 27 '12 at 19:34

score 3 · Answer 1 · answered Mar 27 '12 at 19:24

3

In query execution order JOIN executed before WHERE condition filtering, so join conditions are some kind of pre-filtering of the result set.

For INNER JOIN this does not make any difference, but for OUTER (LEFT/RIGHT) does.

answered Mar 27 '12 at 19:24

sll

61,540
22
104
156

score 1 · Answer 2 · answered Mar 27 '12 at 19:22

If I correctly understand your question, AND is the logical operator for when two conditions in a WHERE statement must return true to be valid. This is the correct syntax:

SELECT column
FROM table
JOIN othertable ON ...
WHERE (column > 10) AND (column2 < 1)

score 1 · Answer 3 · answered Mar 27 '12 at 19:22

You're asking if you should write queries like this:

SELECT
FROM table1, table2
WHERE table1.year = table2.year;

Versus:

SELECT
FROM table1 JOIN table2 ON table1.year = table2.year;

There is no difference, logically speaking. And most parsers / query engines will treat them as identical. The rule of thumb I use is this:

If the comparison condition is being used in order to relate two entities, place that in a JOIN clause
If the comparison is instead being used to filter the result set, place that in a WHERE clause

I find that those two rules keep the intent of the query more clear. In your specific example, I'd need more information to really decide which category it falls into.

That is absolutely *not* what the OP is asking. Both of the OP's query variants use ANSI joins. — ruakh, Mar 27 '12 at 19:28

score 1 · Accepted Answer · answered Mar 27 '12 at 19:23

1

(Assuming MySQL for this answer).

If it's an INNER JOIN, like on your example, it makes no difference. But if you use a LEFT OUTER JOIN and put the conditions in the WHERE clause, it will behave like an INNER JOIN.

answered Mar 27 '12 at 19:23

bfavaretto

71,580
16
111
150

I actually do have a LEFT OUTER JOIN directly under the FROM and then a couple INNER JOINS after. So are you saying that because there is a LEFT OUTER JOIN first, that anything in a following WHERE clause will no longer act as a LEFT OUTER JOIN? – doug Mar 27 '12 at 19:26
1

@doug: Something like `SELECT * FROM a LEFT OUTER JOIN b ON b.a_id = a.id WHERE b.type = 'A'` will essentially function as an `INNER JOIN`, because a row in the result-set can never satisfy the `b.type = 'A'` condition unless the join succeeded. But something like `SELECT * FROM a LEFT OUTER JOIN b ON b.a_id = a.id WHERE a.type = 'B'` is absolutely fine. – ruakh Mar 27 '12 at 19:31
Thanks @ruakh, nice examples! – bfavaretto Mar 27 '12 at 19:33

score 1 · Answer 5 · answered Mar 27 '12 at 19:24

1

Use WHERE, save the ON keyword for joining tables. It's by far more readable, and won't give you undesired result when using OUTER JOIN...

answered Mar 27 '12 at 19:24

gdoron

147,333
58
291
367

score 1 · Answer 6 · answered Mar 27 '12 at 19:25

Conceptually, the difference is that ON clauses indicate the conditions that relate the two tables (e.g. ON table2.table1id = table1.id AND table2.is_active = 'Y'), whereas WHERE clauses indicate conditions that determine which rows you actually want (e.g. WHERE table1.name LIKE 'Foo %' AND table1.type = 3).

But this difference, as you've observed, as no effect when you're using an INNER JOIN; it only affects the query results if you have a LEFT OUTER JOIN or whatnot (where a failed condition in the ON clause means that you omit data from table2, whereas a failed condition in the WHERE clause means that you filter out entire rows from your result-set).

So when you're using an INNER JOIN, you just have to decide which way seems like a more intuitive way to think about a certain condition. There's not always a single "right" answer.

score 0 · Answer 7 · answered Mar 27 '12 at 19:22

I personally would use a WHERE clause for the last one. Then you are using the ANDs for the fields that are joining the tables and the WHERE for the filtering.

SELECT *
FROM table1 t1
  JOIN table2 t2
    ON t1.field1 = t2.field1
   AND t1.field2 = t2.field2
   AND t1.field3 = t2.field3
WHERE year = ii_year

score 0 · Answer 8 · answered Mar 27 '12 at 19:29

At first glance they seem quite similar but they aren’t. Joining with multiple conditions can result in a different dataset than joining and filtering (where statement).

For example:

SELECT * FROM customer
JOIN order ON customer.id = order.customerid
WHERE customer.id = 10

Will return all orders for customer ID 10

SELECT * FROM customer
JOIN order ON customer.id = order.customerid AND order.id = 10

Will return ALL customers but only order details for customer 10 will be joined, the rest will be blank (depending on join method used)

If you’re using INNER JOIN then yes you can use either and the SQL optimiser will change the query for the best execution plan.

If you’re using OUTER JOIN then the returned dataset will contain all customers, every customer apart from id = 10 will have NULL entries

WHERE Versus AND

8 Answers8