Name for "Concatenation Operator"(Rel. Algebra, SQL)

1. Feb 19, 2016

WWGD

Hi All,
When operating on tables T1, T2 and obtaining a table T3, one obtains rows {$r_{3k}$} in T3 that are obtained from
rows {$r_{1i}$} and {$r_{2j}$} by what I call "concatenation" . I wonder if there is a formal name for it.

Specifically, say, during an inner join between T1, T2 along the field f , we obtain a table T3 in which

the general row $r_{3k}$ in T3 is obtained by appending to a row $r_{1i}$ in T1, columns in/from rows $r_{2j}$ for which the values on f are equal to each other , i.e. if, f($r_{ij}$)=f$(r_{2k})$ we form $r_{3k}$

containing all columns on T1, together with all columns on T2 . QUESTION: Is there a formal name for the

operation of obtaining a row by appending rows from two different tables?

EDIT: Concrete Example:
Given : https://en.wikipedia.org/wiki/Relational_algebra#Natural_join

We obtain rows in the join by unioning columns in both tables according to some rules. What is the
name of this general operation by which we obtain rows in the result table by unioning rows of the
input tables?
Thanks.

Last edited: Feb 19, 2016
2. Feb 19, 2016

Staff: Mentor

I would call it "union". AFAIK is concatenation only a string operation, appending one string to another and creating a longer string. However, the definition of concatenation is by what I remember from automation theory.

Edit: http://www.tutorialspoint.com/sql/sql_tutorial.pdf

Last edited: Feb 19, 2016
3. Feb 19, 2016

WWGD

Thank you. Can you think of the relational algebra equivalent of this row operation?

4. Feb 19, 2016

Staff: Mentor

Isn't it listed on the Wiki page you quoted? There are all versions of joins explained plus the union.
(You can also switch to the German page which I found a little easier to read and before you show me a finger, no, not because of the language. Examples and formulas don't need the language they are surrounded by. And the few words needed are either already in English or are of Latin origin.)

5. Feb 19, 2016

WWGD

The page I linked to does not seem to address issues from a relational algebra perspective. And Don't worry about my giving you the finger, this http://www.hulu.com/watch/276488 [Broken] has endeared me to the German language.

Last edited by a moderator: May 7, 2017
6. Feb 19, 2016

Staff: Mentor

Hm, should I be pleased or disappointed? (US access only)

Last edited by a moderator: May 7, 2017
7. Feb 19, 2016

MrAnchovy

What is the difference between what you are describing and a natural join?

8. Feb 19, 2016

WWGD

What I am referring to is what happens at a row level: If we join , say an inner join for definiteness, T1 and T2 along T1.f =T2.f , we obtain a table T3 . I am trying to describe the process by which rows in T3 are obtained from rows in T1 and T2. We somehow apend some columns in T2 to columns in T1 . I am trying to find out the name of this "operator" that takes rows from T1, T2 and produces a row in T3. More formally, this concatenation is an operation :

ConcatRows: $r_{1i} \times r_{2j} \rightarrow r_{3k}$ that describes the general form of rows $r_{3k}$ in T3 obtained from rows in T1, T2 by using joins. I wonder if this ConcatRows function has a standard name.

9. Feb 19, 2016

Staff: Mentor

I don't get it. This is what Wiki says including the description of the set and the rest is behind the link on fibre products (pullbacks).

Natural join (⋈)

"Natural join" redirects here. For the SQL implementation, see Natural join (SQL).
Natural join ([PLAIN]https://upload.wikimedia.org/math/e/9/6/e96250edf3d71c59494dff9736902e9c.png) [Broken] is a binary operator that is written as (R S) where R and S are relations.[2] The result of the natural join is the set of all combinations of tuples in R and S that are equal on their common attribute names. For an example consider the tables Employee and Dept and their natural join:

Employee
Name EmpId DeptName
Harry 3415 Finance
Sally 2241 Sales
George 3401 Finance
Harriet 2202 Sales

Dept
DeptName Manager
Finance George
Sales Harriet
Production Charles

Employee Dept
Name EmpId DeptName Manager
Harry 3415 Finance George
Sally 2241 Sales Harriet
George 3401 Finance George
Harriet 2202 Sales Harriet

This can also be used to define composition of relations. For example, the composition of Employee and Dept is their join as shown above, projected on all but the common attribute DeptName. In category theory, the join is precisely the fiber product.

More formally the semantics of the natural join are defined as follows:

where Fun is a predicate that is true for a relation r if and only if r is a function.

Last edited by a moderator: May 7, 2017
10. Feb 19, 2016

FactChecker

The term "join" is also used to describe the operation of forming a single tuple from two tuples. The term doesn't have to be used only for entire tables. The term "concatenate" may be wrong if it implies an order of the result. You may or may not want to imply that. But "join" may have the same implication if you do not say otherwise.

11. Feb 20, 2016

WWGD

Basically, I am looking for a name for the function f on rows described by:

f(Harry 3415 Finance, Finance George)=(Harry 3415 Finance George),

and does similar for other rows. Basically we take a pair of rows and produce a third row
by appending columns in 'Employees' with other columns in 'Dept' , the rules of appending the rows depend on the choice of operation on the tables.

Last edited by a moderator: May 7, 2017
12. Feb 20, 2016

WWGD

I see, so we would refer, given tables T1, T2 "operated into" T3 (i.e., we join T1, T2 to get T3) we would refer to the rows in T3 as resulting from joining rows in T1, T2?

13. Feb 20, 2016

Staff: Mentor

I would call it a pushback or fibre product over Finance. But I guess now you want to know a term closer to databases.
One can't do it right to you

Edit: Better a fibre product over DeptName.

14. Feb 20, 2016

WWGD

Reminds of the story about the father who told his child on the child's sixth birthday: When I was your
age --I was seven!!

No, sorry to keep going over it, I was not sure I had explained myself clearly, but I like the topology-like names; there may be some interest connections.Let's hope so.

15. Feb 20, 2016

MrAnchovy

In relational algebra we use the term table interchangeably with relation. However in SQL the term table means specifically a persistent relation; the result of a SQL SELECT operation is not a table: you can use the term recordset for this.

The name of the operator you are looking for is simply a "selection". Where the selection does not apply a restriction we don't usually include it when we write a sentence in relational algebra, although it is there implicitly. So when we write $S$ in relational algebra this is equivalent to $\sigma_{true}(S)$ and we translate this into SQL as SELECT * FROM table_s. So when in relational algebra we define the relation $\bowtie : s_f = t_f$ and write $S \bowtie T$ we translate this to SELECT * FROM table_s AS s JOIN table_t AS t ON s.f = t.f.

16. Feb 20, 2016

MrAnchovy

The name for this function is an inner join on the relation Employee.DeptName = Dept.DeptName, or in (sloppy but succinct) relational algebra $Employee \bowtie Dept; \bowtie: (Employee_{DeptName} = Dept_{DeptName})$.

Last edited: Feb 20, 2016
17. Feb 20, 2016

FactChecker

I guess I can not say what term is commonly used in particular contexts.