OraFAQ Forum: SQL & PL/SQL » Difference between JOIN and inner join

Home » SQL & PL/SQL » SQL & PL/SQL » Difference between JOIN and inner join

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Difference between JOIN and inner join [message #7905]

Wed, 16 July 2003 10:57

shashi
Messages: 34
Registered: March 2001

Member

What is the difference between JOIN keywrod and the inner join??

Example -
Select * from a , b where a.id = b.id

&

Select * from a JOIN on b .......I am not sure how to use this exactly.

Thanks

Report message to a moderator

Re: Difference between JOIN and inner join [message #7915 is a reply to message #7905]

Wed, 16 July 2003 20:11

Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA

Senior Member

Prior to version 9i of Oracle, the only valid syntax for an inner join was:

Select * from a , b where a.id = b.id;

In Oracle 9i, you can still use the above or you can use:

Select * from a JOIN on b;

or you can use:

Select * from a INNER JOIN on b;

If you don't specify whether a join is an INNER JOIN or a LEFT OUTER JOIN or a RIGHT OUTER JOIN or a FULL OUTER JOIN, then INNER JOIN is the default. That is why INNER JOIN is the same as just JOIN.

They are all the same. The new 9i syntax is ANSI SQL compliant. Theoretically you could use the same syntax on anything that supports SQL, whether it is Oracle or SQL Server or whatever. The old syntax, prior to 9i, is specific to Oracle and doesn't work on other systems.

Report message to a moderator

Re: Difference between JOIN and inner join [message #7957 is a reply to message #7915]

Fri, 18 July 2003 11:52

shashi
Messages: 34
Registered: March 2001

Member

Thanks Barbara,
After reading your answer got another question.
what is a FULL OUTER JOIN.

Shashi

Report message to a moderator

Re: Difference between JOIN and inner join [message #7963 is a reply to message #7957]

Fri, 18 July 2003 15:01

Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA

Senior Member

A FULL OUTER JOIN is the combined results of a LEFT OUT JOIN and RIGHT OUTER JOIN. I have provided an example of LEFT OUTER JOIN, RIGHT OUTER JOIN, then FULL OUTER JOIN below. I have provided both the 9i ANSI SQL syntax and 8i syntax, which is still usable in 9i, but I have only run the 8i syntax, since I am using 8i. Also, the OUTER keyword is optional, so, for example, LEFT OUTER JOIN is the same as LEFT JOIN.

SQL> -- test data:
SQL> SELECT * FROM a
  2  /

        ID COL2                                                                 
---------- -------------------------                                            
         1 id value in both tables                                              
         2 id value in table a only                                             

SQL> SELECT * FROM b
  2  /

        ID COL2                                                                 
---------- -------------------------                                            
         1 id value in both tables                                              
         3 id value in table b only                                             

SQL> -- left outer join with 9i syntax:
SQL> -- SELECT a.id, a.col2, b.id, b.col2
SQL> -- FROM   a LEFT OUTER JOIN b ON a.id = b.id
SQL> -- /
SQL> -- left outer join with 8i syntax:
SQL> SELECT a.id, a.col2, b.id, b.col2
  2  FROM   a, b
  3  WHERE  a.id = b.id (+)
  4  /

        ID COL2                              ID COL2                            
---------- ------------------------- ---------- -------------------------       
         1 id value in both tables            1 id value in both tables         
         2 id value in table a only                                             

SQL> -- right outer join with 9i syntax:
SQL> -- SELECT a.id, a.col2, b.id, b.col2
SQL> -- FROM   a RIGHT OUTER JOIN b ON a.id = b.id
SQL> -- /
SQL> -- right outer join with 8i syntax:
SQL> SELECT a.id, a.col2, b.id, b.col2
  2  FROM   a, b
  3  WHERE  a.id (+) = b.id
  4  /

        ID COL2                              ID COL2                            
---------- ------------------------- ---------- -------------------------       
         1 id value in both tables            1 id value in both tables         
                                              3 id value in table b only        

SQL> -- full outer join with 9i syntax:
SQL> -- SELECT a.id, a.col2, b.id, b.col2
SQL> -- FROM   a FULL OUTER JOIN b ON a.id = b.id
SQL> -- /
SQL> -- full outer join with 8i syntax:
SQL> SELECT a.id, a.col2, b.id, b.col2
  2  FROM   a, b
  3  WHERE  a.id = b.id (+)
  4  UNION ALL
  5  SELECT a.id, a.col2, b.id, b.col2
  6  FROM   a, b
  7  WHERE  a.id (+) = b.id
  8  AND    a.id IS NULL
  9  /

        ID COL2                              ID COL2                            
---------- ------------------------- ---------- -------------------------       
         1 id value in both tables            1 id value in both tables         
         2 id value in table a only                                             
                                              3 id value in table b only

Report message to a moderator