When do I not use USING clause and just stick with ON in some cases?

Chinasa Omenukor 0
Viewed 15

So, I tried fixing this up it’s a self join (joining using the same table. In this case, the table name is populations and has a country_code and year record in it amongst others) exercise I came across;

SELECT p1.country_code,
       p1.size AS size2010,
       p2.size AS size2015
FROM populations AS p1
JOIN populations AS p2
ON p1.country_code = p2.country_code
AND p1.year = p2.year - 5;

I tried using USING(country_code) to replace that “ON” clause, but I got a syntax error… but it gave a result when I queried using the ON as seen in the above query. Would love to find out why

mysql sql postgresql
1 Answers

As using doesn't support calculations you need to split the conditions.

But as you don't need USING you should keep your code, it is only a short cut

SELECT p1.country_code,
       p1.size AS size2010,
       p2.size AS size2015
FROM populations AS p1
JOIN populations AS p2
USING(country_code)
WHERE p1.year = p2.year - 5;
nbk0
Related