Dismiss Notice
Join Physics Forums Today!
The friendliest, high quality science and math community on the planet! Everyone who loves science is here!

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

  1. Feb 19, 2016 #1

    WWGD

    User Avatar
    Science Advisor
    Gold Member

    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. jcsd
  3. Feb 19, 2016 #2

    fresh_42

    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
  4. Feb 19, 2016 #3

    WWGD

    User Avatar
    Science Advisor
    Gold Member

    Thank you. Can you think of the relational algebra equivalent of this row operation?
     
  5. Feb 19, 2016 #4

    fresh_42

    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.)
     
  6. Feb 19, 2016 #5

    WWGD

    User Avatar
    Science Advisor
    Gold Member

    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
  7. Feb 19, 2016 #6

    fresh_42

    Staff: Mentor

    Hm, should I be pleased or disappointed? (US access only)
     
    Last edited by a moderator: May 7, 2017
  8. Feb 19, 2016 #7
    What is the difference between what you are describing and a natural join?
     
  9. Feb 19, 2016 #8

    WWGD

    User Avatar
    Science Advisor
    Gold Member

    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.
     
  10. Feb 19, 2016 #9

    fresh_42

    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 e96250edf3d71c59494dff9736902e9c.png 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 e96250edf3d71c59494dff9736902e9c.png 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:
    25685a46503caa667d2b2e4d29d99297.png
    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
  11. Feb 19, 2016 #10

    FactChecker

    User Avatar
    Science Advisor
    Gold Member

    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.
     
  12. Feb 20, 2016 #11

    WWGD

    User Avatar
    Science Advisor
    Gold Member

    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
  13. Feb 20, 2016 #12

    WWGD

    User Avatar
    Science Advisor
    Gold Member

    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?
     
  14. Feb 20, 2016 #13

    fresh_42

    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 :wink:

    Edit: Better a fibre product over DeptName.
     
  15. Feb 20, 2016 #14

    WWGD

    User Avatar
    Science Advisor
    Gold Member

    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.
     
  16. Feb 20, 2016 #15
    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.
     
  17. Feb 20, 2016 #16
    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
  18. Feb 20, 2016 #17

    FactChecker

    User Avatar
    Science Advisor
    Gold Member

    I guess I can not say what term is commonly used in particular contexts.
     
Know someone interested in this topic? Share this thread via Reddit, Google+, Twitter, or Facebook




Similar Discussions: Name for "Concatenation Operator"(Rel. Algebra, SQL)
  1. SQL relationships (Replies: 3)

  2. Sql join? (Replies: 4)

  3. SQL Help (Replies: 2)

Loading...