T Sql 简明教程

T-SQL - Joining Tables

MS SQL Server Joins 子句用于合并数据库中两个或更多表中的记录。JOIN 是通过使用每张表都通用的值来合并两张表中的字段的方法。

The MS SQL Server Joins clause is used to combine records from two or more tables in a database. A JOIN is a means for combining fields from two tables by using values common to each.

考虑以下两个表，（a）CUSTOMERS 表如下−

Consider the following two tables, (a) CUSTOMERS table is 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        MP                  4500.00
7   Muffy      24        Indore              10000.00

（b）另一张表 ORDERS 如下−

(b) Another table is ORDERS as follows −

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

让我们在我们的 SELECT 语句中连接这两个表，如下所示 −

Let us join these two tables in our SELECT statement as follows −

SELECT ID, NAME, AGE, AMOUNT
   FROM CUSTOMERS, ORDERS
   WHERE  CUSTOMERS.ID = ORDERS.CUSTOMER_ID
OR
SELECT A.ID, A.NAME, A.AGE, B.AMOUNT
   FROM CUSTOMERS A inner join  ORDERS B on A.ID = B.Customer_ID

以上命令将生成以下输出。

The above command will produce the following output.

ID   NAME      AGE    AMOUNT
2    Khilan    25     1560.00
3    kaushik   23     1500.00
3    kaushik   23     3000.00
4    Chaitali  25     2060.00

需要注意的是，联接是在 WHERE 子句中执行的。可以使用多个运算符来联接表，例如 =、<、>、<>、⇐、>=、!=、BETWEEN、LIKE 和 NOT；它们都可以用于联接表。但是，最常见的运算符是等号。

It is noticeable that the join is performed in the WHERE clause. Several operators can be used to join tables, such as =, <, >, <>, ⇐, >=, !=, BETWEEN, LIKE, and NOT; they can all be used to join tables. However, the most common operator is the equal symbol.

MS SQL Server 联接类型−

MS SQL Server Join Types −

MS SQL Server 中有多种类型的联接可用 −

There are different types of joins available in MS SQL Server −

  1. INNER JOIN − Returns rows when there is a match in both tables.

  2. LEFT JOIN − Returns all rows from the left table, even if there are no matches in the right table.

  3. RIGHT JOIN − Returns all rows from the right table, even if there are no matches in the left table.

  4. FULL JOIN − Returns rows when there is a match in one of the tables.

  5. SELF JOIN − This is used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the MS SQL Server statement.

  6. CARTESIAN JOIN − Returns the Cartesian product of the sets of records from the two or more joined tables.