Outer Joins

   ---------------------------------------------------------------------------------

   -- Filename    :  outer_join_example01.sql

   ---------------------------------------------------------------------------------

   -- Programmer  :  Ramachandran AmmasaiGounder

   -- Date        :  21-Aug-2013

   -- Language    :  SQL

   -- Location    :  Practice

   -- Purpose     :  This Query is simple example for outer join using SQL

   ---------------------------------------------------------------------------------

   -- Description :

   --    Take a A and B tables for test.

   --    write a query for outer join

   --    Check the outputs

   --

   ---------------------------------------------------------------------------------

 

 

   ----------------------------------------------------------------------------------------------------

   -- Update History:

   -- Date       Updated By                          Description

   -- ---------  ---------------------------  ---------------------------------------------------------

   -- 21-Aug-13  Ramachandran AmmasaiGounder   First version.

   --

   ----------------------------------------------------------------------------------------------------

-- Drop tables

DROP TABLE A;

DROP TABLE B;

/

-- Create Tables

CREATE TABLE A

(

C NUMBER PRIMARY KEY,

C1 VARCHAR2(10),

C2 VARCHAR2(10)

);

/

CREATE TABLE B

(

C NUMBER PRIMARY KEY,

C1 VARCHAR2(10),

C2 VARCHAR2(10)

);

/

-- Insert values

INSERT INTO A

VALUES(1,'A', 'A');

INSERT INTO A

VALUES(2,'B', 'B');

INSERT INTO A

VALUES(3,'C', 'C');

INSERT INTO A

VALUES(4,'D', 'D');

INSERT INTO A

VALUES(5,'E', 'E');

/

INSERT INTO B

VALUES(2,'B', 'B');

INSERT INTO B

VALUES(4,'D', 'D');

COMMIT;

/

-- Older outer query

SELECT *

FROM A

FULL OUTER JOIN B

ON A.C = B.C;

/

-- Newer Outer Query

SELECT A.*

       ,B.*

FROM A

     ,B

WHERE A.C = B.C (+);

/