ON æ¡ä»¶ï¼âA LEFT JOIN B ON æ¡ä»¶è¡¨è¾¾å¼âä¸çONï¼ç¨æ¥å³å®å¦ä½ä» B 表ä¸æ£ç´¢æ°æ®è¡ã
å¦æ B 表ä¸æ²¡æä»»ä½ä¸è¡æ°æ®å¹é ON çæ¡ä»¶,å°ä¼é¢å¤çæä¸è¡ææå为 NULL çæ°æ®ã
å¨å¹é é¶æ®µ WHERE åå¥çæ¡ä»¶é½ä¸ä¼è¢«ä½¿ç¨ãä» å¨å¹é é¶æ®µå®æ以åï¼WHERE åå¥æ¡ä»¶æä¼è¢«ä½¿ç¨ãå®å°ä»å¹é é¶æ®µäº§ççæ°æ®ä¸æ£ç´¢è¿æ»¤ã
让æ们çä¸ä¸ª LFET JOIN 示ä¾ï¼
mysql> CREATE TABLE `product` (
`id` int(10) unsigned NOT NULL auto_increment,
`amount` int(10) unsigned default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
mysql> CREATE TABLE `product_details` (
`id` int(10) unsigned NOT NULL,
`weight` int(10) unsigned default NULL,
`exist` int(10) unsigned default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
mysql> INSERT INTO product (id,amount)
VALUES (1,100),(2,200),(3,300),(4,400);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> INSERT INTO product_details (id,weight,exist)
VALUES (2,22,0),(4,44,1),(5,55,0),(6,66,1);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM product;
+----+--------+
| id | amount |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
| 4 | 400 |
+----+--------+
4 rows in set (0.00 sec)
mysql> SELECT * FROM product_details;
+----+--------+-------+
| id | weight | exist |
+----+--------+-------+
| 2 | 22 | 0 |
| 4 | 44 | 1 |
| 5 | 55 | 0 |
| 6 | 66 | 1 |
+----+--------+-------+
mysql> SELECT * FROM product LEFT JOIN product_details
ON (product.id = product_details.id);
+----+--------+------+--------+-------+
| id | amount | id | weight | exist |
+----+--------+------+--------+-------+
| 1 | 100 | NULL | NULL | NULL |
| 2 | 200 | 2 | 22 | 0 |
| 3 | 300 | NULL | NULL | NULL |
| 4 | 400 | 4 | 44 | 1 |
+----+--------+------+--------+-------+
- ON åå¥å WHERE åå¥æä»ä¹ä¸å
ç¨ä¾åæ¥ç解æ好ä¸è¿äºï¼
mysql> SELECT * FROM product LEFT JOIN product_details
ON (product.id = product_details.id)
AND product_details.id=2;
+----+--------+------+--------+-------+
| id | amount | id | weight | exist |
+----+--------+------+--------+-------+
| 1 | 100 | NULL | NULL | NULL |
| 2 | 200 | 2 | 22 | 0 |
| 3 | 300 | NULL | NULL | NULL |
| 4 | 400 | NULL | NULL | NULL |
+----+--------+------+--------+-------+
4 rows in set (0.00 sec)
mysql> SELECT * FROM product LEFT JOIN product_details
ON (product.id = product_details.id)
WHERE product_details.id=2;
+----+--------+----+--------+-------+
| id | amount | id | weight | exist |
+----+--------+----+--------+-------+
| 2 | 200 | 2 | 22 | 0 |
+----+--------+----+--------+-------+
第ä¸æ¡æ¥è¯¢ä½¿ç¨ ON æ¡ä»¶å³å®äºä» LEFT JOINç product_details表ä¸æ£ç´¢ç¬¦åçæææ°æ®è¡ã
第äºæ¡æ¥è¯¢åäºç®åçLEFT JOINï¼ç¶åä½¿ç¨ WHERE åå¥ä» LEFT JOINçæ°æ®ä¸è¿æ»¤æä¸ç¬¦åæ¡ä»¶çæ°æ®è¡ã
åæ¥çä¸äºç¤ºä¾ï¼
mysql>
mysql> SELECT * FROM product LEFT JOIN product_details
ON product.id = product_details.id
AND product.amount=100;
+----+--------+------+--------+-------+
| id | amount | id | weight | exist |
+----+--------+------+--------+-------+
| 1 | 100 | NULL | NULL | NULL |
| 2 | 200 | NULL | NULL | NULL |
| 3 | 300 | NULL | NULL | NULL |
| 4 | 400 | NULL | NULL | NULL |
+----+--------+------+--------+-------+
æææ¥èªproduct表çæ°æ®è¡é½è¢«æ£ç´¢å°äºï¼ä½æ²¡æå¨product_details表ä¸å¹é å°è®°å½ï¼product.id = product_details.id AND product.amount=100 æ¡ä»¶å¹¶æ²¡æå¹é å°ä»»ä½æ°æ®ï¼
mysql> SELECT * FROM product LEFT JOIN product_details
ON (product.id = product_details.id)
AND product.amount=200;
+----+--------+------+--------+-------+
| id | amount | id | weight | exist |
+----+--------+------+--------+-------+
| 1 | 100 | NULL | NULL | NULL |
| 2 | 200 | 2 | 22 | 0 |
| 3 | 300 | NULL | NULL | NULL |
| 4 | 400 | NULL | NULL | NULL |
+----+--------+------+--------+-------+
åæ ·ï¼æææ¥èªproduct表çæ°æ®è¡é½è¢«æ£ç´¢å°äºï¼æä¸æ¡æ°æ®å¹é å°äºã
- ä½¿ç¨ WHERE ⦠IS NULL åå¥ç LEFT JOIN
å¦åæè¿°ï¼WHERE æ¡ä»¶æ¥è¯¢åçå¨ å¹é é¶æ®µä¹åï¼è¿æå³ç WHERE ⦠IS NULL åå¥å°ä»å¹é é¶æ®µåçæ°æ®ä¸è¿æ»¤æä¸æ»¡è¶³å¹é æ¡ä»¶çæ°æ®è¡ã
纸é¢ä¸çèµ·æ¥å¾æ¸ æ¥ï¼ä½æ¯å½ä½ å¨ ON åå¥ä¸ä½¿ç¨å¤ä¸ªæ¡ä»¶æ¶å°±ä¼æå°å°æäºã
ææ»ç»äºä¸ç§ç®åçæ¹å¼æ¥ç解ä¸è¿°æ åµï¼
å° IS NULL ä½ä¸ºå¦å®å¹é æ¡ä»¶
ä½¿ç¨ !(A and B) == !A OR !B é»è¾å¤æ
ççä¸é¢ç示ä¾ï¼
SELECT a.* FROM product a LEFT JOIN product_details b
ON a.id=b.id AND b.weight!=44 AND b.exist=0
WHERE b.id IS NULL;
+----+--------+
| id | amount |
+----+--------+
| 1 | 100 |
| 3 | 300 |
| 4 | 400 |
+----+--------+
3 rows in set (0.00
让æ们æ£æ¥ä¸ä¸ ON å¹é åå¥ï¼
(a.id=b.id) AND (b.weight!=44) AND (b.exist=0)
æ们å¯ä»¥æ IS NULL åå¥ çä½æ¯å¦å®å¹é æ¡ä»¶ã
è¿æå³çæ们å°æ£ç´¢å°ä»¥ä¸è¡ï¼
!( exist(b.id that equals to a.id) AND b.weight !=44 AND b.exist=0 )
!exist(b.id that equals to a.id) || !(b.weight !=44) || !(b.exist=0)
!exist(b.id that equals to a.id) || b.weight =44 || b.exist=1
å°±åå¨Cè¯è¨ä¸çé»è¾ AND å é»è¾ OR表达å¼ä¸æ ·ï¼å ¶æä½æ°æ¯ä»å·¦å°å³æ±å¼çãå¦æ第ä¸ä¸ªåæ°åå¤å¤ææä½ç»æï¼é£ä¹ç¬¬äºä¸ªåæ°ä¾¿ä¸ä¼è¢«è®¡ç®æ±å¼ï¼çè·¯ææï¼
ççå«ç示ä¾ï¼
mysql> SELECT a.* FROM product a LEFT JOIN product_details b
ON a.id=b.id AND b.weight!=44 AND b.exist=1
WHERE b.id IS NULL;
+----+--------+
| id | amount |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
| 4 | 400 |
+----+--------+
4 rows in set (0.00
- Matching-Conditions ä¸ Where-conditions ä¹æ
å¦æä½ åºæ¬çæ¥è¯¢æ¡ä»¶æ¾å¨ ON åå¥ä¸ï¼æå©ä¸çå¦å®æ¡ä»¶æ¾å¨ WHERE åå¥ä¸ï¼é£ä¹ä½ ä¼è·å¾ç¸åçç»æã
ä¾å¦ï¼ä½ å¯ä»¥ä¸è¿æ ·åï¼
SELECT a.* FROM product a LEFT JOIN product_details b
ON a.id=b.id AND b.weight!=44 AND b.exist=0
WHERE b.id IS NULL;
ä½ å¯ä»¥è¿æ ·åï¼
SELECT a.* FROM product a LEFT JOIN product_details b
ON a.id=b.id
WHERE b.id is null OR b.weight=44 OR b.exist=1;
mysql> SELECT a.* FROM product a LEFT JOIN product_details b
ON a.id=b.id
WHERE b.id is null OR b.weight=44 OR b.exist=1;
+----+--------+
| id | amount |
+----+--------+
| 1 | 100 |
| 3 | 300 |
| 4 | 400 |
+----+--------+
è¿äºæ¥è¯¢ççææä¸æ ·ï¼
å¦æä½ åªéè¦ç¬¬ä¸ä¸ªè¡¨ä¸çæ°æ®çè¯ï¼è¿äºæ¥è¯¢ä¼è¿åç¸åçç»æéãæä¸ç§æ åµå°±æ¯ï¼å¦æä½ ä» LEFT JOINç表ä¸æ£ç´¢æ°æ®æ¶ï¼æ¥è¯¢çç»æå°±ä¸åäºã
å¦åæå±ï¼WHERE åå¥æ¯å¨å¹é é¶æ®µä¹åç¨æ¥è¿æ»¤çã
ä¾å¦ï¼
SELECT * FROM product a LEFT JOIN product_details b
ON a.id=b.id AND b.weight!=44 AND b.exist=1
WHERE b.id is null;
+----+--------+------+--------+-------+
| id | amount | id | weight | exist |
+----+--------+------+--------+-------+
| 1 | 100 | NULL | NULL | NULL |
| 2 | 200 | NULL | NULL | NULL |
| 3 | 300 | NULL | NULL | NULL |
| 4 | 400 | NULL | NULL | NULL |
+----+--------+------+--------+-------+
4 rows in set (0.00 sec)
mysql> SELECT * FROM product a LEFT JOIN product_details b
ON a.id=b.id
WHERE b.id IS NULL OR b.weight=44 OR b.exist=0;
+----+--------+------+--------+-------+
| id | amount | id | weight | exist |
+----+--------+------+--------+-------+
| 1 | 100 | NULL | NULL | NULL |
| 2 | 200 | 2 | 22 | 0 |
| 3 | 300 | NULL | NULL | NULL |
| 4 | 400 | 4 | 44 | 1 |
+----+--------+------+--------+-------+
4 rows in set (0.00
æ»é注ï¼
å¦æä½ ä½¿ç¨ LEFT JOIN æ¥å¯»æ¾å¨ä¸äºè¡¨ä¸ä¸åå¨çè®°å½ï¼ä½ éè¦åä¸é¢çæµè¯ï¼WHERE é¨åç col_name IS NULL(å ¶ä¸ col_name å被å®ä¹ä¸º NOT NULL)ï¼MYSQL å¨æ¥è¯¢å°ä¸æ¡å¹é LEFT JOIN æ¡ä»¶åå°åæ¢æç´¢æ´å¤è¡ï¼å¨ä¸ä¸ªç¹å®çç»åé®ä¸ï¼ã