Joins in oracle

Question

If I write simply this query -

 SELECT * FROM AA,BB WHERE AA.ID = BB.ID

then what kind of join it will be. If we apply any kind of join then we need to specify like inner join, outer join, cross join. Then what kind of join it is?

And what's the actual difference between Inner Join and Cross join?

That is archaic syntax for an inner join. You should be using `JOIN`/`ON`, not asking about history. — Gordon Linoff, Jun 06 '19 at 19:51
[Inner join vs Where](https://stackoverflow.com/questions/121631/inner-join-vs-where), [INNER JOIN ON vs WHERE](https://stackoverflow.com/questions/1018822/inner-join-on-vs-where-clause), [Explicit vs implicit SQL joins](https://stackoverflow.com/questions/44917/explicit-vs-implicit-sql-joins), [SQL JOIN: is there a difference between USING, ON or WHERE?](https://stackoverflow.com/questions/5654278/sql-join-is-there-a-difference-between-using-on-or-where) — Parfait, Jun 06 '19 at 20:01

score 1 · Answer 1 · answered Jun 06 '19 at 20:00

Your query represents inner join. For example, on Scott's sample schema, it'll join rows from EMP and DEPT tables on DEPTNO (which is a common column):

SQL> select count(*)
  2  from emp e inner join dept d on e.deptno = d.deptno;

  COUNT(*)
----------
        14

SQL>

(you can omit inner keyword).

You asked what is a difference between inner and cross join; cross join represents a Cartesian product, which means that the result will be pairs of all rows from the first table with all rows from the second table:

SQL> select count(*)
  2  from emp e cross join dept d;

  COUNT(*)
----------
        56

SQL>

Using "your" old syntax, that's a join without WHERE clause:

SQL> select count(*)
  2  from emp e, dept d;

  COUNT(*)
----------
        56

SQL>

Outer join will take rows that don't have a "pair" in another table. In Scott's schema, it is department 40 in DEPT table as there are no employees who work there:

SQL> select count(*)
  2  from emp e right outer join dept d on e.deptno = d.deptno;

  COUNT(*)
----------
        15

SQL>

The old Oracle outer join operator ((+)) is something you might still see somewhere:

SQL> select count(*)
  2  from emp e, dept d
  3  where e.deptno (+) = d.deptno;

  COUNT(*)
----------
        15

SQL>

Basically, you should switch to modern joins. More about the subject in documentation about joins, here: https://docs.oracle.com/cd/B28359_01/server.111/b28286/queries006.htm#SQLRF52331 (11g version; find the one related to your database version, although there shouldn't be anything revolutionary in more recent versions).

The old vs. new points are valid, however a serious Oracle developer would be well served to understand both. Many applications still use the old syntax and many developers still write the older syntax. The Oracle database still uses the old outer join syntax quite a bit internally, and it shows up in the filter list of EXPLAIN PLAN sometimes where least expected. — Tad Harrison, Jun 07 '19 at 12:38
I wouldn't say "serious" but "old", @Tad. Zillion lines of my code are written using the "old" syntax (no, I'm not going to rewrite those). It's the way Oracle did that, and still does. I know that old Forms & Reports don't like ANSI 92 joins, so - that's how it is. A big drawback of the old outer join is its inability to outer join one table to more than just one another table, so - yes, I'd like to suggest anyone to *switch* to new syntax as it separates *joins* from *conditions*. Just my point of view. — Littlefoot, Jun 07 '19 at 12:57
@Littlefoot, That is my point of view as well--new code gets the new syntax. It allows more features, like full outer joins, and a developer who uses it can speak with a MySQL or SQL Server developer on better terms. — Tad Harrison, Jun 07 '19 at 13:09

xQbert · Answer 2 · 2019-06-07T12:19:44.860

You asked: If I write simply this query -

SELECT * FROM AA,BB WHERE AA.ID = BB.ID

then what kind of join it will be?

it's an INNER JOIN using pre ANSI-92 syntax.

If we apply any kind of join then we need to specify like inner join, outer join, cross join.

Only if using the current ANSI join syntax (which you should be in 2019).

Then what kind of join it is?

Really it's a cross join which gets records eliminated base on the where clause making it behave like an inner join. Eliminate the where clause and you get every record in AA to every record in BB.

And what's the actual difference between Inner Join and Cross join?>

An inner join only returns records that EXIST & RELATE IN both tables involved

A cross join relates EVERY record in one table to EVERY record in the other.

Take for example two Tables (A,B,C) (A,B,D)

an INNER JOIN would return the set {A.A,B.B}(2)

a cross join would return {A.A, A.B, A.D, B.A, B.B, B.D, C.A, C.B, C.D}(9)

score 0 · Answer 3 · answered Jun 06 '19 at 20:23

It looks like you don't quite understand the various types of joins. Overall, there are 5 different join types: INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER, CROSS. I'll show you an example of all 5 using these 2 tables Tables AA and BB will only have the field ID. Table AA will have 'A', and 'B' as values for ID. Table BB will have 'B', 'C', and 'D' as values for ID.

For an INNER JOIN, only those rows with matching values are included. So the INNER join of AA and BB would be: 'B','B'

For a LEFT OUTER JOIN, every row in the left table is returned, and for those rows in the right table without a matching value, NULL is returned. So we get: 'A',NULL 'B','B'

For a RIGHT OUTER JOIN, every row in the right table is returned, and for those rows in the left table without a matching value, NULL is returned. So we get: 'B','B' NULL,'C' NULL,'D'

For a FULL OUTER JOIN, every row in both tables are returned. And missing values in either table are NULL. So we get: 'A',NULL 'B','B' NULL,'C' NULL,'D'

And finally, for a CROSS JOIN, every row in each table is returned with every row in the other table. So we get: 'A','B' 'A','C' 'A','D' 'B','B' 'B','C' 'B','D'

For the example SQL statement you gave, you're returning an INNER JOIN. As for the difference between an INNER JOIN and a CROSS JOIN, the above example should have illustrated the differences, but for an INNER JOIN you're examining the minimum number of rows and for a CROSS JOIN, you're examining the maximum possible number of rows. In general if you examine the plan for a SQL query and find out that a CROSS JOIN is being used in the plan, more often than not, you have an error in your SQL since cross joins tend to be extremely processor and I/O intensive.

Joins in oracle

3 Answers3