SQL - Full Join

SQL Full Join 通过合并两个表格形成新的表格。 合并的表格载有两个表上的所有记录,并填写了两个表上的联合国利比里亚特派团失踪人员登记册。 简言之,全面加入是一种外在结合左边和右边的结局。

让我们在两个样本表的帮助下详细理解这一概念。 下表列出了两个雇员表格,其中包括其细节。 如果我们利用联卢部队联合网络加入这些表格,两个表格的对应浏览器就被吸收,而联卢部队填充失踪记录后,会增加不匹配的浏览量。

Syntax

下面是康克全党的基本同义——

SELECT column_name(s)
FROM table_name1
FULL JOIN table_name2
ON table_name1.column_name = table_name2.column_name

在此,根据您的要求,可以说明具体条件。

Example

假设我们已经建立了一个名为“客户”的表格,其中载有客户的个人详情,包括姓名、年龄、地址和工资等,使用以下询问:

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 INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1,  Ramesh , 32,  Ahmedabad , 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2,  Khilan , 25,  Delhi , 1500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3,  kaushik , 23,  Kota , 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4,  Chaitap , 25,  Mumbai , 6500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5,  Hardik , 27,  Bhopal , 8500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6,  Komal , 22,  MP , 4500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (7,  Muffy , 24,  Indore , 10000.00 );

该表将编成:

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

让我们制定另一份表格命令,载列所发布命令的细节和日期。

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

采用INSERT声明,在本表中插入以下数值:

INSERT INTO ORDERS (OID, DATE, CUSTOMER_ID, AMOUNT)
VALUES (102,  2009-10-08 00:00:00 , 3, 3000.00);

INSERT INTO ORDERS (OID, DATE, CUSTOMER_ID, AMOUNT)
VALUES (100,  2009-10-08 00:00:00 , 3, 1500.00);

INSERT INTO ORDERS (OID, DATE, CUSTOMER_ID, AMOUNT)
VALUES (101,  2009-11-20 00:00:00 , 2, 1560.00);

INSERT INTO ORDERS (OID, DATE, CUSTOMER_ID, AMOUNT)
VALUES (103,  2008-05-20 00:00:00 , 4, 2060.00);

表格如下:

+-----+---------------------+-------------+---------+
| 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 |
+-----+---------------------+-------------+---------+

我们利用下文所述询问,试图加入两个表格的客户和订单。

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

Output

下表是:

+------+----------+--------+---------------------+
| ID   | NAME     | AMOUNT | DATE                |
+------+----------+--------+---------------------+
|    1 | Ramesh   |   NULL | NULL                |
|    2 | Khilan   |   1560 | 2009-11-20 00:00:00 |
|    3 | kaushik  |   3000 | 2009-10-08 00:00:00 |
|    3 | kaushik  |   1500 | 2009-10-08 00:00:00 |
|    4 | Chaitap |   2060 | 2008-05-20 00:00:00 |
|    5 | Hardik   |   NULL | NULL                |
|    6 | Komal    |   NULL | NULL                |
|    7 | Muffy    |   NULL | NULL                |
+------+----------+--------+---------------------+

Joining Multiple Tables with Full Join

也可使用全部的Join询问加入两个以上表格。 为此,我们按顺序将两个表格合并,直到所有表格合并。 然而,这两个表格与全部的Join合并,必须有一个共同栏目,以便能够使用这些表格和《国家公报》条款。

Syntax

The syntax to participate multi table using Full Join is given below -

SELECT column1, column2, column3…
FROM table1
FULL JOIN table2
ON table1.column_name = table2.column_name
FULL JOIN table3
ON table2.column_name = table3.column_name
.
.
.

Example

为了演示全连接，让我们考虑之前创建的示例表顾客和订单；以及新创建的表“员工”。

我们将努力利用以下询问创建就业岗位:

