Hacker News Reader

Comment by darksaints | Hacker News Reader

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.

View on HN

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.

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 [...]

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.

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.

It is supported and still in common use in some circles. For reasons I don't fully understand this is called "oracle style".

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

PostgreSQL doesn't support this; IDK if it ever did.

Reply on news.ycombinator.com