How to Perform JOIN in ANSI SQL 89 Syntax

join;sql-standard

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?

Best Answer

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

Related Question