7 ms·

The Difference Between SQL’s JOIN .. ON Clause and the Where Clause

• jimmytucson 8y ago
  As soon as you filter on the table you’re joining with in the where clause it becomes an inner join. FROM foo LEFT JOIN bar ON ... /* Makes it an inner join: */ WHERE bar.baz ...
  • astine 8y ago
    Only if you're resting for a non null value. If you test for a null value, you'll be looking for all values in one table that don't have a match in the other table.
    • Svip 8y ago
      Indeed, a faster way to check if a table does _not_ have a comparable row is a LEFT JOIN with a WHERE NULL clause: SELECT t1.* FROM t1 LEFT JOIN t2 ON t2.id = t1.foreign_id WHERE t2.id IS NULL Is a lot faster than the naïve alternative: SELECT t1.* FROM t1 WHERE NOT EXISTS (SELECT * FROM t2 WHERE id = t1.foreign_id)
      • brokensegue 8y ago
        Unless the query is optimized?
        • Svip 8y ago
          For a simple query like this, then yes, it will be optimised. But once your queries get more complicated, the optimiser might not be able to make that deduction on its own.
• deleted 8y ago
  [deleted]
• sheeshkebab 8y ago
  Contrary to articles conclusion, there are databases (i.e. Oracle) where it doesn’t matter how you write joins, but oracle also has support for left/right/full outer joins in the where clause Left join ... is the same as leftcol=rightcol(+) (oracle has special syntax for this with that + sign). In summary, depending on database, it may not matter how you write these joins, but it’s best to stick to SQL standard using JOIN if you’d like cross database support.
  • irrational 8y ago
    I have noticed that many authors of SQL articles don't seem to be aware of Oracles quirks. For example, recently I read an article about NULLs in SQL. The author didn't specify any particular database, but wrote as if what he was saying applied to all relational databases. But Oracle treats empty strings and NULLs as the same thing, which I don't think the article's author was aware of.
    • RHSeeger 8y ago
      > treats empty strings and NULLs as the same thing Wow, that's ridiculous. They are very clearly two different things. For example "this has no value" and "we don't know the value of this".
      • paulddraper 8y ago
        More like "this value is empty text" and "this has no value/is unknown". I agree though. Makes as much sense as treating 0 and NULL as the same.
        • kijin 8y ago
          What does ANSI SQL say about the matter? If Oracle's behavior is non-standard, it makes sense for the aforementioned author to ignore it.
          • irrational 8y ago
• snidane 8y ago
  The syntax for joins is unnecessarily complex. The difference in behaviour is determined by the operator used for comparison between the two tables. If there existed modifiers for the operators to be sensitive to nulls, no need for special syntax would be needed. If we define /<op> to make the operator include nulls on the left side and <op>\ to include nulls on the right side, we can do joins of all kinds without the JOIN ON syntax. Example. inner join select * from a,b where a.id=b.id left join select * from a,b where a.id=\b.id right join select * from a,b where a.id/=b.id outer join select * from a,b where a.id/=\b.id
  • jontro 8y ago
    How would you solve a left join like this with that kind of syntax? LEFT JOIN ( inner join table2 ON table2.ID = table1.table2id and table2.othertype = 1234 ) on table1.outerid = outertable.Id
    • Svip 8y ago
      The subquery would be in the WHERE clause, and it would be called for each row, thus making it N times slower than the JOIN syntax. There is a reason why this style has been abandoned.
      • kijin 8y ago
        Modern RDBMS's are smart enough to figure out when the result of a subquery doesn't depend on rows outside of it. Then the subquery is run only once, and its result is treated as a temporary table to be JOINed.
        • Svip 8y ago
          For most simple queries, that is correct. But if the query gets complicated, and/or the tables involved have several indexes, where several might be useful, then it might make the wrong decision. I've written plenty of complicated queries, where I have to help the query optimiser along. Although, the most modern I've had this experience with is SQL Server 2014, but that definitely still needed guiding a few times.
• deleted 8y ago
  [deleted]