tabelle in JOIN

ALL JOINS

In una base dati normalizzata, anche parzialmente, è inevitabile dover unire due o più tabelle.

Vediamo alcuni esempi di JOIN.

CREARE LE TABELLE

PROMPT all Joins - 2013, Mauro Rossolato
PROMPT 
PROMPT                 +--------------+
PROMPT left outer ---> | one 1        |
PROMPT                 |          +--------------+
PROMPT            +--> | two      | 2 : dos      |
PROMPT inner join |    |          |   :          |
PROMPT            +--> | three    | 3 : tres     |
PROMPT                 +--------|- - -+          |
PROMPT                            | 4 cuatro     | <--- right outer
PROMPT                            +--------------+
PROMPT              p1ita:
PROMPT                       +--------------+
PROMPT                       | uno 1        |
PROMPT                       |              |
PROMPT                       | tre 3        |
PROMPT                       +--------------+


DROP TABLE P1ENG; 
DROP TABLE P1SPA; 
DROP TABLE P1ITA; 
 
SET FEEDBACK OFF 
CREATE TABLE p1eng (tag NUMBER, nome VARCHAR2(20)); 
CREATE TABLE p1spa (tag NUMBER, nome VARCHAR2(20)); 
CREATE TABLE p1ita (tag NUMBER, nome VARCHAR2(20)); 
INSERT INTO P1SPA VALUES (2,'DOS'); 
INSERT INTO P1SPA VALUES (3,'TRES'); 
INSERT INTO P1SPA VALUES (4,'QUATRO'); 
INSERT INTO P1ENG VALUES (1,'ONE'); 
INSERT INTO P1ENG VALUES (2,'TWOS'); 
INSERT INTO P1ENG VALUES (3,'THREE'); 
INSERT INTO P1ITA VALUES (1,'UNO'); 
INSERT INTO P1ITA VALUES (3,'TRE'); 
INSERT INTO P1ITA VALUES (5,'CINQUE'); 
COMMIT;

INNER JOIN

PROMPT <<<<<<<<<<<<<<<< esempio 1 >>>>>>>>>>>>>>>>>>>>
SELECT * from p1spa s, p1eng e
where s.tag = e.tag;
PROMPT << PREMI INVIO >>
PAUSE

PROMPT <<<<<<<<<<<<<<<< esempio 2 >>>>>>>>>>>>>>>>>>>>
SELECT * from p1spa s
inner join p1eng e on s.tag = e.tag;
PROMPT << PREMI INVIO >>
PAUSE

LEFT OUTER JOIN

PROMPT <<<<<<<<<<<<<<<< esempio 3 >>>>>>>>>>>>>>>>>>>>
PROMPT rk con campo IN comune + non associati di SX (p1spa)
select * from p1spa s
left outer join p1eng e on s.tag = e.tag;
PROMPT << PREMI INVIO >>
PAUSE

RIGHT OUTER JOIN

PROMPT <<<<<<<<<<<<<<<< esempio 4 >>>>>>>>>>>>>>>>>>>>
PROMPT rk con campo IN comune + non associati di DX (p1eng)
select * from p1spa s
right outer join p1eng e on s.tag = e.tag;
PROMPT << PREMI INVIO >>
PAUSE

 

INNER JOIN  A 3 TABELLE

PROMPT <<<<<<<<<<<<<<<< esempio 5 >>>>>>>>>>>>>>>>>>>>
PROMPT rk con campo IN comune sulle 3 tabelle (p1ita,p1spa,p1eng)
select * from p1spa s
inner join p1eng e on s.tag = e.tag 
inner join p1ita i on i.tag = e.tag;
PROMPT << PREMI INVIO >>
PAUSE

 

INNER JOIN A 3 E RIGHT OUTER

PROMPT <<<<<<<<<<<<<<<< esempio 6 >>>>>>>>>>>>>>>>>>>>
PROMPT rk con campo IN comune sulle 3 tabelle (p1ita,p1spa,p1eng) + NON associati in right (ita)
select * from p1spa s
inner join p1eng e on s.tag = e.tag 
right outer join p1ita i on i.tag = e.tag;
PROMPT << PREMI INVIO >>
PAUSE

INNER JOIN A 3 E LEFT OUTER

PROMPT <<<<<<<<<<<<<<<< esempio 7 >>>>>>>>>>>>>>>>>>>>
select * from p1spa s
inner join p1eng e on s.tag = e.tag 
left outer join p1ita i on i.tag = e.tag;
PROMPT << PREMI INVIO >>
PAUSE

 

 

 

 

 

 

 

 

 

Leave a Reply