mysql的inner join, left join, right join

mysql的inner join, left join, right join

 0. 先看表資料：

mysql> select * from a;
id	age
1	10
2	20
3	30
4	40

4 rows in set (0.00 sec)

mysql> select * from b;
	score
	100
	200
	300
5	500

     1. inner join最簡單，我們之前說過， 來看下：

mysql> select * from a inner join b;

16 rows in set (0.00 sec)

    可以用on/where過濾下：

mysql> select * from a inner join b on a.id = b.id;

3 rows in set (0.00 sec)

mysql> select * from a inner join b where a.id = b.id;

      之是以可以用where, 是因為where之前本身就有結果。

     2. 再看left join:

mysql> select * from a left join b;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

     是以很顯然不能用where:

mysql> select * from a left join b where a.id = b.id;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where a.id = b.id' at line 1

      隻能用on:

mysql> select * from a left join b on a.id = b.id;
		NULL

     3. right join和left join類似，來看看right join的結果：

mysql> select * from a right join;

mysql> select * from a right join where a.id = b.id;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where a.id = b.id' at line 1

mysql> select * from a right join b on a.id = b.id;

    一目了然，不必多說。

作者：stpeace

來源：CSDN

原文：

https://blog.csdn.net/stpeace/article/details/91507571

版權聲明：本文為部落客原創文章，轉載請附上博文連結！