1, Multi table query

select * from employees;--107
select * from departments;--27
select * from employees,departments ;--27 * 107

These phenomena are called Cartesian sets

1. Cartesian set

① Cartesian assembly is generated under the following conditions:

Omit connection conditions
 Invalid connection condition
 All rows in all tables are connected to each other

② To avoid Cartesian sets, you can add valid join conditions to WHERE.

2, Oracle connection

Use joins to query data in multiple tables.

SELECT  table1.column, table2.column
FROM  table1, table2
WHERE  table1.column1 = table2.column2;

1. = = write connection condition = = in WHERE clause.
2. If there are the same columns in the table, prefix the column name with the table name

1. Equivalent connection

--oracle Equivalent connection of 
--When two tables are queried, for the fields in both tables, you must indicate which table name the queried field is from.Field name to specify
-- In equivalent connection, if a table exists null Value and the record does not exist in another table
-- Can avoid the production of Cartesian product
select first_name,last_name,employees.department_id,department_name 
from employees,departments
where  employees.department_id = departments.department_id;

In the case of multi table query, the alias of an individual named table for each table cannot be used as

2. Multiple join conditions AND and operators

1. Distinguish duplicate column names

1. ==Use the table name prefix to distinguish the same columns in multiple tables==
2. Columns with the same column name in different tables can be distinguished by the alias of the table

2. Alias of table

Use aliases to simplify queries.
Using table name prefix can improve the efficiency of execution

select first_name,last_name,dept.department_id,department_name 
from employees  emp ,departments dept
where emp.department_id = dept.department_id;

3. Multi table associated query

--Exercise: find out the employee's last_name, department_name, city ,country,region_name
select e.last_name,d.department_name,l.city,c.country_name,r.region_name
from employees e  ,departments d,locations l,countries c ,regions r
where e.department_id = d.department_id and d.location_id = l.location_id and l.country_id = c.country_id and c.region_id = r.region_id;

Connect n tables, at least n-1 connection conditions are required. For example, to join three tables, you need at least two join conditions.
Note: in multi table Association query, first make sure that your data comes from several tables and the association relationship between them
User role table role permission relationship table permission table

4. Non equivalent connection

--Non equivalent link 
--Query employee's basic information and position name according to employee's salary scott User tables
select e.ename,e.job,e.sal ,s.grade
from emp e,salgrade s
where e.sal between s.losal and s.hisal;

5. external connection

1. Internal connection and external connection

1. Inner join: merges rows of two or more tables with the same column. The result set does not contain rows that do not match one table with another
2. Outer join: two tables return not only the rows that meet the join conditions, but also the rows that do not meet the conditions in the left (or right) table. This join is called the left (or right) outer join. When there is no matching row, the corresponding column in the result table is empty (NULL). The WHERE clause condition of the external connection is similar to the internal connection, but the column of the table without matching row in the connection condition should be followed by the external connection operator, that is, the plus sign (+ surrounded by parentheses)
   Special note: this link operator is unique to oracel
   ① Left outer link:

select first_name,last_name,dept.department_id,department_name 
from employees  emp ,departments dept
where emp.department_id = dept.department_id(+);

Use = in the where condition as the limit (+) to write on the right as the left outer link to write on the left as the right outer link
In the left outer connection, the left table is the primary table and the right table is the secondary table, which always ensures the integrity of the primary table. If there are no records matching the primary table in the secondary table, the secondary table is filled with empty records
In the right outer join, the right table is the primary table and the left table is the slave table, which always ensures the integrity of the primary table. If there are no records matching the primary table in the slave table, the slave table is filled with empty records
② Right outer connection

--When two tables are queried, for the fields in both tables, you must indicate which table name the queried field is from.Field name to specify
-- In equivalent connection, if a table exists null Value and the record does not exist in another table
select first_name,last_name,dept.department_id,department_name 
from employees  emp ,departments dept
where emp.department_id(+) = dept.department_id;

2. Self connection (internal link of a table)

--Self join
--Query all employees' information and their manager's name
--Now the data comes from one table, but we can think of one table as two tables
--All are employees One for employees and one for Managers
select * from employees;

select e1.employee_id empid ,e1.last_name empname, e1.manager_id mid ,e2.employee_id manager_id, e2.last_name manage_name
from employees e1,employees e2
where e1.manager_id = e2.employee_id(+);

6. Use SQL: 1999 syntax to connect

For all relational databases

SELECT  table1.column, table2.column
FROM  table1
[CROSS JOIN table2] |
[NATURAL JOIN table2] |
[JOIN table2 USING (column_name)] |
[JOIN table2
  ON(table1.column_name = table2.column_name)] |
[LEFT|RIGHT|FULL OUTER JOIN table2
  ON (table1.column_name = table2.column_name)];

1. Cross set

1. CROSS JOIN clause is used to generate a cross set of joined tables.
2. The cross set and Cartesian set are the same.

select *  from  employees cross join departments;

2. Natural connection

1. The NATURAL JOIN clause creates an equivalent join based on the same name column in two tables.
2. Query the data satisfying the equivalence condition in the table.
3. If only the column names are the same and the data types are different, an error will occur.
   Returns and, intersection, rather than or, union, of columns with the same name in both tables. That is, for example, the employee class and department class both have department ID and manager ID, which return the same result.

select * from emp natural join dept;
select * from dept natural join emp;
select * from dept;
SELECT department_id, department_name,
       location_id, city
FROM   departments
NATURAL JOIN locations ;

3. Use the USING clause to create a connection

1. When creating an equivalent join in the NATURAL JOIN clause, you can use the USING clause to specify the columns that need to be used in the equivalent join.
2. USING using allows you to select when more than one column meets the criteria.
3. Do not prefix or alias the selected columns with table names.
4. JOIN and USING clauses are often used at the same time.

--Query all employee and department information
select * from employees join departments using(department_id);

4. Create a connection using the ON Clause (common)

1. In a natural join, columns with the same name are the join conditions.
2. You can use the ON clause to specify additional connection conditions.
3. This connection condition is separate from other conditions.
4. The ON clause makes the statement more readable.

--ON clause
select * from employees e join departments d on(e.department_id = d.department_id);

Creating a multi table join using the ON Clause

--Use ON Clause to create a multi table join
select * from employees  e
join departments  d on (e.department_id = d.department_id)
join locations l on (d.location_id = l.location_id);

7. Internal connection and external connection

1. In SQL: 1999, internal connections only return data that meets the connection conditions
2. In the process of joining, two tables return not only the rows that meet the join conditions, but also the rows that do not meet the conditions in the left (or right) table. This kind of join is called the left (or right) outer join.
3. In the process of joining, two tables return not only the rows that meet the join conditions, but also the rows that do not meet the conditions. This kind of join is called full outer join.

1. Left outer connection

--Left outer join
select * from employees e
left outer join  departments d
on (e.department_id = d.department_id);

--Left outer join
select * from employees e
left join  departments d
on (e.department_id = d.department_id);

2. Right outer connection

--Right outer join
select * from employees e
right outer join  departments d
on (e.department_id = d.department_id);

3. Full external connection

--Full (full) external connection
select * from employees e
full outer join  departments d
on (e.department_id = d.department_id);