Use full outer join to join multiple tables

Today, we help you modify a report, and design the warehouse receiving and warehouse receiving operations in it. We need to list the Daily portal information of each product.

 

This may happen:

 

1. The product has data in the warehouse receiving table, but there is no data in the warehouse receiving table.

2. There is data in the product warehouse receiving table, but there is no data in the warehouse picking table

 

Therefore, this report directly uses inner join or left
Join/right join joins two tables without obtaining the desired data. At that time, the idea of the report developer was to first find out qualified products from the warehouse receiving table and warehouse receiving table, and then use left join to connect the warehouse receiving table and warehouse receiving table to obtain the final data. Of course, this is okay, but you do not need an intermediate table. You only need to use full
Outer Join can obtain the required data. (Full outer join is rarely used)

 

Here is an example:

 

-- Create a test table and create test data:

 

Create Table A (idint, qtyint)

Create Table B (idint, qtyint)

 

INSERT INTO
Values (1, 10)

INSERT INTO
Values (2, 20)

 

Insert into B
Values (2, 30)

Insert into B
Values (3, 40)

 

-- Expected final result:

 

-- Use left join

Select a. ID
As inbound, A. qtyas inqty, B. Idas outbound, B. qtyas
Outqty from a leftouterjoin bon A. ID =
B. ID

 

 

-- Use right join

Select a. ID
As inbound, A. qtyas inqty, B. Idas outbound, B. qtyas
Outqty from a rightouterjoin bon A. ID =
B. ID

 

 

-- Use full outer join

Select a. ID
As inbound, A. qtyas inqty, B. Idas outbound, B. qtyas
Outqty from a fullouterjoin bon A. ID =
B. ID

 

 

You can see that all data in the two data sets involved in the connection is returned, regardless of whether they have matched rows. In terms of function, it is equivalent to performing left Outer Join and right outer join on the two data sets respectively. Then, the preceding two result sets are combined into a result set by removing duplicate rows.

 

Simply put, it is the result set of left join union right join. The following statement is identical to the result of fullouter join.

 

Select a. ID
As inbound, A. qtyas inqty, B. Idas outbound, B. qtyas
Outqty from a leftouterjoin bon A. ID =
B. ID

Union

Select a. ID
As inbound, A. qtyas inqty, B. Idas outbound, B. qtyas
Outqty from a rightouterjoin bon A. ID =
B. ID