2

In Oracle, I use

SELECT * FROM table1...JOIN...

where the dots represent either the type of join or the condition on which to be joined.

Is this ANSI 89 syntax? If not, then how would I perform a join if I were using ANSI 89?

4

You don't want to use SQL-89 syntax. SQL-89 is just an implicit CROSS JOIN (Cartesian product).

SELECT *            -- NEVER
FROM foo, bar;      -- NEVER EVER DO THIS

Is the same as the more modern

SELECT *
FROM foo
CROSS JOIN bar;

Adding the WHERE clause makes it the equivalent of an INNER JOIN ... ON

SELECT *                         -- DO NOT
FROM foo,bar                     -- DO THIS
WHERE foo.foo_id = bar.foo_id;   -- EVER

You want to use SQL-92 Syntax.

SELECT *
FROM foo
INNER JOIN bar
  ON foo.foo_id = bar.foo_id;   

Or even better (if the join column is the same and it's an equijoin =)

SELECT *
FROM foo
INNER JOIN bar
  USING (foo_id);
  • Note some people don't use INNER. That's fine. It's still SQL-92.

See also

  • "You want to use SQL-92" and I agree. This is actually for a homework assignment. – moonman239 Jul 14 '18 at 1:14
  • Just to be clear - the professor doesn't expect us to use ANSI 89, just know how to do certain things in it. – moonman239 Jul 14 '18 at 1:15
  • @moonman239 that professor is wasting your time and exposing you to bad practice. It's not more powerful. It's all around worse. It's like teaching you how to write a program with only global mutable variables. It's possible, but you won't get anything out of the experience. – Evan Carroll Jul 14 '18 at 1:17
  • 4
    Explicit join syntax ("SQL-92") is clearer and preferable almost all of the time. But comma-separated FROM list items ("SQL-89") are not evil. It's valid syntax and there are valid use cases. Don't forget that explicit joins bind stronger than commas. CROSS JOIN is not 100% equivalent to a comma. We've had this before: dba.stackexchange.com/a/167217/3684 – Erwin Brandstetter Jul 14 '18 at 1:28
  • 1
    @EvanCarroll, mastering global mutable variables may get you a job as a Cobol programmer. It won't be fun but you can potentially make a lot of money;-) – Lennart Jul 14 '18 at 14:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.