0008 "SQL must know" note 04-subqueries, joins, and combinatorial queries

1, sub-query: is a nested query, with the results of one query as a condition of another query.

For example, to list the ID, name, and contact of all the customers who ordered the item "RGAN01", the following steps are required

(1) Find all order numbers for "RGAN01" from OrderItems

(2) Use the order number (1) to find the customer's ID in orders

(3) Identify the customer's name and contact in customers using the customer ID (2)

When writing SQL statements, start with the 3rd step, and the SQL code is as follows:

SELECTcust_id,cust_name,cust_contact fromCustomersWHEREcust_idinch(SELECTcust_id fromordersWHEREOrder_numinch(SELECTOrder_num fromOrderItemsWHEREprod_id='RGAN01'));

The results of the operation are as follows:

Instruction for use: 1. Select can only query a single column as a subquery, 2. The number of nested subqueries is not limited; 3. Sub-queries are less efficient

2. Join: Associate a table in a SELECT statement. Usage: Specifies the tables to join and how they are joined. The above list is joined by the following method:

SELECTfrom WHERE customers.cust_id=orders.cust_id     and Orders.order_num=orderitems.order_num  and prod_id=  'RGAN01';

Note: 1. You have the same column names in multiple tables (for example, customers and orders have "cust_id"), you must clearly indicate which table is the column name; 2. Where the sentence is not small, otherwise it will form a Cartesian product, the left and right table each row will be joined, total number = table row number of the connection; 3. The connection must be correct

3. Use subqueries as calculated fields: Understanding of this section cannot be

4, Inner joins: (2) is used in the inner join (also known as the equivalent join, the two table columns of the join is equal), the following example joins 2 tables: List each supplier's name, the products supplied, the price.

SELECT Vend_name,prod_name,prod_price  from INNER JOIN  Products  on vendors.vend_id=products.vend_idORDER by vendors.vend_id;

5, self-join: You can join 2 different tables, you can also join 2 identical tables. For example, list the email addresses of other people under the same client as "Jim Jones":

SELECT C1.cust_name,c1.cust_contact,c1.cust_email  from Customers c1,customers C2 WHERE c1.cust_name= and c2.cust_contact='Jim Jones  ';

The C1,C2, which is the alias of the table, is separated by a space after the table name.

6, outer joins: left, right, whole. An inner join can list the rows that are associated with a row, and sometimes we need to list those rows that have no associated rows, which requires an outer join. For example, list each customer's order number, including the customer who has no order:

SELECT Customers.cust_id,orders.cust_id,orders.order_num  from customers left  OUTERJOIN  orders   on customers.cust_id=orders.cust_id;

Description: The left OUTER join lists rows that have no associated rows (cust_id=1000000002) in the right-hand table (customers).

Left OUTER joins: Right OUTER join; Fully join: Full OUTER join. About is relative, the join word before and after the two tables in order to change, the left and right to change a bit.

7. Combine aggregation functions with joins

SELECT customers.cust_id,COUNT as num_ordfrom leftOUTER  JOIN  orders  on customers.cust_id=orders.cust_id   GROUP by customers.cust_id;

8. Use of joins NOTE: Do not join unnecessary tables, the more tables you join, the more performance you fall

9. Combination query: Combine multiple SELECT statements together

SELECT Cust_name,cust_contact,cust_email  from Customers WHERE inch ('IL','in','MI' )  OR cust_name='fun4all';

Change to group query:

SELECTCust_name,cust_contact,cust_email fromCustomersWHERECust_stateinch('IL','inch','MI')UNIONSELECTCust_name,cust_contact,cust_email fromCustomersWHERECust_name='Fun4all';

Other: Union By default excludes duplicate rows, and if not excluded, union all;

The order BY statement that sorts the union result is put to the last

Union rules: 1. Multiple SELECT statements can be grouped together, whether the number is limited, depending on the DBMS;

2. Each select must contain the same columns, expressions, aggregate functions;

3. The data type of the same column must be compatible

0008 "SQL must know" note 04-subqueries, joins, and combinatorial queries