CREATE TABLE EMPLOYEE (
   EID INT NOT NULL,
   EMPLOYEE_NAME VARCHAR (30) NOT NULL,
   SALES_MADE DECIMAL (20)
);

现在,我们可以利用INSERT声明,将价值观纳入这一空表:

INSERT INTO EMPLOYEE VALUES (102,  SARIKA , 4500);
INSERT INTO EMPLOYEE VALUES (100,  ALEKHYA , 3623);
INSERT INTO EMPLOYEE VALUES (101,  REVATHI , 1291);
INSERT INTO EMPLOYEE VALUES (103,  VIVEK , 3426);
INSERT INTO EMPLOYEE VALUES (100,  ALEKHYA , 3456);

下表详列。

+-----+---------------+------------+
| EID | EMPLOYEE_NAME | SALES_MADE |
+-----+---------------+------------+
| 102 | SARIKA        |       4500 |
| 100 | ALEKHYA       |       3623 |
| 101 | REVATHI       |       1291 |
| 103 | VIVEK         |       3426 |
| 100 | ALEKHYA       |       3456 |
+-----+---------------+------------+

Full Join Query

让我们利用下文提出的全部联合询问,努力加入这三个表格:

SELECT CUSTOMERS.ID, CUSTOMERS.NAME, ORDERS.DATE, EMPLOYEE.EMPLOYEE_NAME
FROM CUSTOMERS
FULL JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
FULL JOIN EMPLOYEE
ON ORDERS.OID = EMPLOYEE.EID;

通过这一询问,我们试图展示客户身份、客户名称、具体日期和出售客户的雇员姓名的记录。

Output

下表是:

+----+----------+---------------------+---------------+
| ID | NAME     | DATE                | EMPLOYEE_NAME |
+----+----------+---------------------+---------------+
|  1 | Ramesh   | NULL                | NULL          |
|  2 | Khilan   | 2009-11-20 00:00:00 | REVATHI       |
|  3 | Kaushik  | 2009-10-08 00:00:00 | ALEKHYA       |
|  3 | Kaushik  | 2009-10-08 00:00:00 | ALEKHYA       |
|  3 | Kaushik  | 2009-10-08 00:00:00 | SARIKA        |
|  4 | Chaitap | 2008-05-20 00:00:00 | VIVEK         |
|  5 | Hardik   | NULL                | NULL          |
|  6 | Komal    | NULL                | NULL          |
|  7 | Muffy    | NULL                | NULL          |
+----+----------+---------------------+---------------+

Full Join with WHERE Clause

与Joins公司一起,我们用《国家公报》条款对记录进行过滤,但缺席。 让我们推测,还需要根据某种条件对记录进行过滤,我们可以利用与Joins签订的WHERE条款。

Syntax

The syntax of Full Join when used with WHERE clauses is given below -

SELECT column_name(s)
FROM table_name1
FULL JOIN table_name2
ON table_name1.column_name = table_name2.column_name
WHERE condition

Example

考虑前两个表格的客户和订单;利用WHERE条款实行某些限制,试图与他们一道使用以下完全的Join查询。

SELECT ID, NAME, DATE, AMOUNT FROM CUSTOMERS
FULL JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
WHERE ORDERS.AMOUNT > 2000.00;

Output

完全加入的WHERE条款适用后得出的表格中含有价值大于2000年的百分数。

+----+----------+---------------------+---------+
| ID | NAME     | DATE                | AMOUNT  |
+----+----------+---------------------+---------+
|  3 | Kaushik  | 2009-10-08 00:00:00 | 3000.00 |
|  4 | Chaitap | 2008-05-20 00:00:00 | 2060.00 |
+----+----------+---------------------+---------+

Conclusion

由于我们可以从本章中列举的例子中看到,如果第一个表像第二张超级表格一样行事,则完全若林声明产生与左宗主诉相同的结果;如果第二张表格与第一张表的超级套类似,则它照搬了Join权利的结果。