Oracle SQL Development Study Notes

I. Inner join)
The connection condition of the where statement first concatenates all tables to be connected into a "big table". If there are other conditions in the middle, you can use the restrictions, after reducing the number of columns and number of rows in a table, you can continue to connect to other tables. At the end of the connection, some query conditions are used to limit the number of rows in this "big table. If any table in the connection does not match the connection condition, the row of data will not appear in the "large table. Example:
-- Old syntax
Select B. MC, A. zxyy_mc
From dm_zxyy A, dj_ztzx B
Where B. yy_dm = A. zxyy_dm -- sql-92 Standard
Select B. MC, A. zxyy_mc
From dm_zxyy a inner join dj_ztzx B
On a. zxyy_dm = B. yy_dm the two SQL statements have the same functions and different standard SQL syntaxes. 2. Outer Join)
There are three types of Outer Join: left Outer Join, right outer join, and full outer join. The inner join can delete some rows in the original table, and the outer join can retain some of these rows. (1) left Outer Join
1. Write the join conditions in the WHERE clause. The addition symbol (+) in parentheses is written on the right of the join condition. This specifies an external connection. When there are multiple clauses in the join condition, the addition symbol must be written on the right of each clause. '+' Is equivalent to 10 thousand rows for the right table to match the left table for 'inner connection '. Example:
-- Old syntax
Select a. *, B .*
From tab1 A, tab2 B
Where a. Sn = B. Sn (+)
A. TX> B. TX (+)
Order by A. Sn

-- Sql92 Standard
Select a. *, B .*
From tab1 a left Outer Join tab2 B
On a. Sn = B. Sn (+)
A. TX> B. TX (+)
Order by A. Sn the two SQL statements have the same functions and the standard SQL syntax is different. (2) Right Outer Join
It is similar to the left Outer Join and is not described. (3) All external connections
1. The full outer connection is actually a combination of the left and right connections and then the result set.
2. the columns of the Union result set must be the same.
3. Full outer join Syntax:
Left Outer Join
Union
Outer right connection
4. Union can have only one order by clause, and it must be in the last row of union. This clause sorts all Union rows in the specified order.
5. When union is used, duplicate rows are deleted, regardless of whether the duplicate rows come from the same table.

Iii. Union and Union all
(1) Difference between Union and join
1. Union merges rows from two tables into one table, without making any changes to these rows and deleting duplicate rows, regardless of whether these duplicate rows come from the same table. Example:
-- Union Joint Query
Select A1, B1, C1
From tab1
Union
Select A2, B2, C2
From tab2 -- connection Query
Select T1. *, T2 .*
From tab1 T1, tab2 T2
Where a. ID = B. ID (2). Difference between Union and Union all
1. Similar to Union, Union all does not delete duplicate rows or Automatically sorts rows.
2. If Union all is less than Union computing resources, use Union all whenever possible.