


        If the data I need to query is in multiple tables, I MUST connect them
        this means I will need a foreign key
        
        a foreign key is a copy of the data from the connected table 
        
        for example:
        
CREATE TABLE CITY (
        ID INTEGER GENERATED BY DEFAULT AS IDENTITY,
        NAME CHAR(35) NOT NULL,
        COUNTRY_CODE CHAR(3) NOT NULL,   <- this is a copy of the code from the Country table
        DISTRICT CHAR(25),                    it is my connector
        POPULATION INTEGER NOT NULL,
        CONSTRAINT PK_CITY PRIMARY KEY (ID)
);

CREATE TABLE COUNTRY (
        CODE CHAR(3) NOT NULL,          <- this is the orginal data copied to the City table
        NAME CHAR(52) NOT NULL,
        CONTINENT VARCHAR(15),
        REGION CHAR(26) NOT NULL,
        SURFACE_AREA DECIMAL(10,2) NOT NULL,
        INDEP_YEAR SMALLINT,
        POPULATION INTEGER NOT NULL,
        LIFE_EXPECTANCY DECIMAL(3,1),
        GNP DECIMAL(10,2),
        LOCALNAME CHAR(45),
        GOVERNMENT_FORM CHAR(45),
        CAPITAL INTEGER,                 <- this is a copy of the City Id, so I know which City
        CODE2 CHAR(2),                                is the Capital for this Country
        CONSTRAINT PK_COUNTRY PRIMARY KEY (CODE)
);

the Data Defination Language (DDL) to create these connects is:

ALTER TABLE COUNTRY 
   ADD CONSTRAINT FK_COUNTRY_CITY FOREIGN KEY (CAPITAL) 
   REFERENCES CITY(ID);

ALTER TABLE CITY 
  ADD CONSTRAINT FK_CITY_COUNTRY FOREIGN KEY (COUNTRY_CODE) 
  REFERENCES COUNTRY(CODE);
  
-------------------------------------------------------
SQL Joins have been in teh SQL standard since day 1, however the syntx has evoloved

we now have 2 syntaxs for coding a join

1) Old School -> is use a comma between the table names
                 and connect them via the where clause
                 for example:
                 
                  select  distinct p.product_name
                         from customer.Product p,
                          customer.order_item oi
                         where p.id = oi.product_id
                           and p.supplier_id = 12;
                           
2) New School -> use the keyword JOIN and connect them via
                  the keyword ON
                  for exanmple:
                  
                  select p.id, p.product_name, oi.order_id
                    from customer.Product p
                     join customer.order_item oi    
                     on p.id = oi.product_id    
                   where p.supplier_id = 12
                   
                   by default this is a INNER JOIN, but we do not code the word inner

------------------------------------------------------
Join types:

Equi-Join where I connect columns w/ an equal check:
       select p.id, p.product_name, oi.order_id
        from customer.Product p
           join customer.order_item oi    
           on p.id = oi.product_id       <- this is the equi-join check
        where p.supplier_id = 12
        
WARNING: if 1 side of the join does not have the matching data it will NOT be included
         in the results
         
         an Equi-Join matchs the 2 side of the connection is one or zero matches
         
Natural Join where I DO NOT code the connection, the server looks for
fields with the same name, this is not very popular

Non-Equi-Join where I connect with a > or < connection, and I could end up with
zero or more matches

Cartesion Join (also known as a CROSS JOIN) where I do NOT connect the table and the server dumps each row in the left
table for each row in the right table (left * right) and I get a huge set of results I 
cannot use, for example:
 
 select count(*)
 from world.country,
      world.continent;   <- I get answer of 1673 which is 239 countries times 7 continents OOPS!
   

Outer Join where I want to include ALL the rows from one side of the join even if there is no match
I must use the newer sytax to do this for example:

select distinct org.name 
from world.organization org left outer join world.country_organization co
  on org.code = co.organization_code
  order by org.name
  
     Note: the organization table is on LEFT of the keyword join, so a LEFT OUTER JOIN
           will include ALL rows from the organization table even is there is no match

I can code:
   LEFT OUTER JOIN
   RIGHT OUTER JOIN
   FULL OUTER JOIN (all row from BOTH sides of the join)
   
   if I have a Supplier that has never sold me a product, then there is row in supplier
   table but no matching rows in the product table, if I want to see ALL suppliers I need
   and OUTER JOIN
   
   I have the same issue when I have a table of products, and some of those products
   have never been ordered
   
   
   
   
--------------------------------------------
I have a table that show the country code for each organization
  I can now connect to the Country table and find the country details
  
What happens if...

I have an Organization without any countries?
or I have a Country that does not belong to an Organization?

so..

select distinct org.name 
from world.organization org
  join
     world.country_organization co
  on org.code = co.organization_code
  order by org.name
  
I will NOT get the League of Nations becuase there is no match for
counties in this organization




         
         
