Skip to content

Commit 7a3af30

Browse files
smpetersgithubsmpetersgithub
authored
Update 01 - Introduction.md
1 parent 5a8a5b9 commit 7a3af30

1 file changed

Lines changed: 16 additions & 14 deletions

File tree

‎Database Articles/Advanced SQL Joins/01 - Introduction.md‎

Lines changed: 16 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -18,20 +18,22 @@ Here is a brief description of each type of join:
1818
| Type | Join | Description |
1919
|-------------|------------------|-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
2020
| Logical | INNER JOIN | An INNER JOIN returns only the rows with matching values in both tables. |
21-
| Logical | OUTER JOIN | An outer join (LEFT OUTER JOIN/RIGHT OUTER JOIN) returns all the rows from one table and any matching rows from the other table. If there is no match, the result will contain NULL markers. |
22-
| Logical | FULL OUTER JOIN | A FULL OUTER JOIN returns all the rows from both tables. If there are no matching rows, the result will contain NULL markers. |
23-
| Logical | CROSS JOIN | A CROSS JOIN returns the Cartesian product of the two tables and returns every possible combination of rows from the two tables. |
24-
| Physical | NESTED LOOP JOIN | Nested loop join is a type of join algorithm that compares each row of one table with all rows of another table. |
25-
| Physical | HASH JOIN | Hash join is a join algorithm that uses a hash table to quickly match rows from one table with rows from another table. |
26-
| Physical | MERGE SORT JOIN | Merge sort join is a join algorithm that sorts both tables on the join column and then merges the sorted rows together. |
27-
| Descriptive | COMPLEX JOIN | A complex join is a type of join operation that combines multiple tables using various comparison operators and often includes subqueries and aggregate functions to retrieve and combine data from different tables. |
28-
| Descriptive | SELF-JOIN | A self-join joins a table to itself, using the same table twice with different aliases. |
29-
| Model | SEMI-JOIN | A semi-join returns only the rows from the first table with matching values in the second table. |
30-
| Model | ANTI-JOIN | An anti-join returns only the rows from the first table that do not have matching values in the second table. |
31-
| Model | EQUI-JOIN | An equi-join returns only the rows where the values in the specified columns of both tables are equal. |
32-
| Model | THETA-JOIN | A theta-join is a flexible type of join that allows you to join tables based on any type of condition, not just an equality condition. |
33-
| Model | NON-EQUI-JOIN | Interchangeable with theta-join. Some texts use the term theta-join, and others use non-equi-join. |
34-
| Model | NATURAL JOIN | A NATURAL JOIN returns the rows where the values in the specified columns of both tables are equal and the column names are the same. |
21+
| Logical | OUTER JOIN | An outer join (LEFT OUTER JOIN/RIGHT OUTER JOIN) returns all the rows from one table and any matching rows from the other table. If there is no match, the result will contain NULL markers. |
22+
| Logical | FULL OUTER JOIN | A FULL OUTER JOIN returns all the rows from both tables. If there are no matching rows, the result will contain NULL markers. |
23+
| Logical | CROSS JOIN | A CROSS JOIN returns the Cartesian product of the two tables and returns every possible combination of rows from the two tables. |
24+
| Physical | NESTED LOOP JOIN | Nested loop join is a type of join algorithm that compares each row of one table with all rows of another table. |
25+
| Physical | HASH JOIN | Hash join is a join algorithm that uses a hash table to quickly match rows from one table with rows from another table. |
26+
| Physical | MERGE SORT JOIN | Merge sort join is a join algorithm that sorts both tables on the join column and then merges the sorted rows. |
27+
| Descriptive | COMPLEX JOIN | A complex join is a join operation that combines multiple tables using various comparison operators and often includes subqueries and aggregate functions to retrieve and combine data from different tables. |
28+
| Descriptive | COMPOSITE JOIN | A composite join is a join that uses multiple attributes from each table to retrieve and combine data from different tables. |
29+
| Descriptive | MULTI-JOIN | A multi-join refers to a query that involves joining more than two tables together |
30+
| Descriptive | SELF-JOIN | A self-join joins a table to itself, using the same table twice with different aliases. |
31+
| Model | SEMI-JOIN | A semi-join returns only the rows from the first table with matching values in the second table. |
32+
| Model | ANTI-JOIN | An anti-join returns only the rows from the first table that do not have matching values in the second table. |
33+
| Model | THETA-JOIN | A theta-join is a join based on a binary operator (equality or inequality). Both equi and non-equi-joins are theta joins. |
34+
| Model | EQUI-JOIN | An equi-join is a join condition that involves only an equality operator. |
35+
| Model | NON-EQUI-JOIN | A non-equi-join is a join condition that involves any operator besides equality. |
36+
| Model | NATURAL JOIN | A NATURAL JOIN returns the rows where the values in the specified columns of both tables are equal and the column names are the same. |
3537

3638
---------------------------------------------------------
3739

0 commit comments

Comments
 (0)