0008 notes 04-subquery, join and combined query, and 04-

0008 notes 04-subquery, join and combined query, and 04-

1. subquery: Nested query. The results of one query are used as the conditions for another query.

For example, to list the IDs, names, and contacts of all customers who have ordered the item "RGAN01", take the following steps:

(1) Find all order numbers ordered by "RGAN01" from orderitems

(2) Use the order number (1) to locate the customer ID in orders.

(3) Use the customer ID (2) to identify the customer's name and contact in mers MERs

When writing SQL statements, start from step 1. the SQL code is as follows:

SELECT cust_id,cust_name,cust_contactFROM customersWHERE cust_id IN(SELECT cust_id                          FROM orders                          WHERE order_num IN(SELECT order_num                                                          FROM orderitems                                                           WHERE prod_id='RGAN01'));

The running result is as follows:

SELECT MERs. cust_id, cust_name, cust_contact FROM customers, orders, orderitems WHERE customers. cust_id = orders. cust_id AND orders. order_num = orderitems. order_num AND prod_id = 'rgan01 ';

Note: 1. if multiple tables have the same column name (for example, customers and orders both have "cust_id"), you must specify the name of the table; 2. the WHERE clause is indispensable. Otherwise, a Cartesian product is formed. Each row in the left and right tables is connected. The total number of rows is a concatenation of the number of rows in the Table. 3. the connection must be correct.

SELECT vend_name, prod_name, prod_priceFROM vendors inner join productsON vendors. vend_id = products. vend_idORDER BY vendors. vend_id;

SELECT c1.cust _ name, c1.cust _ contact, c1.cust _ emailFROM MERs c1, customers c2WHERE c1.cust _ name = c2.cust _ name AND c2.cust _ contact = 'Jim Jones ';

SELECT customers. cust_id, orders. cust_id, orders. order_numFROM customers left outer join orders ON customers. cust_id = orders. cust_id;

SELECT MERs. cust_id, COUNT (orders. order_num) AS num_ordFROM customers left outer join orders ON customers. cust_id = orders. cust_idGROUP BY MERs. cust_id;

SELECT cust_name, cust_contact, cust_emailFROM customersWHERE cust_state IN ('il ', 'in', 'mi') OR cust_name = 'fun4all ';

Change to Combined Query:

SELECT cust_name,cust_contact,cust_emailFROM customersWHERE cust_state IN('IL','IN','MI')UNIONSELECT cust_name,cust_contact,cust_emailFROM customersWHERE cust_name='Fun4All';

Others: the UNION clause is used to exclude duplicate rows by default. If the clause is not used, the union all clause is used;

Order by statement for sorting UNION results should be placed at the end

UNION rules: 1. You can combine multiple SELECT statements to determine whether to limit the number depends on the DBMS;

2. Each SELECT statement must contain the same columns, expressions, and aggregate functions;

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