What is the difference between the results of the following two SQL queries?

Question

SELECT r.region_name as Region,
       COUNT(o.*) AS CanCount
FROM region AS r
INNER JOIN orders AS o ON o.region_id = r.region_id
WHERE r.region_id = 1

SELECT [Region] = r.region_name
     , [CanCount] = COUNT(o.*)
FROM region AS r
INNER JOIN orders AS o ON o.region_id = r.region_id
                      AND r.region_id = 1

The second one is invalid standard SQL, while the first one is valid standard SQL — , Jan 04 '18 at 22:12
The syntax (`COUNT(o.*)`) is rather non-standard, but the two do the same thing and should have the same execution plan. — Gordon Linoff, Jan 04 '18 at 22:28
@a_horse_with_no_name, could you clarify what makes the second one invalid in standard SQL? Thanks for your help! — SoundSpace, Jan 05 '18 at 00:45
The `[` and `]` are invalid in an identifier in standard SQL. And the column alias with the `=` sign is also invalid in the SQL standard — , Jan 05 '18 at 05:34

score 0 · Answer 1 · answered Jan 04 '18 at 22:15

The results would be the same.

In the second query you're stating that you only want results to join the two tables if 'region id' = 1. This would imply that only the 'region ID' field only joins the two tables if they have a matching output of 1.

In the first query you are requesting the results to only return values if the 'region id' is = 1. The join in the first query will bring back all of the 'region ID' field if they match and filter out for the value of 1 in the output.

What is the difference between the results of the following two SQL queries?

1 Answers1