JOINS - das leidige Thema (mehrere Tabellen abfragen)

weil hier immer so viel fragen zu joins und zu erklärenden beispiel kommen, hab ich mich in meiner grenzenlosen güte mal hingesetzt und was zusammengeflickt

die SELECT-Statements einfach mal ausprobieren und schauen was rauskommt, ich denke, ich hab so ziemlich jeden Fall abgedeckt

tabellen

CREATE TABLE tabelle1 (
  tab1_id int(11) NOT NULL auto_increment,
  zahl int(11) NOT NULL default '0',
  PRIMARY KEY  (tab1_id)
) TYPE=MyISAM;

INSERT INTO tabelle1 VALUES (1, 1),
(2, 2),
(3, 4),
(4, 8);

CREATE TABLE tabelle2 (
  tab2_id int(11) NOT NULL auto_increment,
  tab1_id int(11) default '0',
  quadrat int(11) NOT NULL default '0',
  PRIMARY KEY  (tab2_id)
) TYPE=MyISAM;

INSERT INTO tabelle2 VALUES (1, 2, 4),
(2, 3, 16),
(3, 4, 64),
(4, NULL, 256);

SELECT-Statements

zahl und quadrat auslesen:

SELECT
	t1.zahl, t2.quadrat
FROM
	tabelle1 t1
	INNER JOIN tabelle2 t2 USING(tab1_id)

# alternativ
# so können auch mehrere bedingungen angegeben werden
# syntax is mit der bei WHERE identisch

SELECT
	t1.zahl, t2.quadrat
FROM
	tabelle1 t1
	INNER JOIN tabelle2 t2 ON t1.tab1_id = t2.tab1_id

# mit ON können auch spalten verglichen werden, deren namen nicht gleich sind
# aber schon allein der übersichtlichkeit halber wären identische spaltennamen besser
# sinnloses beispiel:
SELECT
	t1.zahl, t2.quadrat
FROM
	tabelle1 t1
	INNER JOIN tabelle2 t2 ON t1.tab1_id = t2.tab2_id

alle zahlen auslesen und das quadrat dazu, sofern vorhanden

SELECT
	t1.zahl, t2.quadrat
FROM
	tabelle1 t1
	LEFT JOIN tabelle2 t2 USING(tab1_id)

alle quadrate auslesen und die wurzel dazu, sofern vorhanden

SELECT
	t1.zahl, t2.quadrat
FROM
	tabelle1 t1
	RIGHT JOIN tabelle2 t2 USING(tab1_id)

##selbstverständlich geht auch das:

SELECT
	t1.zahl, t2.quadrat
FROM
	tabelle2 t2
	LEFT JOIN tabelle1 t1 USING(tab1_id)

alle zahlen auslesen, zu denen das quadrat nicht vorhanden ist

SELECT
	t1.zahl
FROM
	tabelle1 t1
	LEFT JOIN tabelle2 t2 USING(tab1_id)
WHERE
	t2.tab1_id IS NULL

alle quadrate auslesen, zu denen die wurzel nicht vorhanden ist

SELECT
	t2.quadrat
FROM
	tabelle1 t1
	RIGHT JOIN tabelle2 t2 USING(tab1_id)
WHERE
	t1.tab1_id IS NULL

Und noch ein Beispiel, dann is wirklich Schluss (sag ich jetzt einfach so )

CREATE TABLE tabelle3 (
  tab3_id int(11) NOT NULL auto_increment,
  tab1_id int(11) default '0',
  kubik int(11) NOT NULL default '0',
  PRIMARY KEY  (tab3_id)
) TYPE=MyISAM;

INSERT INTO tabelle3 VALUES (1, 1, 1),
(2, 2, 8),
(3, 3, 64);

zahl und zahl² und/oder zahl³ liefern, sofern vorhanden

SELECT
	t1.zahl,
	t2.quadrat,
	t3.kubik
FROM
	tabelle2 t2
	RIGHT JOIN tabelle1 t1 ON t2.tab1_id=t1.tab1_id
		LEFT JOIN tabelle3 t3 ON t1.tab1_id=t3.tab1_id

bei noch mehr tabellen geht's analog

mann sollte sich nur überlegen, welche tabelle man wo hinschreibt, hier steht tabelle1 in der mitte, da ich sie zweimal brauche
hätte ich mittabelle1 angefangen, hätte ich sie zweimal verwenden müssen, was ja nicht unbedingt sein muss

Awsome Happy!
Gestern hat mich jemand dannach gefragt, werd gleich mal den link weiter geben.

Thanks!

cool!

ich habe diese seite als nach schlag: http://www.little-idiot.de/mysql/mysql-118.html

was ich mich schon immer fragte, was ist der unterschied zu:

SELECT t1.t1_id,t2.t2_id FROM table1 as t1, table2 as t2 WHERE t2.t1_id = t1.t1_id

das steht bei meinem link sehr genau! performance!

@invitatoinvitat
stell dir einfach vor, dass die datenbank bei dir das karthesische produkt der tabellen bildet, also jede zeile aus tabelle1 mit jeder zeile aus tabelle2 verknüpft und danach diese ergebnismenge durchgeht und datensätze, die die bedingung nicht erfüllen nachträglich rausschmeißt

kommt zwar nicht ganz hin, aber vom prinzip her sollte es klar sein

genau, und weil das
SELECT t1.t1_id,t2.t2_id FROM table1 as t1, table2 as t2 WHERE t2.t1_id = t1.t1_id
alle zeilen betrifft macht man erst einen join um sie auf die relevanten ids zuerst zu reduzieren. speed machen
umgekehrt hat man dardurch auch die möglichkeit nicht existtierende gegenstücke der anderen tabelle zu ermittel (wo gibt es keine paare).. und das kannst du mit dem query oben nicht.
bei kleinen tabellen ist das eigentl egal. wenn du aber ein paar tausend einträge hast unterscheidet sich die abfrage geschwindigkeit erheblich (unterschiede im sec. bereich anstelle von ein paar millisekunden!)

Original geschrieben von mrhappiness
@invitatoinvitat
stell dir einfach vor, dass die datenbank bei dir das karthesische produkt der tabellen bildet, also jede zeile aus tabelle1 mit jeder zeile aus tabelle2 verknüpft und danach diese ergebnismenge durchgeht und datensätze, die die bedingung nicht erfüllen nachträglich rausschmeißt

kommt zwar nicht ganz hin, aber vom prinzip her sollte es klar sein

Naja ... das ist allerdings auch mehr ein Produkt aus Theorie ... und dem was die Spinner bei little-idiot schreiben ... moderne Datenbank optimieren durchaus etwas mehr ... !

Wobei der EQUI-Join meist definitiv langsamer ist als ein LEFT JOIN ... möglicherweise liefert dieser jedoch nicht die gewünschten Ergebnisse ... (insbesondere beim aggregieren).

oho :-O
danke für die auskünfte, ich beschäftige mich derzeit nämlich mit der optimierung meiner community, da sie bei knapp 70 gleichzeitigen usern merklich langsamer wird, da sind ziemlich viele solcher verknüpften abfragen drin, die auf tabellen mit über 50.000 datensätzen zugreifen

Zwei weitere gute Einführungen ins Thema JOINs bei SELFHTML:

Einführung in Joins
Fortgeschrittene Jointechniken