Explain the difference between these two SQL Queries?

Question

First Query:

SELECT 
    d.id,
    d.brand_name,
    count(*) AS no_of_time_used
FROM content oc
LEFT JOIN drama d ON d.id = oc.drama_id
LEFT JOIN orders o ON o.id = oc.order_id
    AND o.fundraiser_id IS NOT NULL
GROUP BY drama_id
ORDER BY no_of_time_used DESC;

Second Query:

SELECT 
    d.id,
    d.brand_name,
    count(*) AS no_of_time_used
FROM content oc
LEFT JOIN drama d ON d.id = oc.drama_id
LEFT JOIN orders o ON o.id = oc.order_id
WHERE o.fundraiser_id IS NOT NULL
GROUP BY drama_id
ORDER BY no_of_time_used DESC;

There is only one difference in these queries that's AND vs WHERE. In First Query, I have used AND just after the second JOIN Statement whereas in the second query I have used WHERE in place of AND.

Edit: Both Queries are returning different result.

When you use `AND` you are adding a second condition to the join. When you use `WHERE` you are adding a condition to the entire query. Totally different things. Even if they return the same results, they are not equivalent. BTW why have you tagged 4 RDBMS's? — Dale K, Jan 04 '20 at 09:18
@SushantYelpale are you sure? The second query will remove all rows where `o.fundraiser_id` is null. The first won't remove the rows, because its a left join. — Dale K, Jan 04 '20 at 09:22
@DaleK I am using MYSQL and you are correct results are different in both cases. — Radhe Shyam sharma, Jan 04 '20 at 09:30
Would you mind providing a common sample input and separated output of both queries? I'm quite sure by doing this you will realise the difference. It being the second query presumably returns less rows than the first... — Laurent S., Jan 04 '20 at 09:30
@LaurentS. You are correct my friend, second query is returning less records as compared to first one. — Radhe Shyam sharma, Jan 04 '20 at 09:32
The stated duplicate IS NOT a duplicate of this question. For instance, the OP is clear that the two versions return different results. — Gordon Linoff, Jan 04 '20 at 12:51
@GordonLinoff this question is a duplicate of https://stackoverflow.com/questions/354070/sql-join-where-clause-vs-on-clause because both questions ask what the difference is if a condition is within the where clause vs if it is in the on clause. — Shadow, Jan 04 '20 at 15:06
@Shadow . . . No. This question is about a specific filtering condition and how that affects the results. It is has nothing to do with putting `JOIN` conditions in a `WHERE` clause. — Gordon Linoff, Jan 04 '20 at 17:49
@GordonLinoff Any condition in the on clause is a join condition. This question is as you wrote, a specific example of the generic question about putting conditions in the on vs where clauses. — Shadow, Jan 04 '20 at 19:47

score 1 · Accepted Answer · answered Jan 04 '20 at 12:56

The question here is the condition on o.fundraiser_id IS NOT NULL.

In your the query, the condition is in the ON clause:

FROM content oc LEFT JOIN
     drama d
     ON d.id = oc.drama_id LEFT JOIN
     orders o
     ON o.id = oc.order_id AND
        o.fundraiser_id IS NOT NULL

This is saying to keep all the rows in content regardless of whether there is a match in orders. That is, the number of rows does not decrease (relative to not using the LEFT JOIN orders).

However, this affects the columns from orders. Only rows with a non-NULL fundraiser_id are in the result set. If there are no such values, then for that order_id the columns are NULL.

The second form is the much more typical form:

FROM content oc LEFT JOIN
     drama d
     ON d.id = oc.drama_id LEFT JOIN
     orders o
     ON o.id = oc.order_id
WHERE o.fundraiser_id IS NOT NULL

The intention here does filter out rows. It will filter out rows where fundraiser_id is not NULL. A NULL value can appear in two ways:

The value could be NULL in orders.
The LEFT JOIN might find no matching order.

This version filters out these rows, so it changes the number of rows. At the very least, it turns the second LEFT JOIN into an INNER JOIN.

Explain the difference between these two SQL Queries?

1 Answers1