Shulou(Shulou.com)05/31 Report--

This article mainly introduces "how JOIN displays data from multiple tables". In daily operation, I believe many people have doubts about how JOIN displays data from multiple tables. Xiaobian consulted all kinds of data and sorted out simple and easy operation methods. I hope to help you answer the doubts about "how JOIN displays data from multiple tables"! Next, please follow the small series to learn together!

Table Relationship Introduction

I. Grammar

SELECT field list

FROM TABLE1

[CROSS JOIN TABLE2 ] |

[NATURAL JOIN TABLE2 ] |

[JOIN TABLE2 USING (field name) ]|

[JOIN TABLE2 ON (TABLE.COLUMN_NAME = TABLE2.COLUMN_NAME) ] |

[(LEFT | RIGHT | FULL OUT) JOIN TABLE2 ON (TABLE1.COLUMN_NAME = TABLE2.COLUMN_NAME) ];

Second, natural connection

1、USING

If you do not want to make an equivalence join by referencing all columns of the joined tables with the same name, a natural join will not suffice, and you can use the USING clause at join time to set the column (reference column) name used for the equivalence join.

Table names or aliases are not allowed as prefixes on reference columns

--99 syntax

SELECT EMPNO, ENAME, SAL, EMP.DEPTNO, DNAME

FROM EMP

JOIN DEPT

USING (DEPTNO);

--92 syntax

SELECT EMPNO, ENAME, SAL, EMP.DEPTNO, DNAME

FROM EMP, DEPT

WHERE EMP.DEPTNO = DEPT.DEPTNO;

2、ON

If you want to make an equivalent join by referencing columns with different names, or if you want to set arbitrary join conditions, you can use the On clause.

SELECT EMPNO, ENAME, SAL, EMP.DEPTNO, DNAME

FROM EMP

JOIN DEPT

ON (EMP.DEPTNO = DEPT.DEPTNO);

III. Self-connection

Link yourself and yourself.

--99 syntax

SELECT WORKER.LAST_NAME EMP, MANAGER.LAST_NAME MGR

FROM EMPLOYEES WORKER

JOIN EMPLOYEES MANAGER

ON (WORKER.MANAGER_ID = MANAGER.EMPLOYEE_ID);

--92 syntax

SELECT WORKER.LAST_NAME EMP, MANAGER.LAST_NAME MGR

FROM EMPLOYEES WORKER, EMPLOYEES MANAGER

WHERE WORKER.MANAGER_ID = MANAGER.EMPLOYEE_ID;

IV. Non-equivalent connection

--99 syntax

SELECT E.LAST_NAME, E.SALARY, J.GRADE_LEVEL

FROM EMPLOYEES E

JOIN JOB_GRADES J

ON E.SALARY BETWEEN J.LOWEST_SAL AND J.HIGHEST_SAL;

--92 syntax

SELECT E.LAST_NAME, E.SALARY, J.GRADE_LEVEL

FROM EMPLOYEES E, JOB_GRADES J

WHERE E.SALARY BETWEEN J.LOWEST_SAL AND J.HIGHEST_SAL;

V. INNER| OUTER CONNECTION

1. INNER Connection

Joining two tables that return only matching rows is called an inner join.

--Query all employees and their department names

--99 syntax

SELECT LAST_NAME, DEPARTMENT_NAME

FROM EMPLOYEES E

INNER JOIN DEPARTMENTS D

ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;

--92 syntax

SELECT LAST_NAME, DEPARTMENT_NAME

FROM EMPLOYEES E, DEPARTMENTS D

WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID;

2、LEFT OUTER |RIGHT OUTER CONNECTION

Two tables in the join process in addition to return to meet the join conditions in addition to return the left (or right) table does not meet the conditions of the row, this join is called left (or right) outer join.

--Which departments have no employees, see the department name

--99 syntax

SELECT DEPARTMENT_NAME

FROM EMPLOYEES E

RIGHT JOIN DEPARTMENTS D

ON E.DEPARTMENT_ID = D.DEPARTMENT_ID

WHERE LAST_NAME IS NULL;

--92 syntax

SELECT DEPARTMENT_NAME

FROM EMPLOYEES E, DEPARTMENTS D

WHERE E.DEPARTMENT_ID(+) = D.DEPARTMENT_ID

AND LAST_NAME IS NULL;

3. FULL OUTER CONNECTION

Two tables in the join process in addition to return to meet the join conditions in addition to return two tables do not meet the conditions of the row, this join is called total outer join.

--99

SELECT E.LAST_NAME, D.DEPARTMENT_ID, D.DEPARTMENT_NAME

FROM EMPLOYEES E

FULL OUTER JOIN DEPARTMENTS D

ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;

--92

SELECT E.LAST_NAME, D.DEPARTMENT_ID, D.DEPARTMENT_NAME

FROM EMPLOYEES E, DEPARTMENTS D

ON E.DEPARTMENT_ID(+) = D.DEPARTMENT_ID(+);

VI. Cartesian product

Descartes sets are generated under the following conditions:

1. Connection conditions are missing

2, the connection conditions are incorrect

All rows in all tables are connected to each other.

--99 syntax

SELECT E.LAST_NAME, D.DEPARTMENT_ID, D.DEPARTMENT_NAME

FROM EMPLOYEES E

CROSS JOIN DEPARTMENTS D;

--92 syntax

SELECT E.LAST_NAME, D.DEPARTMENT_ID, D.DEPARTMENT_NAME

FROM EMPLOYEES E, DEPARTMENTS D;

At this point, the study of "JOIN how to display data from multiple tables" is over, hoping to solve everyone's doubts. Theory and practice can better match to help everyone learn, go and try it! If you want to continue learning more relevant knowledge, please continue to pay attention to the website, Xiaobian will continue to strive to bring more practical articles for everyone!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.