SQL is required. Note: Chapter 13th creates an advanced join. SQL is required.

SQL is required. Note: Chapter 13th creates an advanced join. SQL is required.
13.1 use table alias

In addition to column names and calculated fields, the SQL statement allows you to specify aliases. There are two main reasons for doing this:
(1) Shorten SQL statements
(2) The same table can be used multiple times 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';

Oracle does not support AS: Oracle does not support the AS keyword. To use an alias in Oracle, you can simply specify a column name without using.

13.2 use different types of connections 13.2.1 auto-join

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';

Use auto join instead of subquery

13.2.2 natural connections

Whenever a table is joined, at least one table (joined column) should be listed ). The standard join returns all data, and even the same column appears multiple times. Natural joins exclude multiple occurrences 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 connections

Many joins associate rows in one table with rows in another table. But sometimes it is necessary to include those rows without associated rows. Join contains rows that are not associated with the relevant table. This type of join is called an external join.
Retrieve all users and their orders
Internal Link

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

External connections, including those without orders

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

When using the outer join syntax, you must use the RIGHT or LEFT keyword to specify the table containing all its rows (RIGHT indicates the table on the RIGHT of the outer join, LEFT indicates the table on the LEFT of outer join ).
The SQL Server supports a simplified external join syntax.

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

* = Left join, = * right join
The outer join syntax also has another form (used only by Oracle). It needs to use the (+) Operator after representation.

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

Full outer join: searches all rows in two tables and associates them with the rows that can be joined. Unlike the left Outer Join or right Outer Join (they contain non-correlated rows from a table), the full outer join contains non-correlated 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 use a join with aggregate Functions

Inner join

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 Join

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 use join and join conditions

Some key points about connection and use:
(1) Pay attention to the join type used. Generally, we use internal connections, but they are also valid.
(2) For the exact join syntax, check the specific documentation.
(3) ensure that correct join conditions are used; otherwise, incorrect data is returned.
(4) The join conditions should always be provided; otherwise, the Cartesian product will be obtained.
(5) A join can contain multiple tables, and different Join types can be used for each join. Although this is legal and useful in general, we should test each link before testing them together. This will make troubleshooting easier.