I cannot help you on specific performance timings -- but if the result is the same, the database engine can use whatever trick to retrieve them. That being said, a "subquery/derived table" could then be rewritten (internally) to a JOIN, for example.
Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com > 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! > ---------------------------------------------------------------------------- ---- > > > ---------------------------------------------------------------------------- ---- > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
