--------------------------------------------------------------------------------------------------------------
-- TITLE           : TESTING ORACLE JOIN SYNTAX
-- CREATION DATE   : 09/07/2012
-- LAST MODIFIED   : 20/08/2012
-- AUTHOR          : S. ADRIANI
-- VERSION         : 1.0
-- DESCRIPTION     : DDL scripts helping testing of the Join Example tutorial.
--                 : To execute the Join Example tutorial on an Oracle Database just run
--                 : each single SQL instructions, one at a time, following the order
--                 : of this script.
--                 : Commented sections (e.g. section 7) are optionals, since they can be 
--                 : used to clean/remove the structures created by this script.
-- REFERENCE       : http://adriani.altervista.org/work/join.php
--------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------------
-- 1. Table creation
--------------------------------------------------------------------------------------------------------------

CREATE TABLE "TEST_JOIN_FAMILY" (
  "ID" NUMBER,
  "SURNAME" VARCHAR2(50 BYTE), 
  "TOWN" VARCHAR2(50 BYTE), 
  "COUNTRY" VARCHAR2(50 BYTE), 
  CONSTRAINT "TEST_JOIN_FAMILY_ID_PK" PRIMARY KEY ("ID")
) ;

CREATE TABLE "TEST_JOIN_PEOPLE" (
  "ID" NUMBER,
  "FIRSTNAME" VARCHAR2(50 BYTE) NOT NULL, 
  "SURNAME" VARCHAR2(50 BYTE), 
  "GENDER" VARCHAR2(1 BYTE), 
  "AGE" NUMBER, 
  "FAMILY_ID" NUMBER, 
  CONSTRAINT "TEST_JOIN_PEOPLE_ID_PK" PRIMARY KEY ("ID"),
  CONSTRAINT "TEST_JOIN_PEOPLE_FK1" FOREIGN KEY ("FAMILY_ID")
  REFERENCES "TEST_JOIN_FAMILY" ("ID") DISABLE --ENABLE	 
) ;

-- REMARK: in the last row of the TEST_JOIN_PEOPLE DDL we specified a DISABLED foreign key.
-- We did this for following reasons:
-- 1. It's always a good practice to define such foreign keys when working with relational data.
-- 2. To provide a good join example it's better to avoid such constraint.
-- Hence, by defining a disabled constraint we satisfied both requirements: we shown where and how
-- to specify a foreign key constraint (without enabling it) and we created a structure allowing
-- to provide a complete join example.

--------------------------------------------------------------------------------------------------------------
-- 2. Inserting data
--------------------------------------------------------------------------------------------------------------

INSERT INTO TEST_JOIN_FAMILY	(ID	, SURNAME	, TOWN			, COUNTRY	) 
VALUES 							( 1	, 'Smith'	, 'Charlotte'	, 'USA'		) ;
INSERT INTO TEST_JOIN_FAMILY	(ID	, SURNAME	, TOWN			, COUNTRY	) 
VALUES 							( 2	, 'Brown'	, 'London'		, 'UK'		) ;
INSERT INTO TEST_JOIN_FAMILY	(ID	, SURNAME	, TOWN			, COUNTRY	) 
VALUES 							( 3	, 'Adriani'	, 'Lecco'		, 'IT'		) ;
INSERT INTO TEST_JOIN_FAMILY	(ID	, SURNAME	, TOWN			, COUNTRY	) 
VALUES 							( 4	, 'Zanini'	, 'Montagnana'	, 'IT'		) ;
INSERT INTO TEST_JOIN_FAMILY	(ID	, SURNAME	, TOWN			, COUNTRY	) 
VALUES 							( 5	, 'Chinello', 'Padova'		, 'IT'		) ;

