Complex query of tables in the database

Complex query of tables in the database

Complex query of tables in the database 1. Connection query 1.0 the basic syntax format of the connection: from TABLE1 join_type TABLE2 [on (join_condition)] [where (query_condition)] TABLE1: Left table TABLE2: right table join_type: Connection type. Cross, inner join, left Outer join, and right outer join on: Set the connection condition where: filter the result of the connection query 1 Progress 1.1 cross join select * from CUSTOMER cross join ORDERS; or select * from CUSTOMER, ORDERS; select c. name, o. order_number from CUSTOMER c, ORDERS o; 1.2 inner join: Implicit inner join: (use where without the on keyword) select * from CUSTOMER c, ORDERS o where c. id = o. customer_id; explicit inner join: (use the on keyword) select * from CUSTOMER c inner join ORDERS o on c. id = o. customer_id; 1.3 outer join: left Outer Join: (return all records that meet the connection conditions, and return all other records in the left table) select * From CUSTOMER c left outer join ORDERS o on c. id = o. customer_id; right outer join: (all records meeting the connection conditions are returned, and all other records in the right table are returned.) select * from CUSTOMER c right outer join ORDERS o on c. id = o. customer_id; 2. subquery (nested query) subquery: select * from orders where customer_id = (select id from customer where name = 'zhang san '); 3. The UNION query SELECT * FROM orders WHERE price> 200 union select * FROM orders WHERE customer_id = 1; takes the UNION of the two statements and removes Repeated Records.