List:General Discussion« Previous MessageNext Message »
From:MAS! Date:October 3 2006 9:58am
Subject:join vs subqueries
View as plain text  
I'm not (yet) using sub-queries since the old version of MySQL were  
unable to handle them, then I was using 'join'.

I wish to know if it's possibile to do all what I did with 'join'  
with subqueries.

and which one is faster/better to use?

for example it'd be possibile to 'translate' that using subselect?

SELECT Esercente.Insegna, Terminale.TermId, Abilitazione.CodCarta,
        Circuito.Nome, Abilitazione.Data, Circuito.Tel
FROM Esercente
INNER JOIN Terminale ON Esercente.CodSia = Terminale.CodSia
LEFT  JOIN Abilitazione ON Terminale.TermId=Abilitazione.TermId
LEFT  JOIN Circuito ON Abilitazione.CodCarta=Circuito.CodCarta

my db is that (and on attachment there is the pdf with the logical  
schema)

CREATE DATABASE IF NOT EXISTS CentroServizi;
USE CentroServizi;
CREATE TABLE IF NOT EXISTS Esercente
(
CodSia  MEDIUMINT UNSIGNED NOT NULL,  #Il CodSia e' un numero  
positivo a 7
                                        # cifre;
                                        # MEDIUMINT UNSIGNED va da 0  
a 16777215
Citta   VARCHAR(20) NOT NULL,
Insegna VARCHAR(50),
PRIMARY KEY (CodSia)
)
TYPE = InnoDB


CREATE TABLE IF NOT EXISTS Terminale
(
TermId   MEDIUMINT UNSIGNED NOT NULL, # il TermId e' numero positivo  
a 6 cifre
                                        # MEDIUMINT UNSIGNED va da 0  
a 16777215
CodSia   MEDIUMINT UNSIGNED NOT NULL,
Modello  VARCHAR(20),
PRIMARY KEY (TermId),
INDEX CodSia (CodSia),
FOREIGN KEY (CodSia) REFERENCES Esercente(CodSia)
                       ON UPDATE CASCADE
                       ON DELETE CASCADE
)
TYPE=INNODB;

CREATE TABLE IF NOT EXISTS Circuito
(
CodCarta TINYINT UNSIGNED NOT NULL, # il CodCarta e' un numero  
positivo a 2
                                      # cifre
                                      # TINYINT UNSIGNED va da 0 a 255
Nome     VARCHAR(20) NOT NULL,
Tel      VARCHAR(50),
PRIMARY KEY (CodCarta)
)
TYPE=INNODB;

CREATE TABLE IF NOT EXISTS Abilitazione
(
TermId    MEDIUMINT UNSIGNED NOT NULL,
CodCarta  TINYINT   UNSIGNED NOT NULL,
Data      DATE NOT NULL,
PRIMARY KEY (TermId, CodCarta),
INDEX TermId (TermId),
INDEX CodCarta  (CodCarta),
FOREIGN KEY (TermId) REFERENCES Terminale(TermId)
                         ON UPDATE CASCADE
                         ON DELETE CASCADE,
FOREIGN KEY (CodCarta) REFERENCES Circuito(CodCarta)
              ON UPDATE CASCADE
              ON DELETE CASCADE
)
TYPE=INNODB;


CREATE TABLE IF NOT EXISTS ChiaInt
(
CallId      INT UNSIGNED NOT NULL, # Si potranno gestire fino a  
4294967295
                                     # chiamate
Operatore   VARCHAR(20) NOT NULL,
Chiamante   MEDIUMINT UNSIGNED NOT NULL , # e' il CodSia
Inizio      DATETIME NOT NULL,
Fine        DATETIME NULL,
Manutentore VARCHAR(20),
Motivo      TEXT, # Stringa lunga al max 65KB
PRIMARY KEY (CallId),
INDEX Chiamante (Chiamante),
FOREIGN KEY (Chiamante) REFERENCES Esercente(CodSia)
                       ON UPDATE CASCADE
                       ON DELETE CASCADE
)
TYPE=INNODB;




thank you in advance

MAS!



Thread
join vs subqueriesMAS!3 Oct
  • Re: join vs subqueriesMartijn Tonies3 Oct