Sql 简明教程

SQL - Joins

The SQL Join Clause

SQL Join 子句用于将来自数据库中两个或更多个表中的数据组合到一起。当相关数据存储在多个表中时，联接可帮助你使用外键检索记录，从而将这些表中的字段组合起来。

The SQL Join clause is used to combine data from two or more tables in a database. When the related data is stored across multiple tables, joins help you to retrieve records combining the fields from these tables using their foreign keys.

Join 子句中指定从两个或更多个表中联接记录的列的部分称为 join-predicate 。此谓词通常与 ON 子句一起指定，并使用各种比较运算符，例如 <、>、<>、⇐、>=、!=、BETWEEN、LIKE 和 NOT 等。我们还可以使用逻辑运算符 AND、OR 和 NOT 将多个联接谓词连接起来。

The part of the Join clause that specifies the columns on which records from two or more tables are joined is known as join-predicate. This predicate is usually specified along with the ON clause and uses various comparison operators such as, <, >, <>, ⇐, >=, !=, BETWEEN, LIKE, and NOT etc. We can also connect multiple join predicates with logical operators AND, OR, and NOT.

Syntax

以下是 SQL JOIN CLAUSE 的基本语法：

Following is the basic syntax of a the SQL JOIN CLAUSE −

SELECT column_name(s)
FROM table1
JOIN table2;

Example

假设我们使用以下查询创建了一个 CUSTOMERS 表，其中包含组织的客户详细信息：

Assume we have created a CUSTOMERS table that contains details of the customers of an organization using the following query −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

现在使用 INSERT 语句向该表中插入值，如下所示：

Now insert values into this table using the INSERT statement as follows −

INSERT INTO CUSTOMERS VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'Hyderabad', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );

CUSTOMERS 表将创建如下：

The CUSTOMERS table will be created as follows −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

以下是另一个包含客户所做的订单详细信息的 ORDERS 表。

Following is another table ORDERS which contains the order details made by the customers.

CREATE TABLE ORDERS (
   OID INT NOT NULL,
   DATE VARCHAR (20) NOT NULL,
   CUSTOMER_ID INT NOT NULL,
   AMOUNT DECIMAL (18, 2)
);

使用 INSERT 语句像下面这样向该表中插入值：

Using the INSERT statement, insert values into this table as follows −

INSERT INTO ORDERS VALUES
(102, '2009-10-08 00:00:00', 3, 3000.00),
(100, '2009-10-08 00:00:00', 3, 1500.00),
(101, '2009-11-20 00:00:00', 2, 1560.00),
(103, '2008-05-20 00:00:00', 4, 2060.00);

ORDERS 表将创建如下：

The ORDERS table will be created as follows −

OID	DATE	CUSTOMER_ID	AMOUNT
102	2009-10-08 00:00:00	3	3000.00
100	2009-10-08 00:00:00	3	1500.00
101	2009-11-20 00:00:00	2	1560.00
103	2008-05-20 00:00:00	4	2060.00

以下查询对 CUSTMERS 和 ORDERS 表执行联接操作：

Following query performs the join operation on the tables CUSTMERS and ORDERS −

SELECT ID, NAME, AGE, AMOUNT
FROM CUSTOMERS
JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

Output

通过执行上述查询，将显示结果表，其中包含 CUSTOMERS 表中 ID、NAME、AGE 字段和 ORDERS 表中 AMOUNT 字段中的值。

By executing the query above, the resultant table is displayed and contains the values present in ID, NAME, AGE fields of CUSTOMERS table and AMOUNT field of ORDERS table.

ID	NAME	AGE	AMOUNT
3	Kaushik	23	3000
3	Kaushik	23	1500
2	Khilan	25	1560
4	Chaitali	25	2060

Types of joins in SQL

SQL 提供了多种类型的联接，这些联接根据多种表中的数据连接在一起的方式进行分类。它们被列为以下：

SQL provides various types of Joins that are categorized based on the way data across multiple tables are joined together. They are listed as follows −

Inner Join

INNER JOIN 是检索两张表的交集的默认联接。它将第一张表的每一行与第二张表的每一行进行比较。如果这些行的对满足关联谓词，则将它们联接在一起。

An INNER JOIN is the default join which retrieves the intersection of two tables. It compares each row of the first table with each row of the second table. If the pairs of these rows satisfy the join-predicate, they are joined together.

Outer Join

外部关联会检索两张表中的所有记录，即使一张表中没有另一张表的对应行也是如此，这与内部关联不同。外部关联进一步分为三种子类型——左关联、右关联和全关联。

An Outer Join retrieves all the records in two tables even if there is no counterpart row of one table in another table, unlike Inner Join. Outer join is further divided into three subtypes - Left Join, Right Join and Full Join.

以下是不同类型的外部关联：

Following are the different types of outer Joins −

LEFT JOIN − returns all rows from the left table, even if there are no matches in the right table.
RIGHT JOIN − returns all rows from the right table, even if there are no matches in the left table.
FULL JOIN − returns rows when there is a match in one of the tables.

Other Joins

除了这些之外，还有另外两种关联：

In addition to these there are two more joins −

SELF JOIN − is used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement.
CROSS Join − returns the Cartesian product of the sets of records from the two or more joined tables.