Mysql 简明教程
MySQL - Using Joins
MySQL 中的 Join 子句用于将一个数据库中的两张或更多张表的记录合并在一起。这些表基于 WHERE 子句中指定的条件连接在一起。
A Join clause in MySQL is used to combine records from two or more tables in a database. These tables are joined together based on a condition, specified in a WHERE clause.
例如，可以将两个不同表中相似列的值相等（=）进行比较作为连接谓词。此外，可以使用多个运算符来连接表，例如 <、>、<>、⇐、>=、!=、BETWEEN、LIKE 和 NOT 等。
For example, comparing the equality (=) of values in similar columns of two different tables can be considered as a join-predicate. In addition, several operators can be used to join tables, such as <, >, <>, ⇐, >=, !=, BETWEEN, LIKE, and NOT etc.
我们可以使用 SELECT、UPDATE 和 DELETE 语句中的 JOINS 来连接 MySQL 表。
We can use JOINS in the SELECT, UPDATE and DELETE statements to join the MySQL tables.
Types of Joins
SQL 提供了多种类型的连接，它们根据跨多个表连接数据的方式进行分类。它们列在以下内容中：
There are various types of Joins provided by SQL which are categorized based on the way data across multiple tables are joined together. They are listed as follows −
-
Inner Join − An Inner Join 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. This is a default join.
-
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, like Inner Join. Outer join is further divided into three subtypes: Left Join, Right Join and Full Join. We will learn about these Joins later in this tutorial.
Example
在此示例中，我们首先使用 CREATE TABLE 查询创建名为 CUSTOMERS 的表，如下所示：
In this example, we first create a table named CUSTOMERS using the CREATE TABLE query as follows −
CREATE TABLE CUSTOMERS (
ID INT NOT NULL,
NAME VARCHAR(15) NOT NULL,
AGE INT NOT NULL,
ADDRESS VARCHAR(25),
SALARY DECIMAL(10, 2),
PRIMARY KEY(ID)
);然后，我们将以下记录插入 CUSTOMERS 表中：
Let us then insert the following records in the CUSTOMERS table −
INSERT INTO CUSTOMERS VALUES
(1, 'Ramesh', '32', 'Ahmedabad', 2000),
(2, 'Khilan', '25', 'Delhi', 1500),
(3, 'Kaushik', '23', 'Kota', 2500),
(4, 'Chaitali', '26', 'Mumbai', 6500),
(5, 'Hardik','27', 'Bhopal', 8500),
(6, 'Komal', '22', 'Hyderabad', 9000),
(7, 'Muffy', '24', 'Indore', 5500);表创建如下 −
The table is created as −
ORDERS Table −
ORDERS Table −
我们使用以下 CREATE TABLE 查询创建另一个名为 ORDERS 的表，其中包含客户下的订单详细信息：
We create another table named ORDERS containing details of orders made by the customers, using the following CREATE TABLE query −
CREATE TABLE ORDERS (
OID INT NOT NULL,
DATE VARCHAR (20) NOT NULL,
CUSTOMER_ID INT NOT NULL,
AMOUNT DECIMAL (18, 2)
);使用 INSERT 语句，按照如下方式插入值到 ORDERS 表中 −
Using the INSERT statement, insert values into the ORDERS 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);该表显示如下：
The table is displayed as follows −
Joining Tables −
Joining Tables −
现在我们编写一个 SQL 查询来联接这两个表。此查询将从表 CUSTOMERS 中选择所有客户，并将从 ORDERS 中提取他们所做的相应数量的订单。
Now we write an SQL query to join these two tables. This query will select all the customers from table CUSTOMERS and will pick up the corresponding number of orders made by them from the ORDERS.
SELECT a.ID, a.NAME, b.DATE, b.AMOUNT
FROM CUSTOMERS a, ORDERS b
WHERE a.ID = b.CUSTOMER_ID;