SQL connections (2) -- SQL Reading Notes, SQL Reading Notes

SQL connections (2) -- SQL Reading Notes, SQL Reading Notes

 

Chapter 1 in SQL Learning Guide 1.4 outer connections In multi-table join, the connection condition may not match all rows in the table. For example, when the account table is connected to the customer table, the value of the cust_id column in the account table cannot match the value of the cust_id column in the customer table. If the inner join fails to match, the matching failure result is not placed in the returned result set, if we need to emphasize that all rows in a table must be returned, the number of rows in the table determines the number of rows in the returned result set. 1) left Outer JoinKeywords: left outer join & left join, left indicates that the table on the left is connected to determine the number of rows in the result set, and the right side is only responsible for providing the matching column value. Example 1. 1) query all commercial customer accounts-this is the inner connection SELECT. account_id, B. cust_id, B. 'name' FROM account a inner join business B ON. cust_id = B. cust_id; Result:

2) query all customers, but if they are commercial customers, return their customer names.

SELECT a. account_id, a. cust_id, B. 'name' FROM account a left outer join business bON a. cust_id = B. cust_id; Result:

3) query all customers, but if they are private customers, return their customer names.

SELECT. account_id,. cust_id, CONCAT (I. fname, '', I. lname) AS gustname FROM account a inner join individual I ON. cust_id = I. cust_id; Result:

2) Right Outer Join

Keywords: right outer join & rigoal join. Similarly, right indicates that the right table is connected to determine the number of rows in the result set, and the left side is only responsible for providing the matching column value. 1) SELECT c. cust_id, B. 'name' FROM customer c right join business B ON c. cust_id = B. cust_id; the result is shown in

 3) Multi-Table Outer Join

1) obtain the list of all accounts, including the name of the individual customer and the company name of the business customer. SELECT. account_id, CONCAT (I. fname, '', I. lname) AS person_name, B. 'name' as business_nameFROM account aLEFT JOIN individual iON. cust_id = I. cust_idLEFT JOIN business bON. cust_id = B. shows the result of cust_id.

Select count (*) FROM account;

The result is as follows:

In the above multi-Table External join operation, the account is used as the main table to match the user in the individual table. If the match fails, the result is null. Then, the user in the business table is matched. Likewise, the match is unsuccessful, display as null. it also explains which table exists in the outer join as the primary table, so the order of the primary table is unchangeable, solving the legacy issues in the last note:The internal join has nothing to do with the table sequence, but the external join must pay attention to the location of the master table.

The preceding three tables are connected simultaneously using a subquery: SELECT indi. id, person_name, bussiness_nameFROM (SELECT. account_id AS id, CONCAT (I. fname, '', I. lname) as person_name FROM account a left join individual I ON. cust_id = I. cust_id) AS indiINNER JOIN (SELECT. account_id AS id, B. 'name' AS bussiness_name FROM account a left join business B ON. cust_id = B. cust_id) AS busiON indi. id = busi. id. The result is shown in 4) External Connection1) An issue previously achieved through internal connections: getting information from employees and their supervisors SELECT e. emp_id, CONCAT (e. fname, '', e. lname) AS emp_name, em. emp_id AS su_emp_id, CONCAT (em. fname, '', em. lname) AS superior_nameFROM employee eINNER JOIN employee emON e. superior_emp_id = em. emp_id; the result is shown in:

 

The preceding result indicates that the employee information is missing because the general manager is the highest-level employee and has no superiors. Therefore, the matching fails and is not displayed in the result set. The left join method can be used to solve this problem. SELECT e. emp_id, CONCAT (e. fname, '', e. lname) AS emp_name, em. emp_id AS su_emp_id, CONCAT (em. fname, '', em. lname) AS superior_nameFROM employee eLEFT JOIN employee emON e. superior_emp_id = em. emp_id; the result is shown in:

 

Change the left connection to the right connection. In this case, SELECT e. emp_id, CONCAT (e. fname, '', e. lname) AS emp_name, em. emp_id AS su_emp_id, CONCAT (em. fname, '', em. lname) AS superior_nameFROM employee eRIGHT JOIN employee emON e. superior_emp_id = em. emp_id; the result is shown in:

When a supervisor has n employees, n-1 pieces of data will be added to the supervisor's data, which is 18 employees, why is there 28 data records in the result set.