Multi-table query and group function of SQL statement in Orancle

1. SQL's multi-table query:

1. Left connection and right connection (unimportant party plus (+))

SELECT e.empnoree.enamered.deptnored.dnamered.dnamered.loc FROM emp esanct d WHERE e.deptno (+) = d.deptno

(+) on the left side of the equal sign, there is a right connection, and vice versa.

2. Cross-connect (CROSS JOIN produces Cartesian product)

SELECT * FROM emp CROSS JOIN dept

3. Natural connection (NATURAL JOIN) automatic association field matching

SELECT * FROM emp NATURAL JOIN dept

4. USING clause, directly related operation column (not commonly used)

SELECT * FROM emp e JOIN dept d USING (deptno) WHERE deptno=30

5. ON clause, and users specify the connection conditions themselves.

SELECT * FROM emp e JOIN dept d ON (e.deptno=d.deptno) WHERE deptno=30

Second, group functions:

1. Commonly used: COUNT () MAX () MIN () AVG () SUM ()

Use conditions, 1. GROUP BY exists in the program, and grouping conditions are specified. 2. Use grouping functions alone except for equality conditions. Grouping functions use HAVING conditions.

Formula:

SELECT {DISTINCT} column FROM table {WHERE (s)} {GROUP BY {HAVING grouping condition}} {ORDER BY sort field ASC | DESC, sort field ACS | DESC, … }

Grouping: there are duplicates on a column, which can be used in nesting

= ANY equals IN is smaller than the maximum, > ALL is larger than the maximum,