In SQL, a join (or cross join) between two tables (A and B) is when you match up each and every line (tuple) in the first table with each and every line in the second. So in this case, the join would create a table (temporarily at least) that is A x B lines long.

Compare with inner join and outer join.

Let G = (N,E) be a graph, and let x and y be distinct nodes of G that aren't joined by an edge. The graph G/xy formed by adding the edge (x,y) to G is called a join of G .

--back to combinatorics--

A Perl built-in function.

The first scalar argument is a separator. The rest of the arguments is a list.

The function joins each element in list putting the separator in the middle, returning the result. This is very useful when you want to convert a list to scalar.

For inverse operation, see split.

Example:

$ perl
my @channellist = ("Red", "Green", "Blue", "Alpha");
my $channels = join " and ", @channellist;

print "$channels\n";
^D
Red and Green and Blue and Alpha

Join is an SQL ("Structured Query Language") keyword. SQL is a language used to talk to databases.

A join operation creates a temporary database table or "record set", composed of elements of two existing tables. There are a few different ways you might go about joining. In all cases, each record in the new table is composed of at most one record from each of the two tables being joined. Your join statement will say how the database should decide which records go together. You can think of a "record" as a "row" much like in a spreadsheet such as Excel, and a "field" as a "column" (again, as in a spreadsheet).

You can also take the result of one join operation, and join that with another table, or with the result of another join operation.

We'll define two very small tables and use those for each example. Even though SQL is case-blind, we'll use all caps for keywords so they stand out clearly. Each of these examples begins with "SELECT * FROM". That's SQL for "give me all of the fields in the following records". We could select only some of the fields, but for the sake of simplicity we won't do that.

    Table A: People
    person_id   lastname    firstname       state_id
    -------------------------------------------------
    1           Bloggs      Joe             PA
    2           Sped        Fred            VT
    3           Martian     Marvin          MA
    4           Necropolis  Ashram          PA

    Table B: States
    state_id    statename
    -------------------------------------------------
    PA          Pennsylvania
    VT          Vermont
    OH          Ohio

Inner join

SELECT * FROM People INNER JOIN States ON People.state_id=States.state_id;

An "inner join" creates new records only where the condition (People.state_id=States.state_id, in this case) holds true. Think of it like this: The database looks at each record in People in turn. If the state_id for that record matches the state_id of a record in the States table, those two records are merged together into one record, which is added to the result table.

Here's the result table for this one:

person_id   lastname    firstname   People.state_id States.state_id statename
--------------------------------------------------------------------------------
1           Bloggs      Joe         PA              PA              Pennsylvania
4           Necropolis  Ashram      PA              PA              Pennsylvania
2           Sped        Fred        VT              VT              Vermont

You'll notice that Marvin Martian is not included: His state_id is "MA", and there is no record in the States table where state_id="MA".

Outer Joins

Outer joins come in two flavors: Left outer join, and right outer join. The only kind of left join is a left outer join, and the only kind of right join is a right outer join, so you're not required to specify "outer" for either one. We'll specify it for the sake of clarity.

Left join (left outer join)

SELECT * FROM People LEFT OUTER JOIN States ON People.state_id=States.state_id;

In a left join, the result record set will include a record for each record in the "left" table (People, in this case), regardless of what is or is not found in the other table. Records will merged where our "on" criterion is met. Therefore, our result table looks like this:

person_id   lastname    firstname   People.state_id States.state_id statename
--------------------------------------------------------------------------------
3           Martian     Marvin      MA      
1           Bloggs      Joe         PA              PA              Pennsylvania
4           Necropolis  Ashram      PA              PA              Pennsylvania
2           Sped        Fred        VT              VT              Vermont

Note that Marvin Martian is included this time. His record includes nothing from the States table: That's why it got left out last time. Note also that we haven't seen hide nor hair of Ohio in any of these operations. That's because we've included information from the States table only when the state_id for a States record matches a state_id in a People record. Since "OH" never appears in the People table, it has never appeared in our results.

Would you like to see Ohio? It's lovely this time of year. Let's move ahead and hope that Ohio joins the party in our last example.

Right join (right outer join)

SELECT * FROM People RIGHT OUTER JOIN States ON People.state_id=States.state_id;

A right join is like a left join except that we include all records from the right table rather than the left table. As in the left join above, records will be merged where the "on" criterion is met. Let's see our results for this query:

person_id   lastname    firstname   People.state_id States.state_id statename
--------------------------------------------------------------------------------
                                                    OH              Ohio
1           Bloggs      Joe         PA              PA              Pennsylvania
4           Necropolis  Ashram      PA              PA              Pennsylvania
2           Sped        Fred        VT              VT              Vermont

Finally, we can see Ohio. That's because we're including everything from States. We've misplaced Marvin Martian again, because no record in States has a state_id matching his. We're also seeing another interesting point: If the "on" criterion for the same record is met twice ("PA"/"Pennsylvania" in this case), we'll get a new merged record each time that happens.

