SQL - Right Join

凭单按特定条件从多个表格检索记录。 加入书包括满足特定条件的记录,外面加入一个表格,其中既有配对的,也有不匹配的浏览。

如前一章所述,左边的Join被利用寻找左桌上两个桌子的结合。 在本章中,让我们更多地讨论加入《权利》。

Right Join in SQL

。 回到右桌上所有各行,即使左桌上没有配对。 这意味着,如果《国家空间活动公约》条款与左表上的0(0o)记录相匹配;合并后仍会退回一行,但在左表的每一栏中,国家扫盲委员会的价值就有了提高。

简言之,权利结合了右桌上的所有价值,加上左桌或民族解放军在无对等的情况下的对应价值。

请注意,在实施《权利法案》之后所展示的表没有储存在数据库的任何地点。

Syntax

The following is the basic syntax of Right Joinkou in

SELECT table1.column1, table2.column2...
FROM table1
RIGHT JOIN table2
ON table1.common_field = table2.common_field;

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
RIGHT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

Output

这将产生以下结果:

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

Joining Multiple Tables with Right Join

同Join一样,Join权利组织也加入了多个表格。 然而,相比之下,第二个表格是作为一个整体而不是第一个表格退还的。

此外,表一的行数与第二位的行数相配。 如果记录不匹配,第二个表的记录数目比第一个表多,则作为头一个表的数值,重新列报。

Syntax

The syntax to participate multi table using Right Join -

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

Example

在此,让我们审议先前编制的表格“客户”和“代理人”;以及新设立的表格“Employee”。

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

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

让我们努力利用 权利Join query加入这三个表格。 具体如下:

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

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

Output

下表是:

+------+----------+---------------------+---------------+
| ID   | NAME     | DATE                | EMPLOYEE_NAME |
+------+----------+---------------------+---------------+
|    3 | Kaushik  | 2009-10-08 00:00:00 | SARIKA        |
|    3 | Kaushik  | 2009-10-08 00:00:00 | ALEKHYA       |
|    2 | Khilan   | 2009-11-20 00:00:00 | REVATHI       |
|    4 | Chaitap | 2008-05-20 00:00:00 | VIVEK         |
|    3 | Kaushik  | 2009-10-08 00:00:00 | ALEKHYA       |
+------+----------+---------------------+---------------+

Right Join with WHERE Clause

A WHERE 条款用于过滤符合其规定条件的记录。 该条款可与《权利法》一起使用,从而对记录施加某些限制。

Syntax

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

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

Example

数据库综合表中的记录可使用WHERE条款进行过滤。 考虑前两个表格的客户和订单;利用《惠予公约》条款实行某些限制,试图利用权利加入查询。

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

Output

适用权利条款后得出的表格含有价值大于1000.00的行文——

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