You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
Copy file name to clipboardExpand all lines: Database Articles/Advanced SQL Joins/04 - Equi, Theta, and Natural Joins.md
+7-9Lines changed: 7 additions & 9 deletions
Display the source diff
Display the rich diff
Original file line number
Diff line number
Diff line change
@@ -1,22 +1,20 @@
1
1
# Equi, Theta, and Natural Joins
2
2
3
-
Equi, theta, and natural joins were introduced by E.F. Codd in his seminal work on defining relational algebra, which serves as the foundation for SQL.
4
-
5
3
SQL is considered a lenient interpretation of relational algebra because it deviates from the strict mathematical principles of relational algebra in some ways. While relational algebra provides a rigorous mathematical foundation for relational database management, SQL is a more practical, user-friendly language for querying and manipulating data in relational databases.
6
4
7
5
SQL has added various features and capabilities beyond those found in relational algebra, such as aggregate functions, subqueries, and the ability to manipulate data directly. It also provides a way to work with NULL markers, which are not part of the mathematical model of relational algebra. Additionally, SQL uses a syntax that is more accessible and easier to read than the mathematical notation used in relational algebra.
8
6
9
7
However, the basic principles of relational algebra still form the basis of SQL, and many SQL operations can be directly mapped to relational algebra operations. This means that understanding relational algebra can help to understand SQL more deeply and improve the ability to write effective and efficient SQL queries. Nevertheless, SQL remains a lenient interpretation of relational algebra, as it deviates from the mathematical principles to provide a practical and user-friendly way to work with relational databases.
10
8
11
-
We will first look at equi and theta-joins, and then move on to the natural join.
12
-
13
9
----------------------------------
14
10
15
11
#### Equi and Theta-joins Overview
16
12
17
-
* Equi-joins look for equality in the join condition. It is a join operation that combines rows from two or more tables based on a matching value in one or more columns. This matching value is known as the join condition and is an equality condition. The term equi comes from the Latin word aequus, meaning equal.
13
+
* A theta-join is a join with an explicit join predicate based on a binary operator. These operators include both comparison and logical operators. Theta-joins can be broken into equi-joins which look for equality, and non-equi-joins are joins that use an operator other than equality.
14
+
15
+
* Equi-joins look for equality in the join condition. It is a join operation that combines rows from two or more tables based on a matching value in one or more columns. This matching value is known as the join condition and is an equality condition. The term "equi" comes from the Latin word aequus, meaning equal.
18
16
19
-
* A theta-join, also known as a non-equi-join, is a join that uses an operator other than equality. These operators include both comparison and logical operators. The term "theta" is used to denote any non-equality comparison. The terms equi-join and theta-join was coined by E.F. Codd in his seminal work on defining relational algebra, which serves as the foundation for SQL.
17
+
* A non-equi-join is a join that uses an operator other than equality. It is the opposite of an equi-join.
SQL has the following operators that can be used to join tables.
@@ -37,7 +35,7 @@ SQL has the following operators that can be used to join tables.
37
35
|| IS DISTINCT FROM | Treats NULLs as known values for comparing equality |
38
36
|| IS NOT DISTINCT FROM | Treats NULLs as known values for comparing equality |
39
37
40
-
* Logical operators test for the truth of some condition. Logical operators, like comparison operators, return a Boolean data type with a value of **TRUE**, **FALSE**, or **UNKNOWN**.
38
+
* Logical operators test for the truth of some condition. Like comparison operators, logical operators return a Boolean data type with a value of **TRUE**, **FALSE**, or **UNKNOWN**.
41
39
42
40
* Comparison operators test whether two expressions are the same. Comparison operators can be used on all expressions except expressions of the `text`, `ntext`, or `image` data types.
Theta-joins looks for any non-equality comparison. Sometimes this join is called a non-equi-join. They can be used with `INNER`, `OUTER`, `FULL OUTER`, and `CROSS JOINS`.
86
+
#### Non-equi-joins
87
+
Non-equi-joins look for any non-equality comparison. They can be used with `INNER`, `OUTER`, `FULL OUTER`, and `CROSS JOINS`.
90
88
91
89
Here are some examples that you may not have realized are possible.
0 commit comments