How do you choose which join to use? That depends entirely on what information you're looking for. There are also questions of efficiency.

What we've learned from all this

The terms "inner" and "outer" turn out to be descriptive, in a metaphorical way: An outer join is much more free and easy than the straightlaced, inward-looking inner join.

We've only scratched the surface of SQL, but we've already seen that SQL is a very high level language: We're asking the database to perform complicated operations, with much searching and comparing and heaving of data to and fro. What's the binary representation of a database table? What's the algorithm for choosing records for each of these joins? Those questions are not addressed. We don't know even how to begin expressing the answers in this language. We do not, in fact, have any concept of process here at all: We're not describing a sequence of actions. Instead, we're defining the expected result and leaving everything else to the implementation. This is just a very terse, stilted equivalent of somebody in a tie yelling "Bill? Get me a list of all our customers!" If poor Bill was very bad in a former life, he might just write an SQL query to generate that list, or some more elaborate list that a lunatic in a tie would find more appealing.

Yeah, but...

Why do we have two tables at all? Why not just put the full text for the appropriate state name in each People record? Because states have more than names. They also have state songs, and state flowers, and average yearly rainfall. There could be half a dozen different pieces of information associated with each of the fifty states (and don't forget Guam). Are you going to reproduce all of that in each People record? That would be painful. What if somebody moves? Then you've got to change half a dozen fields in his record instead of just one simple two-letter code. The reality of the data entry process is that mistakes happen: At some point, you'll have one state flower for Kansas in Jane Doe's record, and a different one for Mary Roe. Furthermore, Kansas might change their state flower next week. Then you'll have to update hundreds, thousands, or even millions of records with the new information. If all of those records just have a state_id field equal to "KS", like the arrangement we have above, then you'll only have to change the Kansan state flower in one place. This also takes up less room on your disk, and with enough records that becomes a real issue. You also save yourself trouble if you decide to start tracking the official state lizard along with the rest: With a separate States table, you'll only have to add a new field to fifty or sixty different records. With one monolithic table you'd have to add (and then populate) a new field in, potentially, millions of records. Breaking tables up logically makes life easier at every point until you actually need the information back. When that happens, you just write a join query as we've outlined above.

To unite or bring together two or more like entities, e.g. We are gathered here to day to join this couple in holy matrimony
To make an individual a part of a larger group: Join the army, you long haired layabout!
To bring into the same state: She had fallen asleep and I was ready to join her.
To meet or accompany: Will you join me at the bar for a drink?
To afix together: The carpenter joined the backpiece onto the new chair with skill and sturdy nails.
A seam, or mark where two parts have been affixed together: It's not all one piece, if you look carefully you can see a join over there.

See joint, conjoin, rejoin.

---

In SQL relational database terminology, a join is when a result set is constructed out of two or more source tables.

Let me explain that in plain english. SQL databases are usually fairly well normalised, that is essentially that one fact is stored in one and only one place. For example, a customer's name is kept on a customer record, and is not duplicated on the record of their order.

Thus, for instance, in order to see the names and order counts of customers with three or more orders in the last month, you would have to look at data from both the the customer table and the orders table. You would join the two tables.

Curiously, the SQL language has two syntaxes for doing joins. Already possessing an elegant and concise join syntax, they found it necessary to change in the SQL-92 standard to the ugly, wordy, redundant cobolesque syntax as given by Wharfinger. Thankfully I don't see it used much in practice. However, since the old syntax is no longer as well documented and may not be supported in future (unlikely as databases live for a long time, and thus backward compatibility is important), we may see more of the wordy syntax.

Here are some joins in the simpler, more mathematical SQL syntax.

First and simplest is the Cartesian product or cross join, which matches all rows in one table to all rows in the other table, e.g. if there are n rows in the first table, and m rows in the second table, there will be n * m rows in the result set, which can be very large if both tables are substantial. Mathematically this is simplest, but it isn't of much practical use.

e.g. Select customer.name, order.quantity
from customer, order

 

Then there is the natural join or inner join, which is done simply by giving an expression in the where clause. You can think of this as two step process: generating the Cartesian product, and then discarding all rows that do not satisfy the where clause. However SQL is not a procedural language: you tell the interpreter what you want but not now to do it, and it does things in the most efficient way that it knows how, which is certainly not to generate a Cartesian product.

e.g. Select customer.name, order.quantity from
customer, order
where order.customerid = customer.customerid

This is the workhorse of SQL joins, it is what you will use in some form or another 99% of the time. Though key equivalence is the join condition that defines a natural join, you could code any expression there and it would still be an inner join. But bear in mind that any other join, that does not join an order to the matching customer as is done above, is semantically nonsensical; which is I suppose what makes this one "natural".

A more complex natural join e.g.
Select customer.name, order.quantity
from customer, order
where (order.customerid = customer.customerid) and (order.quantity > 5) and
(order.customerid in (select customerid from specialcustomers))

 

Finally there is the outer join, which addresses a potential issue in all of the above joins. It is possible that there are orders with customerid equal to null, or with a customer id that does not match any customer. These will never be seen in the above queries, as the null does not match any customer id. The outer join is done with *= instead of =

e.g. Select customer.name, order.quantity
from customer, order
where order.customerid *= customer.customerid

This says to find all orders, and match each one with a customer if possible, but to show them without a customer otherwise. Remember that the * goes on the side of the anchor table, the one that is always there.

The distinction between left outer join and right outer join is IMHO a bogus one. A.id *= B.id is entirely equivalent to B.id =* A.id , and the order in which columns are joined has no bearing on the order in which they are displayed.

The new, wordy, SQL-92 syntax does however allow one to specify a full outer join, where you show all A that matches B, all A with no matching B, and all B with no matching A.

KANJI: GOU GAッ KAッ a (join, meet, fit, one-tenth)

ASCII Art Representation:

                      %%%%
                     ,%%%%,
                    ,%%%"%%,
                   ,%%%" "%%%,
                 ,%%%"     "%%%,
               ,%%%"         "%%%,
             ,%%%"             "%%%,
           ,%%%"                 "%%%%,
         ,%%%"                     "%%%%%%,,,
      ,,%%%"                ,%%%,    "%%%%%%%%%"
  ,,%%%""     "%%%%%%%%%%%%%%%%%%%      ""%%%"
""""
 
 
          %%,                     ,%%%,
          %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
          %%%%                    %%%%
          %%%%                    %%%%
          %%%%                    %%%%
          %%%%                    %%%%
          %%%%                    %%%%
          %%%%%%%%%%%%%%%%%%%%%%%%%%%%
          %%%%                    %%%%
          "%%"                    "%%"

Character Etymology:

Originally shown as a lid covering a round open object. Some feel this object is a container, while others see it as a mouth, to give the meaning similar to the Enligh term cap off a remark, i.e. to reply fittingly. It now means to join or to fit in the more general sense.

A Listing of All On-Yomi and Kun-Yomi Readings:

on-yomi: GOU GAッ KAッ
kun-yomi: a ai

Nanori Readings:

Nanori: au an i ka kou goo ni ne ya ri wai

English Definitions:

1. GOU: one-tenth; one of ten stations up a mountain.
2. a(u): fit; suit; agree with, match, be correct; be profitable.
3. a(wasu), a(waseru): join together; be opposite, face; unite, combine, connect; add up; mix; match; overlap; compare; check with.
4. a(wasaru): get together, unite.
5. gas(suru): join together; sum up; combine, unite, mix; agree with.
6. a(i)-: joint; associate, accomplice.
7. a(wase): joined together; opposite, facing.

Character Index Numbers:

New Nelson: 715
Henshall: 121

Unicode Encoded Version:

合

Unicode Encoded Compound Examples:

合理 (gouri): rationality.
合戦 (kassen): battle.
合一 (gouitsu): unification, union, oneness.

Previous: yellow | Japanese Kanji | Next: valley

Join (?), v. t. [imp. & p. p. Joined (?); p. pr. & vb. n. Joining.] [OE. joinen, joignen, F. joindre, fr. L. jungere to yoke, bind together, join; akin to jugum yoke. See Yoke, and cf. Conjugal, Junction, Junta.]

1.

To bring together, literally or figuratively; to place in contact; to connect; to couple; to unite; to combine; to associate; to add; to append.

Woe unto them that join house to house. Is. v. 8.

Held up his left hand, which did flame and burn Like twenty torches joined. Shak.

Thy tuneful voice with numbers join. Dryden.

2.

To associate one's self to; to be or become connected with; to league one's self with; to unite with; as, to join a party; to join the church.

We jointly now to join no other head. Dryden.

3.

To unite in marriage.

He that joineth his virgin in matrimony. Wyclif.

What, therefore, God hath joined together, let not man put asunder. Matt. xix. 6.

4.

To enjoin upon; to command.

[Obs. & R.]

They join them penance, as they call it. Tyndale.

5.

To accept, or engage in, as a contest; as, to join encounter, battle, issue.

Milton.

To join battle, To join issue. See under Battle, Issue.

Syn. -- To add; annex; unite; connect; combine; consociate; couple; link; append. See Add.

 

© Webster 1913.

---

Join, v. i.

To be contiguous, close, or in contact; to come together; to unite; to mingle; to form a union; as, the hones of the skull join; two rivers join.

Whose house joined hard to the synagogue. Acts xviii. 7.

Should we again break thy commandments, and join in affinity with the people of these abominations? Ezra ix. 14.

Nature and fortune joined to make thee great. Shak.

 

© Webster 1913.

---

Join, n. Geom.

The line joining two points; the point common to two intersecting lines.

Henrici.

 

© Webster 1913.