INSERT INTO TEST_JOIN_PEOPLE	(ID	, FIRSTNAME	, SURNAME	, GENDER	, AGE	, FAMILY_ID	) 
VALUES 							( 1	, 'John'	, 'Smith'	, 'M'		, 42	, 1			) ;
INSERT INTO TEST_JOIN_PEOPLE	(ID	, FIRSTNAME	, SURNAME	, GENDER	, AGE	, FAMILY_ID	) 
VALUES 							( 2	, 'Sasha'	, 'Smith'	, 'F'		, 35	, 1			) ;
INSERT INTO TEST_JOIN_PEOPLE	(ID	, FIRSTNAME	, SURNAME	, GENDER	, AGE	, FAMILY_ID ) 
VALUES 							( 3	, 'Kevin'	, 'Smith'	, 'M'		, 12	, 1			) ;
INSERT INTO TEST_JOIN_PEOPLE	(ID	, FIRSTNAME	, SURNAME	, GENDER	, AGE	, FAMILY_ID ) 
VALUES 							( 4	, 'Paul'	, 'Brown'	, 'M'		, 30	, 2			) ;
INSERT INTO TEST_JOIN_PEOPLE	(ID	, FIRSTNAME	, SURNAME	, GENDER	, AGE	, FAMILY_ID ) 
VALUES 							( 5	, 'Claire'	, 'Brown'	, 'F'		, 25	, 2			) ;
INSERT INTO TEST_JOIN_PEOPLE	(ID	, FIRSTNAME	, SURNAME	, GENDER	, AGE	, FAMILY_ID	) 
VALUES 							( 6	, 'Joseph'	, 'Brown'	, 'M'		, 3		, 2			) ;
INSERT INTO TEST_JOIN_PEOPLE	(ID	, FIRSTNAME	, SURNAME	, GENDER	, AGE	, FAMILY_ID	) 
VALUES 							( 7	, 'Stefano'	, 'Adriani'	, 'M'		, 43	, 3			) ;
INSERT INTO TEST_JOIN_PEOPLE	(ID	, FIRSTNAME	, SURNAME	, GENDER	, AGE	, FAMILY_ID	) 
VALUES 							( 8	, 'Stefania', 'Orsini'	, 'F'		, 38	, 3			) ;
INSERT INTO TEST_JOIN_PEOPLE	(ID	, FIRSTNAME	, SURNAME	, GENDER	, AGE	, FAMILY_ID	) 
VALUES 							( 9	, 'Giada'	, 'Adriani'	, 'F'		, 6		, 3			) ;
INSERT INTO TEST_JOIN_PEOPLE	(ID	, FIRSTNAME	, SURNAME	, GENDER	, AGE	, FAMILY_ID	) 
VALUES 							( 10, 'Giuliano', 'Zagreus'	, 'M'		, 70	, 10		) ;

--------------------------------------------------------------------------------------------------------------
-- 3. Validation queries
--------------------------------------------------------------------------------------------------------------

-- These queries SHOULD FAIL because they try to violate primary key constraint.
INSERT INTO TEST_JOIN_FAMILY	(ID	, SURNAME	, TOWN		, COUNTRY	) 	
VALUES 							( 1	, 'Foo'		, 'Foo'		, 'Foo'		) ;
INSERT INTO TEST_JOIN_PEOPLE	(ID	, FIRSTNAME	, SURNAME	, GENDER	, AGE	, FAMILY_ID) 
VALUES 							( 9	, 'Foo'		, 'Foo'		, 'X'		, 99	, 3			) ;

-- This query SHOULD NOT FAIL because we DISABLED the foreign-key constraint.
INSERT INTO TEST_JOIN_PEOPLE	(ID	, FIRSTNAME	, SURNAME	, GENDER	, AGE	, FAMILY_ID) 
VALUES 							( 11, 'Mork'	, 'Ork'		, 'X'		, 99	, 9			) ;

--------------------------------------------------------------------------------------------------------------
-- 4. Questing queries
--------------------------------------------------------------------------------------------------------------

SELECT * FROM TEST_JOIN_FAMILY ORDER BY ID ;
SELECT * FROM TEST_JOIN_PEOPLE ORDER BY ID ;

--------------------------------------------------------------------------------------------------------------
-- 5. Testing join queries
--------------------------------------------------------------------------------------------------------------

-- 5.1 Simple basic OUTER JOIN: return only exactly joined records
-- (i.e. those records having a matching ID in both tables)
SELECT * FROM TEST_JOIN_FAMILY family, TEST_JOIN_PEOPLE people
WHERE family.id = people.family_id 
ORDER BY family.ID ;

-- 5.2 LEFT OUTER JOIN: standard syntax.
-- This query will return all "joined" records and ALSO all records from 
-- the table on the LEFT (i.e. TEST_JOIN_FAMILY), even if they do not match
-- any record in the other table (i.e. TEST_JOIN_PEOPLE)
-- Hence "LEFT" depicts the table providing all records, even if not joined.
SELECT * FROM TEST_JOIN_FAMILY family LEFT OUTER JOIN TEST_JOIN_PEOPLE people
ON family.id = people.family_id
ORDER BY family.id ;

