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.