3 ms·

There is a semantic difference between a `where` clause and an `on` condition. If you're intending to do a left|right|full outer join, filtering via the `on` co

There is a semantic difference between a `where` clause and an `on` condition. If you're intending to do a left|right|full outer join, filtering via the `on` condition versus the `where` clause can produce drastically different results, even if the predicates used are exactly the same.

tomnipotent 5y ago
Before SQL/92, you would exclusively use the where clause. Here's an example of a left join: SELECT a.pk FROM table1 a, table2 b WHERE a.pk *= b.fk I haven't tried this syntax in ages, but I imagine it's still supported.
- bruce343434 5y ago
  I just tried this in MariaDB 10.5.10 on my personal blog database and it doesn't work. `post` <- many to many -> `tag` With LEFT JOIN: SELECT posts.title, GROUP_CONCAT(tags.tag) AS tagarr FROM posts LEFT JOIN post_tags ON post_tags.post_id = posts.id LEFT JOIN tags ON tags.tag_id = post_tags.tag_id With WHERE: SELECT posts.title, GROUP_CONCAT(tags.tag) AS tagarr FROM posts, post_tags, tags WHERE posts.id *= post_tags.post_id AND tags.tag_id *= post_tags.tag_id It gives: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '= post_tags.post_id [...]
  - da_chicken 5y ago
    The trouble you're having there is that there is no standard syntax for ANSI 89 outer joins at all. All outer joins were RDBMS-specific syntax. Indeed, if I remember right, left outer comma joins are *= for Oracle and =* for DB2 (or was it =+). MySQL has never supported any other syntax for outer joins, but it does support ANSI 89 (comma) inner joins. The ANSI 89 syntax for inner joins is pretty ubiquitous, as it can be a little easier to write code generators that way.
- Twisell 5y ago
  It is and it was (is?) for a long the time mandatory join style for UPDATE statement. Reason being that it's used to declare and restrict which table should be updated in the UPDATE section as opposed to optional joined tables listed in the FROM section. PS: The *= is plateform specific however (have no clue which). Usually you just write a more SQL standard WHERE section.
- calpaterson 5y ago
  It is supported and still in common use in some circles. For reasons I don't fully understand this is called "oracle style".
- e12e 5y ago
  I guess the star is for outer join? Or maybe it's just vendor syntax. Interestingly the postgresql tutorial introduces joins without using the join keyword initially: https://www.postgresql.org/docs/13/tutorial-join.html https://www.postgresql.org/docs/13/tutorial-join.html See also join_type: https://www.postgresql.org/docs/13/sql-select.html https://www.postgresql.org/docs/13/sql-select.html
- paulddraper 5y ago
  PostgreSQL doesn't support this; IDK if it ever did.

tomnipotent 5y ago

Before SQL/92, you would exclusively use the where clause. Here's an example of a left join: SELECT a.pk FROM table1 a, table2 b WHERE a.pk *= b.fk I haven't tried this syntax in ages, but I imagine it's still supported.

bruce343434 5y ago
I just tried this in MariaDB 10.5.10 on my personal blog database and it doesn't work. `post` <- many to many -> `tag` With LEFT JOIN: SELECT posts.title, GROUP_CONCAT(tags.tag) AS tagarr FROM posts LEFT JOIN post_tags ON post_tags.post_id = posts.id LEFT JOIN tags ON tags.tag_id = post_tags.tag_id With WHERE: SELECT posts.title, GROUP_CONCAT(tags.tag) AS tagarr FROM posts, post_tags, tags WHERE posts.id *= post_tags.post_id AND tags.tag_id *= post_tags.tag_id It gives: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '= post_tags.post_id [...]
- da_chicken 5y ago
  The trouble you're having there is that there is no standard syntax for ANSI 89 outer joins at all. All outer joins were RDBMS-specific syntax. Indeed, if I remember right, left outer comma joins are *= for Oracle and =* for DB2 (or was it =+). MySQL has never supported any other syntax for outer joins, but it does support ANSI 89 (comma) inner joins. The ANSI 89 syntax for inner joins is pretty ubiquitous, as it can be a little easier to write code generators that way.
Twisell 5y ago
It is and it was (is?) for a long the time mandatory join style for UPDATE statement. Reason being that it's used to declare and restrict which table should be updated in the UPDATE section as opposed to optional joined tables listed in the FROM section. PS: The *= is plateform specific however (have no clue which). Usually you just write a more SQL standard WHERE section.
calpaterson 5y ago
It is supported and still in common use in some circles. For reasons I don't fully understand this is called "oracle style".
e12e 5y ago
I guess the star is for outer join? Or maybe it's just vendor syntax. Interestingly the postgresql tutorial introduces joins without using the join keyword initially: https://www.postgresql.org/docs/13/tutorial-join.html https://www.postgresql.org/docs/13/tutorial-join.html See also join_type: https://www.postgresql.org/docs/13/sql-select.html https://www.postgresql.org/docs/13/sql-select.html
paulddraper 5y ago
PostgreSQL doesn't support this; IDK if it ever did.