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
> (
> TermIdMEDIUMINT 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,
> FineDATETIME 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]
