Module 06 · Bonus Module: Advanced SQL for Data Engineers
SQL Cheat Sheet: JOIN StatementsSQL 速查表：连接语句
Joins 连接
| Topic 主题 | Syntax 语法 | Description 描述 | Example 示例 |
|---|---|---|---|
| Cross Join 笛卡尔积连接 | SELECT column_name(s) FROM table1 CROSS JOIN table2; |
The CROSS JOIN is used to generate a paired combination of each row of the first table with each row of the second table.CROSS JOIN 用于生成第一个表每一行与第二个表每一行的配对组合。 |
SELECT DEPT_ID_DEP, LOCT_ID FROM DEPARTMENTS CROSS JOIN LOCATIONS; |
| Inner Join 内连接 | SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name; WHERE condition; |
You can use an inner join in a SELECT statement to retrieve only the rows that satisfy the join conditions on every specified table.您可以在 SELECT 语句中使用 inner join 来检索仅满足每个指定表上连接条件的行。 |
select E.F_NAME,E.L_NAME, JH.START_DATE from EMPLOYEES as E INNER JOIN JOB_HISTORY as JH on E.EMP_ID=JH.EMPL_ID where E.DEP_ID ='5'; |
| Left Outer Join 左外连接 | SELECT column_name(s) FROM table1 LEFT OUTER JOIN table2 ON table1.column_name = table2.column_name WHERE condition; |
The LEFT OUTER JOIN will return all records from the left side table and the matching records from the right table.LEFT OUTER JOIN 将返回左侧表的所有记录以及右侧表中匹配的记录。 |
select E.EMP_ID,E.L_NAME,E.DEP_ID,D.DEP_NAME from EMPLOYEES AS E LEFT OUTER JOIN DEPARTMENTS AS D ON E.DEP_ID=D.DEPT_ID_DEP; |
| Right Outer Join 右外连接 | SELECT column_name(s) FROM table1 RIGHT OUTER JOIN table2 ON table1.column_name = table2.column_name WHERE condition; |
The RIGHT OUTER JOIN returns all records from the right table, and the matching records from the left table.RIGHT OUTER JOIN 返回右表的所有记录，并返回左表中匹配的记录。 |
select E.EMP_ID,E.L_NAME,E.DEP_ID,D.DEP_NAME from EMPLOYEES AS E RIGHT OUTER JOIN DEPARTMENTS AS D ON E.DEP_ID=D.DEPT_ID_DEP; |
| Full Outer Join 全外连接 | SELECT column_name(s) FROM table1 FULL OUTER JOIN table2 ON table1.column_name = table2.column_name WHERE condition; |
The FULL OUTER JOIN clause results in the inclusion of rows from two tables. If a value is missing when rows are joined, that value is null in the result table.FULL OUTER JOIN 子句导致两个表中的行被包含。当行连接时如果某个值缺失，那么在结果表中该值为 null。 |
select E.F_NAME,E.L_NAME,D.DEP_NAME from EMPLOYEES AS E FULL OUTER JOIN DEPARTMENTS AS D ON E.DEP_ID=D.DEPT_ID_DEP; |
| Self Join 自连接 | SELECT column_name(s) FROM table1 T1, table1 T2 WHERE condition; |
A self join is regular join but it can be used to joined with itself.A self join 是常规连接，但它可以用来与自己连接。 |
SELECT B.* FROM EMPLOYEES A JOIN EMPLOYEES B ON A.MANAGER_ID = B.MANAGER_ID WHERE A.EMP_ID = 'E1001'; |
Joins in MySQL using phpMyAdmin 使用 phpMyAdmin 在 MySQL 中进行连接
| Topic 主题 | Syntax 语法 | Description 描述 | Example 示例 |
|---|---|---|---|
| Full Outer Join 全外连接 | SELECT column_name(s) FROM table1 LEFT OUTER JOIN table2 ON table1.column_name = table2.column_name WHERE conditionUNIONSELECT column_name(s)FROM table1RIGHT OUTER JOIN table2ON table1.column_name = table2.column_nameWHERE condition |
The UNION operator is used to combine the result-set of two or more SELECT statements.UNION operator 用于组合两个或多个 SELECT 语句的结果集。 |
select E.F_NAME,E.L_NAME,D.DEP_NAME from EMPLOYEES AS E LEFT OUTER JOIN DEPARTMENTS AS D ON E.DEP_ID=D.DEPT_ID_DEPUNIONselect E.F_NAME,E.L_NAME,D.DEP_NAMEfrom EMPLOYEES AS ERIGHT OUTER JOIN DEPARTMENTS AS D ON E.DEP_ID=D.DEPT_ID_DEP |