[MySQL] LEFT JOIN met AND expr

[headliner]

Ik heb de volgende database layout:

code:

CREATE TABLE t1 (
  id int(2) unsigned default '0' not null,
  name char(50) default '' not null
);
INSERT INTO t1 (id, name) VALUES ('1','name A');
INSERT INTO t1 (id, name) VALUES ('2','name B');
INSERT INTO t1 (id, name) VALUES ('3','name C');

CREATE TABLE t2 (
  t1_id int(2) unsigned default '0' not null,
  name_type char(50) default '' not null
);
INSERT INTO t2 (t1_id, name_type) VALUES ('1','check');
INSERT INTO t2 (t1_id, name_type) VALUES ('2','uncheck');
INSERT INTO t2 (t1_id, name_type) VALUES ('2','check');

en probeer de volgende query:

code:

SELECT t1.id FROM t1 LEFT JOIN t2 ON t2.t1_id = t1.id AND t2.name_type = 'uncheck'

met als resultaat:

1
2
3

waarbij een betere query uiteraard moet zijn:

code:

SELECT t1.id FROM t1 LEFT JOIN t2 ON t2.t1_id = t1.id WHERE t2.name_type = 'uncheck'

met alleen 2 als het goede resultaat maar het gaat me om de theorie en hoe ik die fout interpreteer want de mysql documentatie zegt:

The conditional_expr used with ON is any conditional expression of the form that can be used in a WHERE clause. Generally, you should use the ON clause for conditions that specify how to join tables, and the WHERE clause to restrict which rows you want in the result set.

wat ik dus interpreteer als: het is 'beter' maar niet 'noodzakelijk' om wat voor conditionele expressie dan ook perse in de WHERE clause te zetten. Naar mijn idee zou beide queries dus hetzelfde resultaat moeten geven maar dat doet het dus niet.

waar denk ik fout?

overigens ipv een LEFT JOIN alleen een JOIN gebruiken geeft ook het correcte resultaat...

[cariolive23]

Bij een LEFT JOIN kán er een relatie zijn tussen de linker en de rechtertabel, dat hoeft niet. Er hoeft in de rechtertabel dus geen data aanwezig te zijn die overeenkomt. Wanneer je in de JOIN een voorwaarde gaat toevoegen, dan moeten alle relaties aan deze voorwaarde voldoen. Met deze query wordt dat duidelijker:

code:

SELECT 
  t1.id AS tabel_1,
  t2.id AS tabel_2
FROM 
  t1 LEFT JOIN t2 ON t2.t1_id = t1.id AND t2.name_type = 'uncheck'

Nu zul je een NULL krijgen wanneer er geen waarde beschikbaar is.

Wanneer je een WHERE opneemt, moet het resultaat van de JOIN aan alle voorwaardes voldoen. Stel je dit voor alsof er met een JOIN eerst een virtuele tabel met records wordt opgebouwd op basis van de gestelde voorwaarden in de JOIN. Pas wanneer dit klaar is, gaat de query zoeken naar de resultaten die jij nodig hebt, bv. WHERE t2.name_type = 'uncheck'

Een beetje duidelijk zo?

Wanneer jij als voorwaarde stelt dat alleen records mogen worden opgehaald waarbij t2.name_type = 'uncheck', dan zul je geen LEFT JOIN gebruiken maar een INNER JOIN (of JOIN). Dit levert namelijk een kleinerere "virtuele tabel" op waarin moet worden gezocht. Je kunt de voorwaarde zowel in de JOIN zetten als in de WHERE, gebruik EXPLAIN om te zien welke sneller is.

[winkbrace]

Door de conditie t2.name_type = 'uncheck' in je JOIN clause op te nemen creeer je eigenlijk eerst een inner view van tabel t2 met alleen de records waar die conditie waar is voordat je gaat joinen.

SQL:

SELECT t1.id AS tabel_1
,      t2.id AS tabel_2
FROM t1 
LEFT JOIN (select * 
           from   t2 
           where  t2.name_type = 'uncheck'
          ) as t2 ON t2.t1_id = t1.id 

zoals cariolive23 al zegt verder

[headliner]

Het is me helemaal duidelijk, thx!