-- 5.3 LEFT OUTER JOIN: Oracle legacy syntax.
-- As above: the "(+)" symbol depicts the "optional table", provided as "surplus" (i.e. TEST_JOIN_PEOPLE)
-- This means that the "mandatory" records come from the other one (i.e. TEST_JOIN_FAMILY).
-- In the example below: the TEST_JOIN_FAMILY will have all records, even if not matching the other one.
-- This is why the syntax below correspond to a LEFT join!
SELECT * FROM TEST_JOIN_FAMILY Family, TEST_JOIN_PEOPLE People
WHERE family.id = people.family_id (+) 
ORDER BY family.id ;

-- 5.4 RIGHT OUTER JOIN: standard syntax.
-- As above, now the table with "all records" is the one on the right (i.e. TEST_JOIN_PEOPLE).
SELECT * FROM TEST_JOIN_FAMILY family RIGHT OUTER JOIN TEST_JOIN_PEOPLE people
ON family.id = people.family_id 
ORDER BY family.id ;

-- 5.5 RIGHT OUTER JOIN: Oracle legacy syntax.
-- As above, now the table with "all records" is the one without the "(+)" symbol (i.e. TEST_JOIN_PEOPLE).
SELECT * FROM TEST_JOIN_FAMILY Family, TEST_JOIN_PEOPLE People
WHERE family.id (+) = people.family_id 
ORDER BY family.id ;

-- CONCLUSIONS:
-- 1. Standard syntax: "LEFT" or "RIGHT" depicts the table providing all records, even if not matched.
-- 2. Oracle syntax: the symbol "(+)" depicts the "optional" table, having only the matched records.
-- 3. The reference defining "left" or "right" is the JOIN label in standard syntax, and the "(+)" symbol in Oracle syntax.
--
-- HINT: to convert Oracle legacy syntax into Standard syntax just "revert" the position of the "(+)" symbol.
-- Example: if the "(+)" symbol is on the left, this means that the query it's a right join.

--------------------------------------------------------------------------------------------------------------
-- 6. Join simmetry
--------------------------------------------------------------------------------------------------------------

-- 6.1 Symmetry on the standard LEFT OUTER JOIN.
-- This query is similar to the 5.2 case, but we exchanged position of the tables (from left to right).
-- Result: the query is still a LEFT OUTER JOIN, which provides same data of the 5.4 and 5.5 cases, but
-- using a different columns order.
SELECT * FROM TEST_JOIN_PEOPLE people LEFT OUTER JOIN TEST_JOIN_FAMILY family
ON family.id = people.family_id
ORDER BY family.id ;

-- 6.2 Symmetry on the Oracle LEFT OUTER JOIN.
-- This query is similar to the 5.3 case, but we exchanged position of the columns (from left to right).
-- Result: the query is still a LEFT OUTER JOIN, which provides same data of the 5.4 and 5.5 cases, in 
-- this case using also the same columns order.
SELECT * FROM TEST_JOIN_FAMILY Family, TEST_JOIN_PEOPLE People
WHERE people.family_id  = family.id (+) 
ORDER BY family.id ;

-- 6.3 Symmetry on the standard RIGHT OUTER JOIN.
-- This query is similar to the 5.4 case, but we exchanged position of the tables (from left to right).
-- Result: the query is still a RIGHT OUTER JOIN, which provides same data of the 5.2 and 5.3 cases, but
-- using a different columns order.
SELECT * FROM TEST_JOIN_PEOPLE people RIGHT OUTER JOIN TEST_JOIN_FAMILY family
ON family.id = people.family_id 
ORDER BY family.id ;

-- 6.4 Symmetry on the Oracle RIGHT OUTER JOIN.
-- This query is similar to the 5.5 case, but we exchanged position of the columns (from left to right).
-- Result: the query is still a RIGHT OUTER JOIN, which provides same data of the 5.2 and 5.3 cases, in 
-- this case using also the same columns order.
SELECT * FROM TEST_JOIN_FAMILY Family, TEST_JOIN_PEOPLE People
WHERE people.family_id (+) = family.id
ORDER BY family.id ;

-- CONCLUSIONS:
-- 1. Standard syntax: "LEFT" or "RIGHT" depicts the table providing all records, even if not matched.
-- 2. Oracle syntax: the symbol "(+)" depicts the "optional" table, having only the matched records.

--------------------------------------------------------------------------------------------------------------
-- 7. Table truncate/destruction
--------------------------------------------------------------------------------------------------------------

-- Optional section, not necessary to run the examples below: these instructions can help you to clean/remove
-- the data structured created by this tutorial.

/*
DROP TABLE TEST_JOIN_PEOPLE;
DROP TABLE TEST_JOIN_FAMILY;

TRUNCATE TABLE TEST_JOIN_PEOPLE;
TRUNCATE TABLE TEST_JOIN_FAMILY;
*/