6 ms·

I really don't get the confusion around JOINs... Any JOIN is what the author is calling a "CROSS JOIN", with "ON" (which is optional) basically being a WHERE c

I really don't get the confusion around JOINs... Any JOIN is what the author is calling a "CROSS JOIN", with "ON" (which is optional) basically being a WHERE condition, and "LEFT", "RIGHT", "INNER", "OUTER" words only describing whether or not to drop a result if the left/right side of the join would be a NULL reference. And, of course, a "UNION JOIN" is not a join at all, because nothing is joined.

darksaints 5y ago
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.
da_chicken 5y ago
> I really don't get the confusion around JOINs Nearly all programmers get locked into thinking in terms of sequential, imperative programming, and the big differences are procedural vs OOP. They think that's always what programming looks like. They never learn to think in any other terms. They think functional programming is a weird outlier and looks like Lisp. It's not entirely their fault. This is all they're typically taught in school. They're taught they don't need to think any other way than how Java, C, or C# tells them they should. So they're not used to thinking in terms of sets. They're not used to declarative programming. Indeed, they'll often argue that anything other than general purpose, imperative programming isn't "real" programming. That's why Professor Brailsford from Computerphile got so much flack for saying that HTML was a programming language.
- bruce343434 5y ago
  To be fair, I wouldn't consider HTML (or Markdown) a programming language. It's in the name, [] Markup Languange
  - deleted 5y ago
    [deleted]
  - da_chicken 5y ago
    Yes, that's just what the YouTube comments insisted. I'm not really interested in the argument, but all you've said so far is "it's domain specific" or "it's not Turing complete", neither of which are actually requirements for programming languages as a whole. It's not particularly difficult to devise a sufficiently loose definition of "program" that HTML fits into, too. Or, rather, it's difficult to envision a definition that LaTeX fits into and HTML+CSS fits into and SQL fits into and LISP fits into and so on, but HTML does not without your argument simply restating "it's domain specific" or "it's not Turing complete". However, it's also not a particularly interesting discussion, either, because the only purpose behind such categorization is to assign value judgements like "a 'real' programming language can be used for systems programming".
    - 5y ago

darksaints 5y ago

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.

da_chicken 5y ago

> I really don't get the confusion around JOINs Nearly all programmers get locked into thinking in terms of sequential, imperative programming, and the big differences are procedural vs OOP. They think that's always what programming looks like. They never learn to think in any other terms. They think functional programming is a weird outlier and looks like Lisp. It's not entirely their fault. This is all they're typically taught in school. They're taught they don't need to think any other way than how Java, C, or C# tells them they should. So they're not used to thinking in terms of sets. They're not used to declarative programming. Indeed, they'll often argue that anything other than general purpose, imperative programming isn't "real" programming. That's why Professor Brailsford from Computerphile got so much flack for saying that HTML was a programming language.

bruce343434 5y ago
To be fair, I wouldn't consider HTML (or Markdown) a programming language. It's in the name, [] Markup Languange
- deleted 5y ago
  [deleted]
- da_chicken 5y ago
  Yes, that's just what the YouTube comments insisted. I'm not really interested in the argument, but all you've said so far is "it's domain specific" or "it's not Turing complete", neither of which are actually requirements for programming languages as a whole. It's not particularly difficult to devise a sufficiently loose definition of "program" that HTML fits into, too. Or, rather, it's difficult to envision a definition that LaTeX fits into and HTML+CSS fits into and SQL fits into and LISP fits into and so on, but HTML does not without your argument simply restating "it's domain specific" or "it's not Turing complete". However, it's also not a particularly interesting discussion, either, because the only purpose behind such categorization is to assign value judgements like "a 'real' programming language can be used for systems programming".
  - 5y ago