Get data from multiple tables
Cartesian set
Cartesian assemblies are generated under the following conditions:
Omit connection conditions
Invalid connection condition
All rows in all tables are connected to each other
In order to avoid Cartesian sets, valid join conditions can be added in WHERE.
Oracle connectivity
Use joins to query data in multiple tables.
SELECT table1.column, table2.column FROM table1, table2 WHERE table1.column1 = table2.column2;
Write the join condition in the WHERE clause.
When there are the same columns in the table, prefix the column name with the table name
Equivalent connection
SQL> SELECT employees.employee_id, employees.last_name,
2 employees.department_id, departments.department_id,
3 departments.location_id
4 FROM employees, departments
5 WHERE employees.department_id = departments.department_id;
EMPLOYEE_ID LAST_NAME DEPARTMENT_ID DEPARTMENT_ID LOCATION_ID
----------- ------------------------- ------------- ------------- -----------
100 King 90 90 1700
101 Kochhar 90 90 1700
102 De Haan 90 90 1700
103 Hunold 60 60 1400
104 Ernst 60 60 1400
107 Lorentz 60 60 1400
124 Mourgos 50 50 1500
141 Rajs 50 50 1500
142 Davies 50 50 1500
143 Matos 50 50 1500
144 Vargas 50 50 1500
149 Zlotkey 80 80 2500
174 Abel 80 80 2500
Multiple connection conditions AND and operators
Distinguish duplicate column names
Use the table name prefix to distinguish the same columns in multiple tables.
Columns with the same column name in different tables can be distinguished by table aliases.
Use aliases to simplify queries.
Using table name prefix can improve execution efficiency.
SELECT e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
FROM employees e , departments d
WHERE e.department_id = d.department_id;
To connect n tables, at least n-1 connection conditions are required. For example, connecting three tables requires at least two connection conditions.
Non equivalent connection
SQL> SELECT e.last_name, e.salary, j.grade_level 2 FROM employees e, job_grades j 3 WHERE e.salary 4 BETWEEN j.lowest_sal AND j.highest_sal; LAST_NAME SALARY GRADE_LEVEL ------------------------- ---------- ----------- Olson 2100.00 A Markle 2200.00 A Philtanker 2200.00 A Gee 2400.00 A Landry 2400.00 A Marlow 2500.00 A Patel 2500.00 A Perkins 2500.00 A Colmenares 2500.00 A Sullivan 2500.00 A Vargas 2500.00 A Grant 2600.00 A
External connection
Internal and external connections
Inner join: merge rows of more than two tables with the same column. The result set does not contain rows that do not match one table with another
External join: in addition to the rows that meet the join conditions, two tables also return the rows that do not meet the conditions in the left (or right) table. This connection is called left (or right) external join. When there is no matching row, the corresponding column in the result table is null. The WHERE clause condition of the outer join is similar to the inner join, but the outer join operator should be added after the column of the table without matching row in the join condition, that is, the plus sign (+) enclosed in parentheses
Outer join syntax
External connection can be used to query data that does not meet the connection conditions.
The symbol for the outer connection is (+).
Right outer connection
SELECT table1.column, table2.column FROM table1, table2 WHERE table1.column(+) = table2.column;
Left outer connection
SELECT table1.column, table2.column FROM table1, table2 WHERE table1.column = table2.column(+);
External connection
SQL> SELECT e.last_name, e.department_id, d.department_name 2 FROM employees e, departments d 3 WHERE e.department_id(+) = d.department_id ; LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME ------------------------- ------------- ------------------------------ King 90 Executive Kochhar 90 Executive De Haan 90 Executive Hunold 60 IT Ernst 60 IT Lorentz 60 IT
Self connection
```c
SELECT worker.last_name || ' works for ' || manager.last_name
FROM employees worker, employees manager
WHERE worker.manager_id = manager.employee_id
WORKER.LAST_NAME||'WORKSFOR'||MANAGER.LAST_NAME
Cambrault works for King
Errazuriz works for King
Partners works for King
Russell works for King
Vollman works for King
Kaufling works for King
Fripp works for King
Weiss works for King
use SQL: 1999 Grammatical connection(Query data from multiple tables using joins:) ```c 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)];
Cross set (understand)
Use the CROSS JOIN clause to generate a cross set of joined tables.
Cross sets and Cartesian sets are the same.
SELECT last_name, department_name FROM employees CROSS JOIN departments ;
Natural connection
In the NATURAL JOIN clause, an equivalent join is created based on columns with the same name in two tables.
Query the data satisfying the equivalence condition in the table.
If only the column names are the same but the data types are different, an error will be generated.
Instead of "or, union", the "and, intersection" of columns with the same name in two tables is returned. That is, for example, both employee class and department class have department_id and manager_id, which returns the same result for both.
Natural connection
SQL> SELECT department_id, department_name,
2 location_id, city
3 FROM departments
4 NATURAL JOIN locations ;
DEPARTMENT_ID DEPARTMENT_NAME LOCATION_ID CITY
------------- ------------------------------ ----------- ------------------------------
10 Administration 1700 Seattle
20 Marketing 1800 Toronto
30 Purchasing 1700 Seattle
40 Human Resources 2400 London
50 Shipping 1500 South San Francisco
60 IT 1400 Southlake
70 Public Relations 2700 Munich
80 Sales 2500 Oxford
90 Executive 1700 Seattle
Use the USING clause to create a connection
When creating an equivalent join in the NATURAL JOIN clause, you can use the USING clause to specify the columns to be used in the equivalent join.
USING allows you to select when multiple columns meet the criteria.
Do not prefix or alias the selected column with the table name.
JOIN and USING clauses are often used together.
select last_name,department_name from employees natural join departments ;
select last_name,department_name from employees join departments using (department_id);
USING clause
SQL> SELECT e.employee_id, e.last_name, d.location_id
2 FROM employees e JOIN departments d
3 USING (department_id) ;
EMPLOYEE_ID LAST_NAME LOCATION_ID
----------- ------------------------- -----------
100 King 1700
101 Kochhar 1700
102 De Haan 1700
103 Hunold 1400
104 Ernst 1400
107 Lorentz 1400
124 Mourgos 1500
141 Rajs 1500
Create connection using ON Clause (common)
In natural connection, columns with the same name are used as connection conditions.
You can use the ON clause to specify additional join conditions.
This connection condition is separate from other conditions.
The ON clause makes the statement more readable.
SQL> SELECT e.employee_id, e.last_name, e.department_id,
2 d.department_id, d.location_id
3 FROM employees e JOIN departments d
4 ON (e.department_id = d.department_id);
EMPLOYEE_ID LAST_NAME DEPARTMENT_ID DEPARTMENT_ID LOCATION_ID
----------- ------------------------- ------------- ------------- -----------
100 King 90 90 1700
101 Kochhar 90 90 1700
102 De Haan 90 90 1700
103 Hunold 60 60 1400
104 Ernst 60 60 1400
107 Lorentz 60 60 1400
124 Mourgos 50 50 1500
141 Rajs 50 50 1500
142 Davies 50 50 1500
Create a multi table join using the ON Clause
SQL> SELECT employee_id, city, department_name
2 FROM employees e
3 JOIN departments d
4 ON d.department_id = e.department_id
5 JOIN locations l
6 ON d.location_id = l.location_id;
EMPLOYEE_ID CITY DEPARTMENT_NAME
----------- ------------------------------ ------------------------------
100 Seattle Executive
101 Seattle Executive
102 Seattle Executive
103 Southlake IT
104 Southlake IT
107 Southlake IT
Internal and external connections
In SQL: 1999, internal connections only return data that meets the connection conditions
In addition to the rows that meet the connection conditions, two tables also return the rows that do not meet the conditions in the left (or right) table. This connection is called left (or right) outer connection.
In addition to the rows that meet the connection conditions, the two tables also return the rows that do not meet the conditions in the two tables. This connection is called full out connection.
Left outer connection
SQL> SELECT e.last_name, e.department_id, d.department_name 2 FROM employees e 3 LEFT OUTER JOIN departments d 4 ON (e.department_id = d.department_id) ; LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME ------------------------- ------------- ------------------------------ Whalen 10 Administration Fay 20 Marketing Hartstein 20 Marketing Colmenares 30 Purchasing Himuro 30 Purchasing Tobias 30 Purchasing Grant
Right outer connection
SQL> SELECT e.last_name, e.department_id, d.department_name 2 FROM employees e 3 RIGHT OUTER JOIN departments d 4 ON (e.department_id = d.department_id) ; LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME ------------------------- ------------- ------------------------------ King 90 Executive Kochhar 90 Executive De Haan 90 Executive Hunold 60 IT Ernst 60 IT Lorentz 60 IT Mourgos 50 Shipping Rajs 50 Shipping Davies 50 Shipping 50 Shipping
Full external connection
SELECT e.last_name, e.department_id, d.department_name FROM employees e FULL OUTER JOIN departments d ON (e.department_id = d.department_id) ; LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME ------------------------- ------------- ------------------------------ King 90 Executive 10 Administratio Whalen