Use full outer join to simplify SQL Code

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 to connect two tables without the expected 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, there is no problem in doing this, but no intermediate table is required. You only need to use full outer join to 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 a values (1, 10)

Insert into a 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