JOIN用法

http://www.sqlguides.com/sql_cross_join.php

 

JOIN ON

ON是和JOIN一起用的，是JOIN的条件

 

INNER JOIN ON

取两个表符合条件的交集

USE AFX_Server
SELECT F_File_Path.F_File_Path, F_File_Info.F_Size, F_File_Name, F_IP_MAC.Ip
FROM F_File_Info INNER JOIN
      F_File_Path ON F_File_Info.F_File_MD5 = F_File_Path.F_File_MD5
      INNER JOIN F_IP_MAC ON
	  F_File_Path.F_From_PC = F_IP_MAC.Mac
WHERE (F_File_Info.F_HotKey LIKE '%Q%') AND (F_File_Info.F_File_Name LIKE '%Q%')

 

CROSS JOIN ON

CROSS JOIN又称为笛卡尔乘积，实际上是把两个表乘起来，并集。

EXAMPLE :

Let's try with 2 tables below:

Table 1: GameScores

PlayerName	DepartmentId	Scores
Jason	1	3000
Irene	1	1500
Jane	2	1000
David	2	2500
Paul	3	2000
James	3	2000

Table 2: Departments

DepartmentId	DepartmentName
1	IT
2	Marketing
3	HR

SQL statement :

SELECT* FROM GameScores CROSS JOIN Departments

Result:

PlayerName	DepartmentId	Scores	DepartmentId	DepartmentName
Jason	1	3000	1	IT
Irene	1	1500	1	IT
Jane	2	1000	1	IT
David	2	2500	1	IT
Paul	3	2000	1	IT
James	3	2000	1	IT
Jason	1	3000	2	Marketing
Irene	1	1500	2	Marketing
Jane	2	1000	2	Marketing
David	2	2500	2	Marketing
Paul	3	2000	2	Marketing
James	3	3000	2	Marketing
Jason	1	3000	3	HR
Irene	1	1500	3	HR
Jane	2	1000	3	HR
David	2	2500	3	HR
Paul	3	2000	3	HR
James	3	3000	3	HR