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?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this communityIn 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?
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);
INNER
. That's fine. It's still SQL-92.See also
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