SQL must know note 13th Chapter create an advanced junction

13.1 Using Table Aliases

In addition to the column names and calculated fields, the aliases allow SQL to indicate aliases. There are two main reasons for this:
(1) Shorten SQL statements
(2) Allows the same table to be used more than once in a single SELECT statement.

SELECT cust_name,cust_contactFROM Customers AS C,Orders AS O,OrderItems AS OIWHERE C.cust_id = O.cust_id     AND OI.order_num = O.order_num     AND prod_id = ‘RGAN01‘;

There is no as:oracle in Oracle that does not support the AS keyword, and to use aliases in Oracle, you can simply specify the column name without as.

13.2 using different types of junction 13.2.1 self-coupling

SELECT c1.cust_id,c1.cust_name,c1.cust_contactFROM Customers AS c1,Customers AS c2WHERE c1.cust_name = c2.cust_name     AND c2.cust_contact = ‘Jim Jones‘;

Using self-joins without subqueries

13.2.2 Natural Coupling

Whenever a table is joined, there should be at least one list that is now in more than one table (the joined column). The standard join returns all data, even the same column multiple times. Natural join exclusions occur multiple times so that each column is returned only once.

SELECT C.*,O.order_num,O.order_date,OI.prod_id,OI.quantity,OI.item_priceFROM Customers AS C,Order AS O,OrderItems AS OIWHERE C.cust.id = O.cust.id     AND OI.order_num = O.order_num     AND prod_id = ‘RGAN01‘;

13.2.3 External coupling

Many junctions associate rows in one table with rows in another table. Sometimes, however, you need rows that contain no associated rows. A junction contains rows that have no associated rows in the related table. This type of junction is called an external junction.
Retrieving all users and their orders
Links within

SELECT Customers.cust_id,Orders.order_numFROM Customers INNER JOIN Orders     ON Customer.cust_id = Orders.order_id;

External connections, including those with no orders

SELECT Customers.cust_id,Orders.order_numFROM Customers LEFT OUTER JOIN Orders     ON Customer.cust_id = Orders.order_id;

When you use the outer join syntax, you must use the right or left keyword to specify a table that includes all of its rows (that is, the table to the left of the outer join, which is indicated by the outer join table).
SQL Server supports a simplified external junction syntax for additional support.

SELECT Customers.cust_id,Orders.order_numFROM Customers ,Orders     ON Customer.cust_id *= Orders.order_id;

*= left Junction, =* right Junction
The OUTER join syntax has another form (used only by Oracle), and it needs to use the (+) operator after it is indicated

SELECT Customers.cust_id,Orders.order_numFROM Customers ,Orders     ON Customer.cust_id (+) = Orders.order_id;

Full outer JOIN, he retrieves all rows from two tables and associates those rows that can be associated. Unlike left outer joins or right outer joins (they contain unrelated rows from a table), the full outer join contains non-associative rows from two tables.

SELECT Customers.cust_id,Orders.order_numFROM Customers FULL OUTER JOIN Orders     ON Customer.cust_id = Orders.order_id;

13.3 using a junction with aggregation functions

Inner coupling

SELECT Customers.cust_id,COUNT(Orders.order_num) AS num_ordFROM Customers INNER JOIN Orders     ON Customer.cust_id = Orders.order_idGROUP BY Customers.cust_id;

Outer coupling

SELECT Customers.cust_id,COUNT(Orders.order_num) AS num_ordFROM Customers LEFT OUTER JOIN Orders     ON Customer.cust_id = Orders.order_idGROUP BY Customers.cust_id;

13.4 using junctions and junction conditions

Some key points regarding connection and use:
(1) Note the type of connection used. In general we use internal joins, but the use of external junctions is also valid.
(2) for the exact junction syntax, you should review the specific documentation.
(3) Ensure that the correct connection conditions are used, otherwise incorrect data will be returned.
(4) The coupling condition should always be provided, otherwise the Cartesian product will be obtained.
(5) Multiple tables can be included in a junction, even with different junction types for each junction. While this is legal and generally useful, you should test each link separately before testing them together. This will make troubleshooting easier.

SQL must know note 13th Chapter create an advanced junction